SQL到LINQ的问题

本文介绍了SQL到LINQ的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个查询，我想转换为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 解决方案 您应该使用匿名类型汇总来自 表的记录中的数据： http://msdn.microsoft.com/en-us/library/bb397696 .aspx 否则，您的查询翻译应该非常简单。 当然，您需要明确列出字段而不是使用*。 您应该使用匿名类型汇总来自 表的记录中的数据： http://msdn.microsoft.com/en-us/library/bb397696 .aspx 否则，您的查询翻译应该非常简单。 当然，您需要明确列出字段而不是使用 " *"。 我打算使用匿名类型，例如你提供的示例，但即使在 你提供的例子没有显示连接。 我玩的代码如下： // -------------------------------------------------- ---------------------- 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 > 如果你在一个复杂的密钥上进行等值连接，最好在连接中进行两次 比较 - 请注意在这种情况下你可以使用匿名类型来复制键，例如： 在新的{cust.CustomerID，cust.CompanyID上加入DC.XRefs中的外部参照} $ / $ 等于新{xref.CustomerID，xref.CompanyID} 显而易见的问题是 - 第一个代码片段有什么问题？ 联接（隐式和显式）是一种情况其中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 havingproblems finding good examples or references that cover more complexqueries.Here is the SQL command I would like to rewrite into C# LINQ. Noticethat one of the returned fields "XrefID" is coming from the second table.This information is very important but its complicating the query expressionin C# The DataContext has the two classes for the two tables (Customerand Xref). This particular query needs information from both tables.Does anyone have any good references that can help me writing this type ofquery using LINQ?Or does anyone know how I would go about dealing with the scenerio I havediscribed here?select Customer.*,(select top 1 Xref.XrefID from Xref where Customer.CustomerID =Xref.CustomerID and Customer.CompanyID = Xref.CompanyID) as XrefIDfrom Customerwhere Customer.CustomerID = 70540 and Customer.CompanyID = 6--Thanks for the help,Dan Tallent 解决方案You should use an anonymous type to aggregate data from records from bothtables: http://msdn.microsoft.com/en-us/library/bb397696.aspxOtherwise, the translation of your query should be pretty straightforward.Of course, you''ll need to explicitly list the fields rather than using "*".You should use an anonymous type to aggregate data from records from bothtables: http://msdn.microsoft.com/en-us/library/bb397696.aspxOtherwise, 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 inthe example you provide it does not show a join.The code I was playing with looks like this://------------------------------------------------------------------------var QueryCustName =from cust in DC.Customersjoin xref in DC.Xrefson cust.CustomerID equals xref.CustomerIDwhere cust.CompanyID = xref.CompanyIDwhere cust.CompanyID = 6select new { cust.DisplayName, cust.Address1, xref.XrefID } ;//------------------------------------------------------------------------The problem is I would prefer to use the following syntax, but I am unsureof how to do a join using this method. All of the examples I have foundlook 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.ThanksDanIf you are doing an equijoin on a complex key, it''s better to do bothcomparisons in the join - note that you can use anonymous types forcomplex keys in such scenarios, i.e.:join xref in DC.XRefs on new { cust.CustomerID, cust.CompanyID }equals new { xref.CustomerID, xref.CompanyID }The obvious question is - what''s wrong with the first code snippet?Joins (both implicit and explicit) are one case where LINQ syntacticsugar is usually much clearer than chained method calls.However, if you insist on doing it the other way, then you justtranslate 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的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！