使用内连接和 2 个左连接（带子查询）的 SQL 到 Linq 转换

Question

我有以下查询，它在 SSMS 中运行良好。我使用 LinqPad (C#)，但在 LinqToSql 中成功使用左外连接确实令人费解：

SELECT DISTINCT 
  A.LocID, 
  V1.PrfValue AS pID, 
  V2.PrfValue AS sID,
  D.DivisionManager, 
  A.IsApproved, 
  A.DateCreated
FROM         
  dbo.Locations AS A 
INNER JOIN
  dbo.Divisions AS D 
  ON    A.DivisionID = D.DivisionID 
LEFT OUTER JOIN
  dbo.ValuesInLocations AS V1 
  ON    A.LocID = V1.LocID 
    AND 
    V1.PrfID IN (SELECT 
            PrfID 
                 FROM 
            dbo.PrfTag 
             WHERE 
            (LevelTypeID = 1)) 
LEFT OUTER JOIN
   dbo.ValuesInLocations AS V2 
   ON   A.LocID = V2.LocID 
    AND 
    V2.PrfID IN (SELECT 
            PrfID 
             FROM 
            dbo.PrfTag 
             WHERE 
            (LevelTypeID = 2))

如您所见，这不是开始工作的最优雅的查询，我同意子查询两个左连接都可以改进。不过，你能帮我翻译一下吗？

Answer 1

以下是您的查询的 2 种可能的翻译。我在第一个翻译中使用了 3 个单独的查询，以使其更具可读性。我希望您发现它们很有用。

var query1 =
    from prfTag in DataContext.PrfTag
    where prfTag.LevelTypeID = 1
    select PrfID;

var query1 =
    from prfTag in DataContext.PrfTag
    where prfTag.LevelTypeID = 2
    select PrfID;

var query = (
from A in DataContext.Locations
join D in DataContext.Divisions
    on A.DivisionID equals D.DivisionID
join V1 in DataContext.ValueInLocations
    on A.LocID equals V1.LocID
    into VGroup1
from V1 in VGroup1.DefaultIfEmpty()
join V2 in DataContext.ValueInLocations
    on A.LocID equals V2.LocID
    into VGroup2
from V2 in VGroup2.DefaultIfEmpty()
where (V1 == null || (V1 != null && query1.Contains(V1.PrfID)))
    && (V2 == null || (V2 != null && query2.Contains(V2.PrfID)))
select new
{
    A.LocID,
    pID = V1 != null ? V1.PrfValue : "",
    sID = V2 != null ? V2.PrfValue : "",
    D.DivisionManager,
    A.IsApproved,
    A.DateCreated
}).Distinct();

这是第二种可能的翻译：

var query = (
from A in DataContext.Locations
join D in DataContext.Divisions
    on A.DivisionID equals D.DivisionID
join V1 in DataContext.ValueInLocations
    on A.LocID equals V1.LocID
    into VGroup1
from V1 in VGroup1.DefaultIfEmpty()
join prfTag1 in DataContext.PrfTag
    on V1.PrfID equals prfTag1.PrfID
join V2 in DataContext.ValueInLocations
    on A.LocID equals V2.LocID
    into VGroup2
from V2 in VGroup2.DefaultIfEmpty()
join prfTag2 in DataContext.PrfTag
    on V2.PrfID equals prfTag2.PrfID
select new
{
    A.LocID,
    pID = V1 != null ? V1.PrfValue : "",
    sID = V2 != null ? V2.PrfValue : "",
    D.DivisionManager,
    A.IsApproved,
    A.DateCreated
}).Distinct();

Answer 2

在 LINQ 中可能有一些棘手的方法可以做到这一点，但除了内部联接之外，LINQ JOIN 对于任何其他操作都是痛苦的。为了可维护性，我不鼓励对此查询使用 linq。我知道这并不能完全回答您的问题，但我认为您不会得到比该查询更好的答案。