将SQL查询转换为linq lambda实体框架核心

Question

我需要使用实体框架核心选择具有groupby、sum(quantity(和where子句的前10个LINQ查询。

有人能帮我把这个SQL代码转换成linqlambda吗？

非常感谢。

SELECT TOP 10 
OrderItems.ProductName,
OrderItems.ProductId,
SUM(OrderItems.Units) AS Quantity 
FROM
Orders, OrderItems  
WHERE
OrderItems.OrderId = Orders.Id  
AND Orders.OrderDate >= '2019-12-18 16:38:27' 
AND Orders.OrderDate <= '2020-12-18 16:38:27' 
AND Orders.OrderStatusId = 2 
GROUP BY  
ProductName, OrderItems.ProductId  
ORDER BY 
Quantity DESC

我尝试了这个EF查询：

var query = (from sta in _context.Set<OrderItem>().AsQueryable()
join rec in _context.Set<Order>().AsQueryable() on sta.OrderId equals rec.Id
where rec.OrderStatusId == Convert.ToInt32(orderStatusId) && rec.OrderDate >= Convert.ToDateTime(startDate) && rec.OrderDate <= Convert.ToDateTime(endDate)
group sta by new
{
sta.ProductName,
sta.ProductId
} into grp
select new OrderDto()
{
ProductName = grp.Key.ProductName,
ProductId = grp.Key.ProductId,
Quantity = grp.Max(t => t.Units),
}).OrderBy(x => x.Quantity).Take(Convert.ToInt32(top)).ToList();

Answer 1

我相信您的语句SELECT ... FROM Orders, OrderItems... WHERE OrderItems.OrderId = Orders.Id虽然看起来像CROSS JOIN，但最终被优化为INNER JOIN。

因此，假设你已经用导航属性设置了你的模型，你可能会更好地使用它。Include((。除此之外，我认为你几乎在那里：

var query = _context.Set<OrderItem>().Include(o => o.Order)
.Where(rec => rec.Order.OrderStatusId == Convert.ToInt32(orderStatusId))
.Where(rec => rec.Order.OrderDate >= Convert.ToDateTime(startDate) && rec.Order.OrderDate <= Convert.ToDateTime(endDate))
.GroupBy(g => new { g.ProductName, g.ProductId })
.Select(grp => new OrderDto
{
ProductName = grp.Key.ProductName,
ProductId = grp.Key.ProductId,
Quantity = grp.Sum(t => t.Units)
})
.OrderBy(x => x.Quantity)
.Take(Convert.ToInt32(top));

这会产生以下输出：

SELECT TOP(@__p_3) [o].[ProductName], [o].[ProductId], SUM([o].[Units]) AS [Quantity]
FROM [OrderItems] AS [o]
INNER JOIN [Orders] AS [o0] ON [o].[OrderId] = [o0].[Id]
WHERE ([o0].[OrderStatusId] = @__ToInt32_0) AND (([o0].[OrderDate] >= @__ToDateTime_1) AND ([o0].[OrderDate] <= @__ToDateTime_2))
GROUP BY [o].[ProductName], [o].[ProductId]
ORDER BY SUM([o].[Units])

假设您不能将导航属性添加到OrderItem模型中，那么您的代码似乎基本上是这样的：

var query2 = (from sta in _context.Set<OrderItem>() 
from rec in _context.Set<Order>()
where sta.OrderId == rec.Id && rec.OrderStatusId == Convert.ToInt32(orderStatusId) 
&& rec.OrderDate >= Convert.ToDateTime(startDate) && rec.OrderDate <= Convert.ToDateTime(endDate)
group sta by new
{
sta.ProductName,
sta.ProductId
} into grp
select new OrderDto()
{
ProductName = grp.Key.ProductName,
ProductId = grp.Key.ProductId,
Quantity = grp.Max(t => t.Units),
}
)
.OrderBy(x => x.Quantity)
.Take(Convert.ToInt32(top));

这将生成以下SQL:

SELECT TOP(@__p_3) [o].[ProductName], [o].[ProductId], MAX([o].[Units]) AS [Quantity]
FROM [OrderItems] AS [o]
CROSS JOIN [Orders] AS [o0]
WHERE ((([o].[OrderId] = [o0].[Id]) AND ([o0].[OrderStatusId] = @__ToInt32_0)) AND ([o0].[OrderDate] >= @__ToDateTime_1)) AND ([o0].[OrderDate] <= @__ToDateTime_2)
GROUP BY [o].[ProductName], [o].[ProductId]
ORDER BY MAX([o].[Units])

这是我的完整测试台，供参考

using Microsoft.EntityFrameworkCore
using Microsoft.EntityFrameworkCore.Query.SqlExpressions
using Microsoft.EntityFrameworkCore.Query
#region EF Core 3.1 .ToSql() helper method courtesy of https://stackoverflow.com/a/51583047/12339804
public static class IQueryableExtensions
{
public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.Private("_relationalCommandCache");
var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
string sql = command.CommandText;
return sql;
}
private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
}
#endregion 
public class OrderItem
{
public int Id {get;set;}
public int OrderId {get;set;}
public int ProductName {get;set;}
public int ProductId {get;set;}
public int Units {get;set;}

public Order Order {get;set;} // added navigation property for .Include() to pick up on
}
public class Order { 
public int Id {get;set;}
public int OrderStatusId {get;set;}
public DateTime OrderDate {get;set;}    
}
public class OrderDto
{
public int ProductName { get; set; }
public int ProductId { get; set; }
public int Quantity { get; set; }
}
class Dbc : DbContext
{
public DbSet<Order> Orders {get;set;}
public DbSet<OrderItem> OrderItems {get;set;}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=.SQLEXPRESS;Database=Test;Trusted_Connection=True");
base.OnConfiguring(optionsBuilder);
}
}
void Main()
{
var _context = new Dbc();
var orderStatusId = "2";
var top = "10";
var startDate = DateTime.Parse("2019-12-16 16:38:27");
var endDate = DateTime.Parse("2019-12-18 16:38:27");
var query = _context.Set<OrderItem>().Include(o => o.Order)
.Where(rec => rec.Order.OrderStatusId == Convert.ToInt32(orderStatusId))
.Where(rec => rec.Order.OrderDate >= Convert.ToDateTime(startDate) && rec.Order.OrderDate <= Convert.ToDateTime(endDate))
.GroupBy(g => new { g.ProductName, g.ProductId })
.Select(grp => new OrderDto
{
ProductName = grp.Key.ProductName,
ProductId = grp.Key.ProductId,
Quantity = grp.Sum(t => t.Units)
})
.OrderBy(x => x.Quantity)
.Take(Convert.ToInt32(top));
query.ToSql().Dump();
//alternatively, trying to force a cross join syntax with extra WHERE condition. This way you don't need `public Order Order {get;set;}` navigation property on `OrderItem`
var query2 = (from sta in _context.Set<OrderItem>() 
from rec in _context.Set<Order>()
where sta.OrderId == rec.Id && rec.OrderStatusId == Convert.ToInt32(orderStatusId) 
&& rec.OrderDate >= Convert.ToDateTime(startDate) && rec.OrderDate <= Convert.ToDateTime(endDate)
group sta by new
{
sta.ProductName,
sta.ProductId
} into grp
select new OrderDto()
{
ProductName = grp.Key.ProductName,
ProductId = grp.Key.ProductId,
Quantity = grp.Max(t => t.Units),
}
)
.OrderBy(x => x.Quantity)
.Take(Convert.ToInt32(top));
query2.ToSql().Dump();
}