Converting complicated LEFT OUTER SQL query to LINQ

Question

I have an (ugly) MS SQL LEFT JOIN query and I would like to write it using LINQ.

select a.VersionId 
FROM (SELECT root.VersionId, root.[Xml], versions.VersionId as replacedBy
  FROM [Entities] as root
    LEFT OUTER JOIN [Entities] as versions
    on root.EntityId = versions.EntityId AND root.VersionId = versions.ReplacedVersionID
      where root.EntityId = @EntityId) as a
where a.replacedBy IS NULL AND a.Xml IS NOT NULL

I know that I can use composite keys in join, and until now I came up with this:

var versionsLinq = from root in entities
  join versions in entities on new { entId = versions.EntityId, replId = versions.ReplacedVersionId }
  equals new { entId = root.EntityId, replId = root.VersionId }
  into joinedList
  from entities in joinedList.DefaultIfEmpty()
...

but this is where I get stuck. Any help would be appreciated.

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 02 '19 at 22:15

score 1 · Answer 1 · answered Oct 02 '19 at 22:24

This isn't too complicated if translated in pieces.

First, translate the subquery into a variable:

var asq = from root in entities
          where root.EntityId == EntityId
          join versions in entities on new { root.EntityId, root.VersionId } equals new { versions.EntityId, VersionId = versions.ReplacedVersionID } into versionsj
          from versions in versionsj.DefaultIfEmpty()
          select new {
              root.VersionId,
              root.Xml,
              replacedBy = versions.VersionId
          };

Then, translate the main query:

var ans = from a in asq
          where a.replacedBy == null && a.Xml != null
          select a.VersionId;

Converting complicated LEFT OUTER SQL query to LINQ

1 Answers1