Subquery with MAX() in C#-CodePudding

I am able to work with queries in C#. But I don't know how to write this subquery. Thank you for any help.

SELECT * 
FROM Stores.PerformanceRealization PR
LEFT JOIN Stores.GroupRealization ON Stores.GroupRealization.Id = PR.GroupRealizationId
WHERE PR.Deadline = (SELECT MAX(Deadline) 
                     FROM Stores.PerformanceRealization PR2 
                     WHERE PR.GroupRealizationId = PR2.GroupRealizationId)

I tried something like this:

var result = from aa in _context.PerformanceRealization
             join bb in _context.GroupRealization on bb.Id equals aa.GroupRealizationId
             where aa.Deadline = (from cc in _context.PerformanceRealization 
                                  where aa.GroupRealizationId = cc.GroupRealizationId 
                                  select max(cc.Deadline))   
             select aa;

CodePudding user response：

Try the following query, which avoids collision if Deadline is duplicated.

 var realizations  = _context.PerformanceRealization; //  here  you can add filters

var result = 
    from aa in realizations
    join bb in _context.GroupRealization on bb.Id equals aa.GroupRealizationId into bbj
    from bb in bbj.DefaultIfEmpty() // left join
    from cc in realizations
        .Where(cc => aa.GroupRealizationId = cc.GroupRealizationId)
        .OrderByDescnding(cc => cc.Deadline)
        .Take(1) // outer apply or join to ROW_NUMBER query
    select new  
    {
        aa,
        bb
    };

CodePudding user response：

I found answer here: LINQ SELECT with Max and Where in SUBQUERY

My solution:

_context.PerformanceRealization.Where(u => u.Deadline == 
                                         _context.PerformanceRealization
                                         .Where(x => x.GroupRealizationId == u.GroupRealizationId)
                                         .Max(x => x.Deadline))
                               .Select(u => u);