entity-framework - 将复杂的 LEFT OUTER SQL 查询转换为 LINQ

Question

我有一个（丑陋的）MS SQLLEFT JOIN查询，我想用 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

我知道我可以在连接中使用复合键，直到现在我想出了这个：

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()
...

但这是我卡住的地方。任何帮助，将不胜感激。

标签： entity-frameworklinq

Answer 1

如果分段翻译，这并不太复杂。

首先，将子查询转换为变量：

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
          };

然后，翻译主查询：

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