How to translate an advanced SQL statement with LEFT OUTER JOIN to LINQ?

Question

I have a similar SQL statement as shown in this example. For the following table

CREATE TABLE [docs] (
  [id] int NOT NULL,
  [rev] int NOT NULL,
  [content] varchar(200) NOT NULL,
  PRIMARY KEY ([id],[rev])
) ;

and the following data

INSERT INTO [docs] ([id], [rev], [content]) VALUES
  (1, 1, 'The earth is flat'),
  (2, 1, 'One hundred angels can dance on the head of a pin'),
  (1, 2, 'The earth is flat and rests on a bull``s horn'),
  (1, 3, 'The earth is like a ball.');

the SQL statement

SELECT d1.*
    FROM docs AS d1
    LEFT OUTER JOIN docs AS d2
    ON (d1.id = d2.id AND d1.rev < d2.rev)
    WHERE d2.id is null
    ORDER BY id;

shows only rows with maximum rev value for each id:

id  rev content
1   3   The earth is like a ball.
2   1   One hundred angels can dance on the head of a pin

My question: How can I translate this statement to LINQ-to-SQL? The problem in my point of view are the AND and the < in the ON clause.

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Oct 09 '19 at 15:59

Bernd Morgeneyer · Answer 1 · 2019-10-10T11:14:03.747

I tried to apply @NetMage's recipe but I got stuck at the < condition:

   using (MyDataContext context = new MyDataContext())
    {
        var query =
            from d1 in context.docs
            join d2 in context.docs on d1.id equals d2.id into jrv
            from x in jrv.Where(x => /* ??? */).DefaultIfEmpty()
            where x equals null
            select x;
        return query.ToArray();
    }

The lambda expression in Where should be a comparison between d1.rev and d2.rev. How can I do that?

score 0 · Accepted Answer · answered Oct 10 '19 at 23:38

In general, it is best to translate a SQL LEFT JOIN...WHERE ... = null into an EXISTS which in LINQ is Any:

var ans = from d1 in docs
          join d2 in docs on d1.id equals d2.id into d2j
          where !d2j.Any(d2 => d1.rev < d2.rev)
          orderby d1.id
          select d1;

But, of course, you can translate it into an explicit LINQ null test:

using (MyDataContext context = new MyDataContext()) {
    var query =
        from d1 in context.docs
        join d2 in context.docs on d1.id equals d2.id into d2j
        from d2 in d2j.Where(d2_2 => d1.rev < d2_2.rev).DefaultIfEmpty()
        where d2 == null
        select d1;
    return query.ToArray();
}

This solution solves my problem. (I used the second one because it seems closer to the original SQL statement.) Thank you! — Bernd Morgeneyer, Oct 11 '19 at 14:04

How to translate an advanced SQL statement with LEFT OUTER JOIN to LINQ?

2 Answers2