CODEWITHSUNDEEP
c#linq
David Nguyen
David Nguyen

Reputation: 8528

Linq query w/ sub query and max

How do I rewrite this SQL into a Linq query?

Plain SQL

SELECT *
FROM contracts
    INNER JOIN
        (SELECT contractid, max(date) date 
        FROM contractlogs GROUP BY contractId) b 
    ON contracts.id = b.contractId

Attempt at Linq

from c in _db.Contracts
join sub in (from cl in _db.ContractLogs
             group cl by cl.contractId into g
             select new { contractId = g.contractId, changedate = g.Max(x => x.date)})
    on c.id equals sub.contractId 
select new { c, cl }

Goal of the query is to select all contracts w/ their newest update (first) (in contractLogs). I'm currently stumped on how the select would work. Ideally i'm trying to return an object with c & cl.

Upvotes: 0

Views: 455

Answers (4)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can try this:

from c in _db.Contracts
select new 
{ 
   c, 
   cl = _db.ContractLogs.Where(l => l.contractId == c.contractId).OrderByDescending(l => l.date).FirstOrDefault() 
}

Upvotes: 0

Thomas Levesque
Thomas Levesque

Reputation: 292455

In cases like this it's often easier to write the query and subquery separately:

var subQuery =
    from cl in _db.ContractLogs
    group cl by cl.contractId into g
    select new { contractId = g.Key, date = g.Max(cl => cl.date) };

var query =
    from c in _db.Contracts
    join cl in subQuery on c.contractId equals cl.contractId
    select new { contract = c, cl.date };

Upvotes: 0

evanmcdonnal
evanmcdonnal

Reputation: 48096

The most literal translation is going to involve you calling groupby on ContractLogs and then joining that into Contacts. I think the ordering of your operations in your LINQ attempt is a little off however I don't often use the query syntax so I'm not positive about that. Regardless, I think you'd prefer something like this;

_db.ContractLogs.GroupBy(x => x.contractId).Select(x => new { contractid = x.Key, changedate = x.Max(y => y.date) })

With that you can do the join into _db.Contracts but I think you could write it more simply with a where though that might be less optimized by the LINQ to SQL provider. Anyway, just completing the example with a join;

OldQuery.Join(_db.Contracts, cl => cl.contractid,
              c => c.contractid, (cl, c) => cl);

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109119

You can get the most recent log by sorting them in descending order and taking the first one:

from c in _db.Contracts
let mostRecentContractLog = c.ContractLogs
                             .OrderByDescending(cl => cl.date)
                             .FirstOrDefault()
select new { c, mostRecentContractLog }

As you see, I assume you have a navigation property Contract.ContractLogs. It's always strongly recommended to work with navigation properties in stead of manually coded joins.

Upvotes: 1

Related Questions