c# - LINQ 语句生成与 LinqPad 不同的查询

Question

我有以下代码，通过 LEFT JOIN 查询一些数据:

Model1 db = new Model1();

db.Database.Log = System.Console.Write;

var x = from e in db.Employees
        where e.id == 746
        join f in db.Company on e.CompanyID equals f.ID into fa
        from fr in fa.DefaultIfEmpty()
        select new
        {
          e.id,
          e.name,
          companyName = fr.name
        };

x.Any(); // to see the query in console

此处使用的类由 Visual Studio 向导生成，以确保模型和数据库上下文代码正确。

当我在 LINQpad 中运行该代码时，SQL 查询如下所示

SELECT [t0].[id] AS [id], [t0].[name] AS [name], [t1].[name] AS [companyName]
FROM [Employees] AS [t0]
LEFT OUTER JOIN [Company] AS [t1] ON [t0].[CompanyID] = [t1].[ID]
WHERE [t0].[id] = 1615

但是当我在一个简单的控制台应用程序中运行代码时，我得到以下完全错误的神秘查询:

SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[Employees] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Company] AS [Extent2] ON ([Extent1].[CompanyID] = [Extent2].[ID]) OR (([Extent1].[CompanyID] IS NULL) AND ([Extent2].[ID] IS NULL))
        WHERE 1615 = [Extent1].[id]
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

我花了几个小时试图找到解决方案，但 LINQ 无法正确获取查询。

有人遇到同样的问题并以某种方式解决了吗？非常感谢任何帮助。

Answer 1

这个查询对我来说看起来是正确的。 LINQpad 查询看起来有所不同，因为在您的代码中使用 Any()。

在 LINQpad 中尝试以下查询。它应该显示相同的 sql:

 (from e in Employees
    where e.id == 746
    join f in Company on e.CompanyID equals f.ID into fa
    from fr in fa.DefaultIfEmpty()
    select new
    {
      e.id,
      e.name,
      companyName = fr.name
    }).Any()

要在控制台中查看正确的查询，您可以使用 ToList() 而不是 Any()