SQL到LINQ的问题 [英] SQL to LINQ questions
问题描述
我有一个查询，我想转换为LINQ。我找到了很好的例子或引用来解决更复杂的
查询问题。
这是我要重写的SQL命令C＃LINQ。注意
其中一个返回的字段是XrefID。来自第二个表格。
这个信息非常重要，但它使查询表达式复杂化在C＃中的
DataContext有两个表用于两个表（Customer
和外部参照）。这个特定的查询需要来自两个表的信息。
有没有人有任何好的参考资料可以帮助我使用LINQ编写这种类型的
查询？
或者有人知道我将如何处理我所拥有的场景吗？
在这里描述？
选择客户。*，
（从Xref中选择前1个Xref.XrefID，其中Customer.CustomerID =
Xref.CustomerID和Customer.CompanyID = Xref.CompanyID）作为XrefID
来自客户
，其中Customer.CustomerID = 70540，Customer.CompanyID = 6
-
感谢您的帮助，
Dan Tallent
" Dan Tallent" < sp ** @ microsoft.comwrote in message
news：Op ************** @ TK2MSFTNGP06.phx.gbl ...
< blockquote class =post_quotes>
>我有一个查询，我想转换为LINQ。我在找到包含更复杂的查询的好例子或参考资料时遇到了问题。
这是我想要重写为C＃LINQ的SQL命令。
请注意，其中一个返回的字段是XrefID。来自第二张
表。这些信息非常重要，但它使查询复杂化了C＃中的表达式。这个DataContext有两个类用于两个
表（Customer和Xref）。这个特殊的查询需要来自
两个表的信息。
有没有人有任何好的参考资料可以帮我写这种类型的
使用LINQ查询？
或者有人知道我将如何处理我已经在这里描述的场景吗？
您应该使用匿名类型汇总来自
表的记录中的数据：
否则，您的查询翻译应该非常简单。
当然，您需要明确列出字段而不是使用*。
" Pavel Minaev" < in **** @ gmail.comwrote in message
news：％2 **************** @ TK2MSFTNGP03.phx.gbl ...
" Dan Tallent" < sp ** @ microsoft.comwrote in message
news：Op ************** @ TK2MSFTNGP06.phx.gbl ...
< blockquote class =post_quotes>
>>我有一个查询，我想转换为LINQ。我在查找包含更复杂的查询的好例子或参考资料时遇到了问题。
这是我想要重写为C＃LINQ的SQL命令。请注意，其中一个返回的字段是XrefID。来自第二张桌子。这个信息非常重要，但它使查询表达式复杂化在C＃中DataContext有两个类用于两个表
（Customer和Xref）。这个特定的查询需要来自两个表的信息。
有没有人有任何好的参考资料可以帮助我使用LINQ编写这种类型的查询？
或者没有人我知道如何处理我在这里描述过的场景吗？
您应该使用匿名类型汇总来自
表的记录中的数据：
否则，您的查询翻译应该非常简单。
当然，您需要明确列出字段而不是使用
" *"。
我打算使用匿名类型，例如你提供的示例，但即使在
你提供的例子没有显示连接。
我玩的代码如下：
// -------------------------------------------------- ----------------------
var QueryCustName =
来自在DC.Customers
加入DC.Xrefs中的外部参照
on cust.CustomerID等于xref.CustomerID
其中cust.CompanyID = xref.CompanyID
其中cust.CompanyID = 6
选择新的{cust.DisplayName ，cust.Address1，xref.XrefID};
// -------------------------- ----------------------------------------------
问题是我更喜欢使用以下语法，但我不确定
如何使用此方法进行连接。我找到的所有例子
看起来都像上面那样。
var QueryCustName =
来自Cust的
在DC.Customers
..OrderBy（Cust = Cust.DisplayName）
..Where（Cust = Cust.CompanyID == mCompanyID） ）
..取（50）
选择新的{Cust.DisplayName，Cust.Address1，Xref.XrefID};
// --------------------------------------- ---------------------------------
任何帮助将不胜感激。
谢谢
Dan
>
9月4日，6：38 * pm，Dan Tallent < s ... @ microsoft.comwrote：
我计划使用匿名类型，例如你提供的示例，但即使在
你提供它的例子没有显示连接。
我玩的代码如下：
// ------- -------------------------------------------------- ---------------
var QueryCustName =
来自DC.Customers的客户的
/>
加入DC.Xrefs中的外部参照
on cust.CustomerID等于xref.CustomerID
where cust .CompanyID = xref.CompanyID
其中cust.CompanyID = 6
选择新的{cust.DisplayName，cust.Address1，xref.XrefID };
如果你在一个复杂的密钥上进行等值连接，最好在连接中进行两次
比较 - 请注意在这种情况下你可以使用匿名类型来复制键，例如：
在新的{cust.CustomerID，cust.CompanyID上加入DC.XRefs中的外部参照} $ / $
等于新{xref.CustomerID，xref.CompanyID}
// ------------ -------------------------------------------------- ----------
问题是我更愿意使用以下语法，但我不确定
怎么做使用此方法的连接。 *我找到的所有例子
看起来就像上面那样。
var QueryCustName =
来自客户在DC.Customers
。订购（Cust = Cust.DisplayName）
.Where（Cust = Cust.CompanyID == mCompanyID）
。获取（50）
选择新的{Cust.DisplayName，Cust.Address1，* Xref.XrefID};
// ---------------------------------------- --------------------------------
任何帮助将不胜感激。
显而易见的问题是 - 第一个代码片段有什么问题？
联接（隐式和显式）是一种情况其中LINQ语法
糖通常比链式方法调用更清晰。
但是，如果你坚持用另一种方式做，那么你只需要/>
将LINQ加入翻译为Enumerable.Join：
DC.Customers
..加入（
DC .Xrefs，
cust = new {cust.CustomerID，cust.CompanyID}，
xref = new {xref.CustomerID，xref.CompanyID}，
（cust，xref）= new {cust.DisplayName，cust.Address1，
xref.XRefID}）
..OrderBy（r = r.DisplayName）
..采取（50）
I have a query which I would like to convert to LINQ. I am having
problems finding good examples or references that cover more complex
queries.
Here is the SQL command I would like to rewrite into C# LINQ. Notice
that one of the returned fields "XrefID" is coming from the second table.
This information is very important but its complicating the query expression
in C# The DataContext has the two classes for the two tables (Customer
and Xref). This particular query needs information from both tables.
Does anyone have any good references that can help me writing this type of
query using LINQ?
Or does anyone know how I would go about dealing with the scenerio I have
discribed here?
select Customer.*,
(select top 1 Xref.XrefID from Xref where Customer.CustomerID =
Xref.CustomerID and Customer.CompanyID = Xref.CompanyID) as XrefID
from Customer
where Customer.CustomerID = 70540 and Customer.CompanyID = 6
--
Thanks for the help,
Dan Tallent
"Dan Tallent" <sp**@microsoft.comwrote in message
news:Op**************@TK2MSFTNGP06.phx.gbl...>I have a query which I would like to convert to LINQ. I am having
problems finding good examples or references that cover more complex
queries.
Here is the SQL command I would like to rewrite into C# LINQ.
Notice that one of the returned fields "XrefID" is coming from the second
table. This information is very important but its complicating the query
expression in C# The DataContext has the two classes for the two
tables (Customer and Xref). This particular query needs information from
both tables.
Does anyone have any good references that can help me writing this type of
query using LINQ?
Or does anyone know how I would go about dealing with the scenerio I have
discribed here?You should use an anonymous type to aggregate data from records from both
tables:
http://msdn.microsoft.com/en-us/library/bb397696.aspx
Otherwise, the translation of your query should be pretty straightforward.
Of course, you''ll need to explicitly list the fields rather than using "*".
"Pavel Minaev" <in****@gmail.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl..."Dan Tallent" <sp**@microsoft.comwrote in message
news:Op**************@TK2MSFTNGP06.phx.gbl...>>I have a query which I would like to convert to LINQ. I am having
problems finding good examples or references that cover more complex
queries.
Here is the SQL command I would like to rewrite into C# LINQ. Notice that
one of the returned fields "XrefID" is coming from the second table. This
information is very important but its complicating the query expression
in C# The DataContext has the two classes for the two tables
(Customer and Xref). This particular query needs information from both
tables.
Does anyone have any good references that can help me writing this type
of query using LINQ?
Or does anyone know how I would go about dealing with the scenerio I have
discribed here?
You should use an anonymous type to aggregate data from records from both
tables:
http://msdn.microsoft.com/en-us/library/bb397696.aspx
Otherwise, the translation of your query should be pretty straightforward.
Of course, you''ll need to explicitly list the fields rather than using
"*".
I plan on using anonymous types like the example you provided, but even in
the example you provide it does not show a join.
The code I was playing with looks like this:
//------------------------------------------------------------------------
var QueryCustName =
from cust in DC.Customers
join xref in DC.Xrefs
on cust.CustomerID equals xref.CustomerID
where cust.CompanyID = xref.CompanyID
where cust.CompanyID = 6
select new { cust.DisplayName, cust.Address1, xref.XrefID } ;
//------------------------------------------------------------------------
The problem is I would prefer to use the following syntax, but I am unsure
of how to do a join using this method. All of the examples I have found
look like the one above.
var QueryCustName =
from Cust in DC.Customers
..OrderBy(Cust =Cust.DisplayName)
..Where(Cust =Cust.CompanyID == mCompanyID)
..Take(50)
select new {Cust.DisplayName, Cust.Address1, Xref.XrefID };
//------------------------------------------------------------------------
any help would be appreciated.
Thanks
Dan
On Sep 4, 6:38*pm, "Dan Tallent" <s...@microsoft.comwrote:I plan on using anonymous types like the example you provided, but even in
the example you provide it does not show a join.
The code I was playing with looks like this:
//------------------------------------------------------------------------
var QueryCustName =
from cust in DC.Customers
join xref in DC.Xrefs
on cust.CustomerID equals xref.CustomerID
where cust.CompanyID = xref.CompanyID
where cust.CompanyID = 6
select new { cust.DisplayName, cust.Address1, xref.XrefID } ;If you are doing an equijoin on a complex key, it''s better to do both
comparisons in the join - note that you can use anonymous types for
complex keys in such scenarios, i.e.:
join xref in DC.XRefs on new { cust.CustomerID, cust.CompanyID }
equals new { xref.CustomerID, xref.CompanyID }
//------------------------------------------------------------------------
The problem is I would prefer to use the following syntax, but I am unsure
of how to do a join using this method. *All of the examples I have found
look like the one above.
var QueryCustName =
from Cust in DC.Customers
.OrderBy(Cust =Cust.DisplayName)
.Where(Cust =Cust.CompanyID == mCompanyID)
.Take(50)
select new {Cust.DisplayName, Cust.Address1, *Xref.XrefID };
//------------------------------------------------------------------------
any help would be appreciated.The obvious question is - what''s wrong with the first code snippet?
Joins (both implicit and explicit) are one case where LINQ syntactic
sugar is usually much clearer than chained method calls.
However, if you insist on doing it the other way, then you just
translate LINQ join to Enumerable.Join:
DC.Customers
..Join(
DC.Xrefs,
cust =new { cust.CustomerID, cust.CompanyID },
xref =new { xref.CustomerID, xref.CompanyID },
(cust, xref) =new { cust.DisplayName, cust.Address1,
xref.XRefID })
..OrderBy(r =r.DisplayName)
..Take(50)
这篇关于SQL到LINQ的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
