c# - Linq To Entities Group By 最大日期加入

Question

我正在尝试根据下面的 SQL 编写 Linq to Entities 查询

基本上我希望获得最近 5 个订单的列表，订单日期在 (TitleDetails) 的子详细信息表中

SELECT t.Name, t.LimpIsbn, t.CasedIsbn, max(td.OrderDate) LastOrder
FROM [inform].[dbo].[Titles] t
left join [inform].[dbo].[TitleDetails] td on t.id = td.TitleId
where td.OrderDate is not null
group by  t.Name, t.LimpIsbn, t.CasedIsbn
order by  max(td.OrderDate) desc

我的代码如下

var query = Context.Titles
            .Include(x => x.MisAccount)
            .Include(x => x.TitleDetails)
            .Where(x => x.TitleDetails.Any());

if (!string.IsNullOrEmpty(model.MisCustomer))
{
    query = query.Where(x => x.MisAccount.Code == model.MisCustomer);
}

if (!User.IsInRole(Permission.CanAccessAllCustomers.ToString()))
{
    var predicate = PredicateBuilder.False<Title>();

     foreach (var customer in validCustomers)
     {
         var cust = customer.Code;
         predicate = predicate.Or(x => x.MisAccount.Code == cust);
     }

    query = query.AsExpandable().Where(predicate);
}

model.LatestOrders = query.Take(5)

LatestOrders 在 View 中是可枚举的。

我查看了有关 Linq Group by 的各种其他来源，这些来源与单表分组有关，我正在努力将其转化为多表。

Answer 1

您的 sql 查询可以转换为此 linq 查询:

var query =(from t in Context.Titles
            join td in Context.TitleDetails on t.id equals td.TitleId into tds
            from e in tds.DefaultIfEmpty()
            where e.OrderDate!=null
            group  e.OrderDate by new {t.Name, t.LimpIsbn, t.CasedIsbn} into groups
            from g in groups
            select new { Name = m.Key.Name,
                        LimpIsbn = g.Key.LimpIsbn,
                        CasedIsbn = g.Key.CasedIsbm,
                        LastOrder = g.Max(x => x)})
            .OrderBy(e=>e.LastOrder).Take(5);