CODEWITHSUNDEEP
c#linq
Frazer
Frazer

Reputation: 606

Linq To Entities Group By Max Date in Join

I am trying to write a Linq to Entities Query based on the SQL below

Basically I am looking to get a list of the last 5 orders, the order date being in the child detail table of (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

My Code is as follows

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)

The LatestOrders being enumerable in the view.

I have looked at various other sources about Linq Group by, which relate to single table groupings and im struggling to translate this into multi table.

Upvotes: 2

Views: 1508

Answers (2)

ocuenca
ocuenca

Reputation: 39326

Your sql query can be translated to this linq query:

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);

Upvotes: 1

neer
neer

Reputation: 4082

Almost like that

var last5Item = from td in Context.TitleDetails.OrderByDescending(i => i.OrderDate).Take(5)
                join t in Context.Titles on td.TitleId equals t.id
                select ..

Upvotes: 1

Related Questions