SQL: SELECT id, MAX(y.DateCreated) FROM x LEFT JOIN y in Linq?

Question

I can't seem to rewrite this SQL into EF Linq:

SELECT      Conversation.Id
FROM        Conversation LEFT JOIN Message
                ON  Conversation.Id = Message.ConversationId
GROUP BY    Conversation.Id
ORDER BY    MAX(Message.DateCreated) DESC

I would think something like this would work:

 _dbContext.Conversation
           .OrderByDescending(c => c.Messages.DefaultIfEmpty().Max(m => m.DateCreated))
           .Select(cm => cm.Id);

But this gives me the error System.InvalidOperationException : Sequence contains no elements.

Also this:

_dbContext.Conversation
          .Select(c => new {c.Id, MaxDate = c.Messages.DefaultIfEmpty().Max(m => m.DateCreated)})
          .OrderByDescending(c => c.MaxDate)
          .Select(cm => cm.Id);

But that gives me System.ArgumentException : At least one object must implement IComparable..

What is the correct way to do this?

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you? — NetMage, Oct 15 '18 at 22:14
I think, you may get some ideas from this post. https://learn.microsoft.com/en-us/ef/ef6/querying/related-data — Tejus, Oct 15 '18 at 23:08
@NetMage I think that converges with Guaravsa 's query. I am close but haven't figured out why my DefaultIfEmpty isn't working with Max and an empty set of subrecords. — mikebridge, Oct 16 '18 at 22:13
What is the datatype of `Messages`? What would the "default" be for that? What is the result of calling `Max` on that? (Alternatively, what is the type of `MaxDate`?) — NetMage, Oct 16 '18 at 22:17
Also, what does `MAX` evaluate to when there are no matching `Message` rows and what order does that end up in? — NetMage, Oct 16 '18 at 22:25
It currently looks like `_dbContext.Conversation .GroupJoin(_dbContext.Message, c => c.Id, m => m.ConversationId, (c, m) => new { c, m }) .SelectMany(cm => cm.m.DefaultIfEmpty(new Message { DateCreated = DateTime.MaxValue }), (cm, m) => new { cm.c, m }) .GroupBy(cm1 => new { cm1.c.Id }, cm1 => cm1.m) .Select(cm => new { cm.Key.Id, MaxDateCreated = cm.Max(x => x.DateCreated) }) .OrderByDescending(x => x.MaxDateCreated).Select(x => x.Id)` — mikebridge, Oct 16 '18 at 22:42
I'm not sure what that means? I was trying to find out what happens in SQL when `MAX` has no matching rows, but I think my answer means it doesn't matter. — NetMage, Oct 16 '18 at 22:43

score 1 · Answer 1 · answered Oct 15 '18 at 23:35

1

Can you try this:

from r in Conversation
              join ru in Message
              on r.Id equals ru.ConversationId into ps
              from ru in ps.DefaultIfEmpty()
              group ru by new { ru.ConversationId, ru.DateCreated } into rug
              select new {
                  id = ru.ConversationId,
                  datecreated = rug.Max(ru => ru.datecreated)
              }).OrderByDescending(x => x.datecreated);

This maynot compile as I dont have the code to test this with (like a fiddle)

answered Oct 15 '18 at 23:35

Gauravsa

6,330
2
21
30

You can just do `form ru in r.Messages.DefaultIfEmpty()` instead. – juharr Oct 16 '18 at 00:05
@Gauravsa This is pretty close, thanks! I haven't successfully gotten DefaultIfEmpty to work against a missing set of subrecords, though---still working on that. – mikebridge Oct 16 '18 at 22:16

Lowkey · Answer 2 · 2018-10-16T22:29:08.087

1

You're close though, just drop DefaultIfEmpty

_dbContext.Conversation.Select(con => new 
    {
        con.Id,
        MaxDateCreated = (DateTime?) con.Messages.Max(msg => msg.DateCreated)
    })
.OrderByDescending(con => con.MaxDateCreated)
.ToArray()

Here is what's going to get generated

SELECT 
[Project2].[C1] AS [C1], 
[Project2].[Id] AS [Id], 
[Project2].[C2] AS [C2]
FROM ( SELECT 
    [Project1].[Id] AS [Id], 
    1 AS [C1], 
     CAST( [Project1].[C1] AS datetime2) AS [C2]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        (SELECT 
            MAX([Extent2].[DateCreated]) AS [A1]
            FROM [dbo].[Message] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[ConversationId]) AS [C1]
        FROM [dbo].[Conversation] AS [Extent1]
    )  AS [Project1]
)  AS [Project2]
ORDER BY [Project2].[C2] DESC

edited Oct 16 '18 at 22:29

answered Oct 15 '18 at 23:36

Lowkey

836
5
12

I think this misses out on the left-join. This would work if I switched it to `MaxDateCreated = con.Messages.Select(x => x.DateCreated).DefaultIfEmpty(DateTime.MaxValue).Max()`. In either case, though, `Max()` is getting evaluated locally rather than in the DB. – mikebridge Oct 16 '18 at 22:07
No it does not. I edited the post with the generated query, which is equivalent to the left-join result that you wanted. You don't need `DefaultIfEmpty` since EF will do that for you already. – Lowkey Oct 16 '18 at 22:27
I get a `System.InvalidOperationException : Sequence contains no elements.` error on `Max()` when it hits an empty set of `.Messages`. – mikebridge Oct 16 '18 at 22:35

NetMage · Accepted Answer · 2018-10-16T22:43:52.697

1

I believe this will generate the SQL you are looking for (plus or minus a nested sub-query):

var ans = from c in Conversation
          join m in Message on c.Id equals m.ConversationId into mj
          from m in mj.DefaultIfEmpty()
          group m by c.Id into mg
          orderby mg.Max(m => m.DateCreated) descending
          select mg.Key;

However, in LINQ you can use group join instead of regular join. This should also do what you want, but uses a sub-select in SQL, so I am not sure which is more efficient on the server. The common wisdom seems to be join was better, but following by group by may make it moot, as may a modern enough optimizer. Another post I found said the sub-query (CIS) was much more efficient, so this may be better.

var ans2 = from c in Conversation
           join m in Message on c.Id equals m.ConversationId into mj
           orderby mj.Max(m => m.DateCreated) descending
           select c.Id;

edited Oct 16 '18 at 22:43

answered Oct 16 '18 at 22:38

NetMage

26,163
3
34
55

Thanks, the first version seems to work. I added a parameter to `DefaultIfEmpty` so that null values would come out on top, which LINQ isn't able to translate into EF, but I can deal with that. (The second doesn't handle the empty set of messages) – mikebridge Oct 17 '18 at 14:42
I had arrived at a near-equivalent query which wasn't working---the only difference between mine and yours was that projection was happening differently in different places. I guess I need to keep those as simple as possible at each step of the query. – mikebridge Oct 17 '18 at 14:44
@mikebridge The second worked correctly in LINQ to SQL on my MS SQL Server database, but EF isn't always the same. – NetMage Oct 17 '18 at 21:50

SQL: SELECT id, MAX(y.DateCreated) FROM x LEFT JOIN y in Linq?

3 Answers3