LINQ生成不正确的SQL（引用不存在的表） [英] LINQ generates incorrect SQL (reference to a non-existent table)

查看：171
本文介绍了LINQ生成不正确的SQL（引用不存在的表）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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



在另一篇文章中（返回属于列表中所有标签的产品使用LINQ ），我收到协助创建LINQ语句以返回一个数据子集。



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



注意，为了保持这个长时间的长度，我不会发布对象类（Product，Tag和ProductTag），但它们在我之前的问题中列出：



LINQ： 

  var tags =administration + commerce
 var tagParams = tags.Split （ '+'）ToList（）; //在linq语句中使用
 
 _repository.Products.Where（p => tagParams.All（tag => p.Tags.Select（x => x.Name）.Contains 。标签）））个不同的（）取（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]。[价格] AS [价格] 
 FROM [dbo]。[产品] 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'商务'AS [C1] 
 FROM（SELECT 1 AS X）AS [SingleRowTable2]）AS [UnionAll1] 
 WHERE（NOT EXISTS（SELECT 
 1 AS [C1] 
 FROM [标签]产品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]）$ ​​b $ b））OR（CASE WHEN（EXISTS $ b 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]）$ ​​b $ b ））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]）$ ​​b $ b））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]）$ ​​b $ b））OR（CASE WHEN（EXISTS（SELECT 
 1 AS [C1] 
 FROM [dbo]。[ProductTag] AS [Extent4] 
 INNER JOIN [标签] AS [Extent5] ON [Extent5]。[TagId] = [Extent4]。[TagId] 
 WHERE（[Extent1]。[ProductId] = [Extent4]。[ProductId]）AND （[Extent5]。[Name] = [UnionAll1]。[C1]）$ ​​b $ b））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]）$ ​​b $ b））THEN cast（0 as bit）END IS NULL ）
）



再次， SQL是

  [dbo]。[TagProduct]更改为[dbo]。[Prod uctTag] 
 [ExtentN]。[Tag_TagId]更改为[ExtentN]。[TagId]

注意我确保数据库没有名为dbo.TagProduct的对象，并且我的代码中没有引用TagProduct（也没有）。



我的LINQ语句有问题，还是LINQ错误？我完全可以打破它，只是创建一个存储过程，但我宁愿找到一个修复。



感谢您的长信息。



编辑



问题原来是一个有缺陷的实体模型，在多对多关系之间的表之间具有过多和不必要的导航属性。新的模式如下：

  public class Product 
 {
。 
。 
 // public virtual List< Tag>标签{get;组; } //<  -  removed 
 public virtual List< ProductTag> ProductTags {get;组; } 
} 
 
 public class ProductTag 
 {
。 
。 
 public virtual Product Product {get;组; } 
 public virtual Tag Tag {get;组; } 
} 
 
 public class标签
 {
。 
。 
 // public virtual List< Product>产品{get;组; } //<  -  removed 
 public virtual List< ProductTag> ProductTags {get;组; 
 
}


解决方案

如果您在链接的帖子中的模型中的Fluent API中没有任何其他映射，生成的SQL是正确和预期的。为什么？



为了说明，我将相关的导航属性复制到你的模型，并标记哪些属于一起：

  public class Tag 
 {
 public int TagId {get;组; } 
 
 public virtual List< Product>产品{get;组; } / * 1 * / 
 public virtual List< ProductTag> ProductTags {get;组; } / * 2 * / 
} 
 
 public class Product 
 {
 public int ProductId {get;组; } 
 
 public virtual List< Tag>标签{get;组; } / * 1 * / 
} 
 
 public class ProductTag 
 {
 public int ProductTagId {get;组; } 
 
 public int ProductId {get;组; } 
 public int TagId {get;组; } 
 
 public virtual Product Product {get;组; } / * 3 * / 
 public virtual Tag Tag {get;组; } / * 2 * / 
}

所以，你有一个 标签和之间的多对关系（ / * 1 * / ） ，之间的一对多关系（ / * 2 * / ）标签和 ProductTag 和一对多关系（ / * 3 * / 产品和 ProductTag 在产品中的导航属性



由于您在Fluent API中没有多对多关系的映射，实体框架将期望数据库遵循映射约定的表，也就是：




  • 一个多对多连接表，名为 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 导航属性类

    • 如下所示，定义Fluent API中的映射（对应于名为 ProductTag 的连接表，其中包含两列 ProductId 和 TagId ，其形成复合主键，并且是产品和标签 table）


因此，您只会有一个单一的关系（产品和标签），而不是三个关系，我希望您的查询可以正常工作。


MVC3 project, using LINQ to Entity, and Entity Framework 4 Code-First.

In another post ( Return products which belong to all tags in a list using LINQ ), I received assistance in creating a LINQ statement to return a subset of data.

The LINQ is syntactically correct and compiles, but generates incorrect SQL. Specifically, it makes reference to a non-existent table. If I correct the table name, it returns the correct data, so the LINQ seems to be correct.

Note in the interest of keeping this long post from getting even longer, I wont post the object classes (Product, Tag, and ProductTag), but they are listed in my previous question here: Return products which belong to all tags in a list using LINQ

The 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();


Following is the incorrect and correct SQL code.

The incorrect SQL makes references to non-existent table 

[dbo].[TagProduct]

as well as a malformed field 

[ExtentN].[Tag_TagId]

If I correct these to "[dbo].[ProductTag]" and "[ExtentN].[TagId]", the SQL executes correctly and returns the correct data.

The LINQ-generated (and faulty) 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)
)

The corrected 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)
)


Again, the only changes in the SQL is

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

Note I've ensured that the database has no object named dbo.TagProduct, and no references exist in my code to TagProduct (nor has it ever).

Is there a problem in my LINQ statement, or is this a LINQ bug? I'm ok with scrapping it altogether and just creating a stored-procedure, but I'd rather find a fix.

Thanks and apologies for the long post.

EDIT

The problem turned out to be a flawed entity model, with excessive and unnecessary navigation properties between the tables in a many-to-many relationship. Slauma's detailed answer was key in understanding what was happening.

The new model is as follows:

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

}

解决方案

If you don't have any additional mapping in Fluent API in the model in your linked post the generated SQL is correct and expected. Why?

To make it clear I copy your model with the relevant navigation properties and mark which belong together:

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 */
}

So, you have a many-to-many relationship (/* 1 */) between Tag and Product, a one-to-many relationship (/* 2 */) between Tag and ProductTag and a one-to-many relationship (/* 3 */) between Product and ProductTag where the navigation property in Product is not exposed.

Because you don't have a mapping for the many-to-many relationship in Fluent API Entity Framework will expect database tables which follow mapping conventions - and that is:

  • A many-to-many join table called ProductTags or TagProducts. If you have disabled pluralization it will expect ProductTag or TagProduct. I say "or" because the name depends on factors like the order of the sets in your derived context and perhaps even the order of navigation properties in your classes, etc. So, it's difficult to predict the name in a complex model - basically the reason why it is recommended to define many-to-many relationships always explicitely in Fluent API.

  • One key column in the table with name EntityClassName_EntityKeyName -> Tag_TagId

  • The other key column in the table with Product_ProductId

In your query only this many-to-many relationship is involved (you are using only Product.Tags as the only navigation property in the query). So, EF will create a SQL query which includes the join table (it happens to be TagProduct in your case, but as said, only by accident) and the key column names of the join table which are Tag_TagId and Product_ProductId.

You can define the many-to-many mapping in Fluent API by:

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

This will create problems though because you already have a ProductTag entity which apparently already has the corresponding table ProductTag. This can't be the join table for your many-to-many relationship at the same time. The join table must have another name, like x.ToTable("ProductTagJoinTable").

I'm wondering if you really want those mentioned three relationships. Or why do you expect the table name ProductTag belonging to the ProductTag entity? This table and entity isn't involved in your query at all.

Edit

Proposal to change your model: Your ProductTag entity doesn't contain any additional fields except the fields necessary for a many-to-many join table. Therefore I would map it as a pure many-to-many relationship. This means:

  • Delete the ProductTag entity class from your model
  • Delete the ProductTags navigation property from your Tag class
  • Define the mapping in Fluent API as shown above (corresponding to a join table named ProductTag with two columns ProductId and TagId which form a composite primary key and are foreign keys to the Product and Tag table respectively)

As a result you will only have a single relationship (many-to-many between Product and Tag) and not three relationships and I expect that your query will work.

这篇关于LINQ生成不正确的SQL（引用不存在的表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆