c# - LINQ 生成不正确的 SQL(引用不存在的表)

Question

MVC3 项目，使用 LINQ to Entity 和 Entity Framework 4 Code-First。

在另一篇文章 ( Return products which belong to all tags in a list using LINQ) 中，我在创建 LINQ 语句以返回数据子集方面获得了帮助。

LINQ 在语法上是正确的并且可以编译，但生成的 SQL 不正确。具体来说，它引用了一个不存在的表。如果我更正表名，它会返回正确的数据，因此 LINQ 似乎是正确的。

请注意，为了避免这篇长文章变得更长，我不会发布对象类(Product、Tag 和 ProductTag)，但它们列在我之前的问题中:Return products which belong to all tags in a list using LINQ

LINQ:

var tags = "administration+commerce"
var tagParams = tags.Split('+').ToList();   //used in linq statement below

_repository.Products.Where(p => tagParams.All(tag => p.Tags.Select(x => x.Name).Contains(tag))).Distinct().Take(75).ToList();   

以下是错误和正确的 SQL 代码。

不正确的SQL引用了不存在的表

[dbo].[TagProduct] 

还有一个畸形的字段

[ExtentN].[Tag_TagId]

如果我将这些更正为“[dbo].[ProductTag]”和“[ExtentN].[TagId]”，SQL 将正确执行并返回正确的数据。

LINQ 生成的(错误的)SQL

SELECT 
[Extent1].[ProductId] AS [ProductId], 
[Extent1].[Name] AS [Name], 
[Extent1].[ShortDescription] AS [ShortDescription], 
[Extent1].[LongDescription] AS [LongDescription], 
[Extent1].[Price] AS [Price]
FROM [dbo].[Product] AS [Extent1]
WHERE  NOT EXISTS (SELECT 
    1 AS [C1]
    FROM  (SELECT 
        N'administration' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        N'commerce' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
    WHERE ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[TagProduct] AS [Extent2]
        INNER JOIN [dbo].[Tag] AS [Extent3] ON [Extent3].[TagId] = [Extent2].[Tag_TagId]
        WHERE ([Extent1].[ProductId] = [Extent2].[Product_ProductId]) AND ([Extent3].[Name] = [UnionAll1].[C1])
    )) OR (CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[TagProduct] AS [Extent4]
        INNER JOIN [dbo].[Tag] AS [Extent5] ON [Extent5].[TagId] = [Extent4].[Tag_TagId]
        WHERE ([Extent1].[ProductId] = [Extent4].[Product_ProductId]) AND ([Extent5].[Name] = [UnionAll1].[C1])
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[TagProduct] AS [Extent6]
        INNER JOIN [dbo].[Tag] AS [Extent7] ON [Extent7].[TagId] = [Extent6].[Tag_TagId]
        WHERE ([Extent1].[ProductId] = [Extent6].[Product_ProductId]) AND ([Extent7].[Name] = [UnionAll1].[C1])
    )) THEN cast(0 as bit) END IS NULL)
)

更正后的 SQL

SELECT 
[Extent1].[ProductId] AS [ProductId], 
[Extent1].[Name] AS [Name], 
[Extent1].[ShortDescription] AS [ShortDescription], 
[Extent1].[LongDescription] AS [LongDescription], 
[Extent1].[Price] AS [Price]
FROM [dbo].[Product] AS [Extent1]
WHERE  NOT EXISTS (SELECT 
    1 AS [C1]
    FROM  (SELECT 
        N'administration' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        N'commerce' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
    WHERE ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[ProductTag] AS [Extent2]
        INNER JOIN [dbo].[Tag] AS [Extent3] ON [Extent3].[TagId] = [Extent2].[TagId]
        WHERE ([Extent1].[ProductId] = [Extent2].[ProductId]) AND ([Extent3].[Name] = [UnionAll1].[C1])
    )) OR (CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[ProductTag] AS [Extent4]
        INNER JOIN [dbo].[Tag] AS [Extent5] ON [Extent5].[TagId] = [Extent4].[TagId]
        WHERE ([Extent1].[ProductId] = [Extent4].[ProductId]) AND ([Extent5].[Name] = [UnionAll1].[C1])
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[ProductTag] AS [Extent6]
        INNER JOIN [dbo].[Tag] AS [Extent7] ON [Extent7].[TagId] = [Extent6].[TagId]
        WHERE ([Extent1].[ProductId] = [Extent6].[ProductId]) AND ([Extent7].[Name] = [UnionAll1].[C1])
    )) THEN cast(0 as bit) END IS NULL)
)

同样，SQL 中唯一的变化是

[dbo].[TagProduct] changed to [dbo].[ProductTag]
[ExtentN].[Tag_TagId] changed to [ExtentN].[TagId]

请注意，我已确保数据库中没有名为 dbo.TagProduct 的对象，并且我的代码中不存在对 TagProduct 的引用(也从来没有)。

我的 LINQ 语句是否有问题，或者这是一个 LINQ 错误？我可以完全放弃它并只创建一个存储过程，但我宁愿找到一个修复程序。

感谢并为冗长的帖子道歉。

编辑

问题原来是一个有缺陷的实体模型，在多对多关系的表之间有过多和不必要的导航属性。 Slauma 的详细回答是理解正在发生的事情的关键。

新模型如下:

public class Product
{
    .
    . 
    //public virtual List<Tag> Tags { get; set; }             // <--removed
    public virtual List<ProductTag> ProductTags { get; set; }
}

public class ProductTag
{
    .
    . 
    public virtual Product Product { get; set; }
    public virtual Tag Tag { get; set; }
}

public class Tag
{
    .
    . 
    //public virtual List<Product> Products { get; set; }      // <--removed
    public virtual List<ProductTag> ProductTags { get; set; }

}

Answer 1

如果您在链接帖子的模型中没有任何额外的 Fluent API 映射，则生成的 SQL 是正确的并且符合预期。为什么？

为了清楚起见，我复制了您的模型以及相关的导航属性和标记:

public class Tag
{
    public int TagId { get; set; }

    public virtual List<Product> Products { get; set; }         /* 1 */
    public virtual List<ProductTag> ProductTags { get; set; }   /* 2 */
}

public class Product
{
    public int ProductId { get; set; }

    public virtual List<Tag> Tags { get; set; }                 /* 1 */
}

public class ProductTag
{
    public int ProductTagId { get; set; }

    public int ProductId { get; set; }
    public int TagId { get; set; }

    public virtual Product Product { get; set; }                /* 3 */
    public virtual Tag Tag { get; set; }                        /* 2 */
}

因此，您在 /* 1 */ 和 Tag 之间有一个多对多关系 ( Product )，在 /* 2 */ 和 Tag 之间有一个一对多 关系 ( ProductTag )以及 /* 3 */ 和 Product 之间的一对多关系 ( ProductTag )，其中 Product 中的导航属性未公开。

因为您在 Fluent API Entity Framework 中没有多对多关系的映射，所以需要遵循映射约定的数据库表 - 即:

• 名为 ProductTags 或 TagProducts 的多对多连接表。如果您禁用复数化，它将期望 ProductTag 或 TagProduct 。我说“或”是因为名称取决于派生上下文中集合的顺序，甚至可能是类中导航属性的顺序等因素。因此，很难预测名称在复杂模型中——这基本上就是为什么建议在 Fluent API 中始终明确定义多对多关系的原因。

• 表中的一个键列名称为 EntityClassName_EntityKeyName -> Tag_TagId

• 表中其他关键列为Product_ProductId

在您的查询中只涉及这种多对多关系(您只使用 Product.Tags 作为查询中唯一的导航属性)。因此，EF 将创建一个 SQL 查询，其中包括连接表(在您的情况下它恰好是 TagProduct，但如前所述，只是偶然)和连接表的键列名称 Tag_TagId 和 Product_ProductId。

您可以通过以下方式在 Fluent API 中定义多对多映射:

modelBuilder.Entity<Product>()
    .HasMany(p => p.Tags)
    .WithMany(t => t.Products)
    .Map(x =>
    {
        x.MapLeftKey("ProductId");
        x.MapRightKey("TagId");
        x.ToTable("ProductTag");
    });

但这会产生问题，因为您已经有一个 ProductTag 实体，它显然已经有相应的表 ProductTag 。这不能同时成为您的多对多关系的连接表。连接表必须有另一个名称，例如 x.ToTable("ProductTagJoinTable") 。

我想知道您是否真的想要上述三种关系。或者你为什么期望表名ProductTag属于ProductTag实体？此表和实体根本不涉及您的查询。

编辑

更改模型的建议:您的 ProductTag 实体不包含任何其他字段，除了多对多连接表所需的字段。因此，我会将其映射为纯粹的多对多关系。这意味着:

• 从模型中删除 ProductTag 实体类
• 从 ProductTags 类中删除 Tag 导航属性
• 如上所示在Fluent API中定义映射(对应名为ProductTag的连接表，其中两列ProductId和TagId组成复合主键，分别是Product和Tag表的外键)

因此，您将只有一个关系(Product 和 Tag 之间的多对多关系)，而不是三个关系，我希望您的查询有效。