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

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?

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;

Я не могу переписать этот SQL в EF Linq:SELECT Conversation.IdFROM Conversation LEFT JOIN Message ON Conversation.Id = Message. SELECT "select_list" LEFT JOIN TableY Y. ON X.Key = Y.Key. WHERE Y.Key IS NUL. Example Of SQL Left Excluding Join : SELECT X.PK, X.Value, LEFT JOIN TableY Y. WHERE Y.PK IS NULL. Different type of SQL JOIN. The types of JOIN you can use and the differences between them, Left Join Excluding Inner Join. Outer Excluding Join.

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)

I need to translate SQL query to LINQ and have no idea how. Date ) AS most_recent INNER JOIN Bins ON most_recent.BinId = Bins.Id Date).Max() }; var mostRecents = from itm in latestRecords join bin in _db. Id select new { bin. Where(x => x. Where(y => !y. left join with up to one row in the right table in LINQ. 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;

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

c# · entity-framework · linq · linq-to-sql. I am trying to grab data from a View into a Model using Entity LINQ/Lambda. And I created a View to grab all items and their respective reviews: Create View ItemReviewView AS SELECT i. AS [​Comment] FROM Items i LEFT JOIN Reviews r ON r.ItemId = i.Id. The entity model I have  SQL SELECT MIN, MAX, with Date, Row, Id. Problem: Find the last order date in 2013 SELECT MAX(OrderDate) FROM [Order] WHERE YEAR(OrderDate) = 2013

Aaron Bertrand (@AaronBertrand) shows how to better optimize SQL Server a clustered index on an IDENTITY column or DateCreated column), then the (​and, for more complex queries, the WHERE and JOIN clauses). with pg( select id from table order by columns offset x rows fetch next y rows only ) LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records. LEFT JOIN and LEFT OUTER JOIN are the same. The definitive guide. for data professionals. See 2 min video. The SQL LEFT JOIN syntax. The general syntax is: SELECT column-names. FROM table-name1 LEFT JOIN table-name2.

EF Core has a concept of mixed client/database evaluation in LINQ queries LEFT OUTER join for instance), so do that on your own risk (and consider the first Count(); or await (from x in _dbContext.People where x.Type == 1 select x.Id). Max(t => t.ID); var v = db.First(t => t.ID == maxID_of_Client); It's consisting of  Max (LINQ) Enumerable.Max is extension method from System.Linq namespace. It returns maximal value of numeric collection. Max for Numeric Types. Gets maximal number from list of integer numbers.

MAX() function with Having. In this article we have discussed how SQL HAVING CLAUSE can be used along with the SQL MAX() to find the maximum value of a column over each group. The SQL HAVING CLAUSE is reserved for aggregate function. The usage of WHERE clause along with SQL MAX() have also described in this page.

Comments
  • Perhaps my SQL to LINQ Recipe would help you?
  • I think, you may get some ideas from this post. docs.microsoft.com/en-us/ef/ef6/querying/related-data
  • @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.
  • 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?)
  • Also, what does MAX evaluate to when there are no matching Message rows and what order does that end up in?
  • 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)
  • 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 The second worked correctly in LINQ to SQL on my MS SQL Server database, but EF isn't always the same.
  • You can just do form ru in r.Messages.DefaultIfEmpty() instead.
  • @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.
  • 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.
  • 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.
  • I get a System.InvalidOperationException : Sequence contains no elements. error on Max() when it hits an empty set of .Messages.