我正在尝试使用
linq的查询语法将此查询转换为基于方法的语法.
这是查询：
var products = from p in context.Products
join t in context.TopSellings
on p.Id equals t.Id into g
from tps in g.DefaultIfEmpty()
orderby tps.Rating descending
select new
{
Name = p.Name,Rating = tps.Rating == null ? 0 : tps.Rating
};
{SELECT
[Project1].[Id] AS [Id],[Project1].[Name] AS [Name],[Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[Id] AS [Id],[Extent1].[Name] AS [Name],CASE WHEN ([Extent2].[Rating] IS NULL) THEN 0 ELSE [Extent2].[Rating] END AS [C1],[Extent2].[Rating] AS [Rating]
FROM [dbo].[Products] AS [Extent1]
LEFT OUTER JOIN [dbo].[TopSellings] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
) AS [Project1]
ORDER BY [Project1].[Rating] DESC}
到目前为止,我尝试过的是这样的：
var products = context.Products
.Join(inner: context.TopSellings.DefaultIfEmpty(),outerKeySelector: c => c.Id,innerKeySelector: y => y.Id,resultSelector: (j,k) => new { Name = j.Name,Rating = k.Rating == null ? 0 : k.Rating })
.OrderByDescending(p => p.Rating);
这个产生一个不同的SQL查询(当然,这与数据在程序中的使用方式有不同的含义)：
{SELECT
[Project1].[Id] AS [Id],CASE WHEN ([Join1].[Rating] IS NULL) THEN 0 ELSE [Join1].[Rating] END AS [C1]
FROM [dbo].[Products] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Id] AS [Id],[Extent2].[Rating] AS [Rating]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN [dbo].[TopSellings] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[Id] = [Join1].[Id]
) AS [Project1]
ORDER BY [Project1].[C1] DESC}
您的答案将会非常有用,非常感谢！
解决方法
通常,您可以通过访问查询的Expression属性来获取从任何查询表达式中使用的确切表达式.然后只需分析该表达式并重现它.
var expr = products.Expression;
另一方面,使用查询语法的每个表达式都具有直接的转换.与子句的部分连接对应于GroupJoin(),而来自子句的额外对应于SelectMany().这应该产生一个等价的查询：
var products = context.Products.GroupJoin(context.TopSellings,p => p.Id,t => t.Id,(p,g) => new { p,g })
.SelectMany(x => x.g.DefaultIfEmpty(),(x,tps) => new { x.p,x.g,tps })
.OrderByDescending(x => x.tps.Rating)
.Select(x => new { x.p.Name,Rating = x.tps.Rating == null ? 0 : x.tps.Rating });
但是你可以删除一些冗余,削减不再使用的变量并利用一些有用的运算符.请注意,它可能会影响生成的实际查询,因此它不完全匹配,但它应该足够接近.
var products = context.Products.GroupJoin(context.TopSellings,g) => g.DefaultIfEmpty()
.OrderByDescending(tps => tps.Rating)
.Select(tps => new { p.Name,Rating = tps.Rating ?? 0 })
);
