LINQ生成不正确的SQL（参照一个不存在的表） [英] LINQ generates incorrect SQL (reference to a non-existent table)
问题描述
MVC3项目，使用LINQ to实体，和Entity Framework 4 code-第一。
在另一篇文章中（<一个href=\"http://stackoverflow.com/questions/9074875/return-products-which-belong-to-all-tags-in-a-list-using-linq\">Return属于列表中使用LINQ ）所有标签的产品，我在创建LINQ语句返回数据子集的援助。
在LINQ语法正确，编制，但会产生不正确的SQL。具体地，它使参考一个不存在的表。如果我更正表名，它返回正确的数据，因此LINQ似乎是正确的。
请注意在保持这种长期职位从得到更长的利益，我不会张贴的对象类（产品，标签和ProductTag），但他们在我的previous这里列出的问题：<一href=\"http://stackoverflow.com/questions/9074875/return-products-which-belong-to-all-tags-in-a-list-using-linq\">Return属于所有标签中使用LINQ
产品列表的LINQ：
VAR标记=+行政商务
变种tagParams = tags.Split（+）了ToList（）; //下面LINQ语句中使用_repository.Products.Where（P =＆GT; tagParams.All（标签=＆GT; p.Tags.Select（X =＆GT; x.Name）。载有（标签）））。鲜明的（）取（75）.ToList （）;
以下是不正确的，正确的SQL code。
不正确的SQL使得引用不存在的表
[DBO]。[TagProduct]
以及畸形场
[ExtentN]。[Tag_TagId]
如果我纠正这些为[DBO]。[ProductTag]和[ExtentN]。[TAGID]，在SQL正确执行，并返回正确的数据。
的LINQ产生的（和错误的）SQL
SELECT
[Extent1]。[产品编号] AS [产品编号]
[Extent1]。[名] AS [名]
[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
选择
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]。[标签] AS [Extent3] ON [Extent3]。[TAGID] = [Extent2]。[Tag_TagId]
WHERE（[Extent1]的[ProductID] = [Extent2]。[Product_ProductId]）AND（[Extent3]。[名] = [UnionAll1] [C1]）
））OR（CASE WHEN（EXISTS（SELECT
1 AS [C1]
FROM [DBO]。[TagProduct] AS [Extent4]
INNER JOIN [DBO]。[标签] AS [Extent5] ON [Extent5]。[TAGID] = [Extent4]。[Tag_TagId]
WHERE（[Extent1]的[ProductID] = [Extent4]。[Product_ProductId]）AND（[Extent5]。[名] = [UnionAll1] [C1]）
））再投（1为位）WHEN（NOT EXISTS（SELECT
1 AS [C1]
FROM [DBO]。[TagProduct] AS [Extent6]
INNER JOIN [DBO]。[标签] AS [Extent7] ON [Extent7]。[TAGID] = [Extent6]。[Tag_TagId]
WHERE（[Extent1]的[ProductID] = [Extent6]。[Product_ProductId]）AND（[Extent7]。[名] = [UnionAll1] [C1]）
））再投（0为位）到底是NULL）
）
校正后的SQL
SELECT
[Extent1]。[产品编号] AS [产品编号]
[Extent1]。[名] AS [名]
[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
选择
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]。[标签] AS [Extent3] ON [Extent3]。[TAGID] = [Extent2]。[TAGID]
WHERE（[Extent1]的[ProductID] = [Extent2]的[ProductID]）AND（[Extent3]。[名] = [UnionAll1] [C1]）
））OR（CASE WHEN（EXISTS（SELECT
1 AS [C1]
FROM [DBO]。[ProductTag] AS [Extent4]
INNER JOIN [DBO]。[标签] AS [Extent5] ON [Extent5]。[TAGID] = [Extent4]。[TAGID]
WHERE（[Extent1]的[ProductID] = [Extent4]的[ProductID]）AND（[Extent5]。[名] = [UnionAll1] [C1]）
））再投（1为位）WHEN（NOT EXISTS（SELECT
1 AS [C1]
FROM [DBO]。[ProductTag] AS [Extent6]
INNER JOIN [DBO]。[标签] AS [Extent7] ON [Extent7]。[TAGID] = [Extent6]。[TAGID]
WHERE（[Extent1]的[ProductID] = [Extent6]的[ProductID]）AND（[Extent7]。[名] = [UnionAll1] [C1]）
））再投（0为位）到底是NULL）
）
再次，在SQL中唯一的变化是
[TagProduct]至[DBO]。[ProductTag]
[ExtentN]更改为[ExtentN]。[Tag_TagId]。[TAGID]
请注意，我保证数据库没有命名dbo.TagProduct对象，没有引用我的code存在TagProduct（也从未）。
有一个问题，我的LINQ语句，或者这是一个错误的LINQ？我确定有完全报废它，只是创建一个存储过程，但我宁愿找到一个解决。
感谢和道歉的长期职位。
修改
问题竟然是一个有缺陷的实体模型，在一个多一对多的关系表之间的过度和不必要的导航性能。 Slauma的详细答案是理解发生了什么事情的关键。
新的模型如下：
公共类产品
{
。
。
//公共虚拟列表＆LT;标记和GT;标签{搞定;组; } //＆LT; - 删除
公共虚拟目录＆LT; ProductTag＆GT; ProductTags {搞定;组; }
}公共类ProductTag
{
。
。
公共虚拟产品产品{搞定;组; }
公共虚拟标签标记{搞定;组; }
}公共类标签
{
。
。
//公共虚拟列表＆LT;产品与GT;产品{搞定;组; } //＆LT; - 删除
公共虚拟目录＆LT; ProductTag＆GT; ProductTags {搞定;组; }}
如果你没有在你的链接后在模型中流利的API任何额外的映射生成的SQL是正确的和预期。为什么呢？
要弄清楚我复制相关的导航性能和标志属于一起模型：
公共类标签
{
公众诠释TAGID {搞定;组; } 公共虚拟目录＆LT;产品与GT;产品{搞定;组; } / * 1 * /
公共虚拟目录＆LT; ProductTag＆GT; ProductTags {搞定;组; } / * 2 * /
}公共类产品
{
公众诠释产品编号{搞定;组; } 公共虚拟目录＆LT;标记和GT;标签{搞定;组; } / * 1 * /
}公共类ProductTag
{
公众诠释ProductTagId {搞定;组; } 公众诠释产品编号{搞定;组; }
公众诠释TAGID {搞定;组; } 公共虚拟产品产品{搞定;组; } / * 3 * /
公共虚拟标签标记{搞定;组; } / * 2 * /
}
所以，你有一个许多一对多的关系（ / * 1 * / ）之间的标记和产品，一个一对许多的关系（ / * 2 * / ）标签和 ProductTag 和一对许多关系（ / * 3 * / ）之间的产品和 ProductTag 其中产品的导航属性不被暴露。
由于你没有在流利的API实体框架的许多一对多关系的映射将期望它遵循制图惯例数据库表 - 那就是：
-
一个多一对多连接表名为
ProductTags或TagProducts。如果已经禁用了多元化它将把ProductTag或TagProduct。我说或因为这个名字取决于你的派生的上下文像集的秩序因素，甚至在你的类等等导航属性的顺序，这样，就很难$ P $在一个复杂的模型pdict的名字 - 基本上是为什么我们建议的原因在流利的API总是明确地定义许多一对多的关系 -
在名称表中的一个键列
EntityClassName_EntityKeyName- >Tag_TagId - 在表中的其他键列与
Product_ProductId
在您的查询仅此很多一对多的关系参与（你只 Product.Tags 使用在查询中唯一的导航属性）。因此，EF将创建一个SQL查询，其中包括连接表（它正好是 TagProduct 在你的情况，但正如所说，只是偶然）和键列名连接表这是 Tag_TagId 和 Product_ProductId 。
您可以定义在流利的API许多一对多映射：
modelBuilder.Entity＆LT;产品及GT;（）
.HasMany（P =＆GT; p.Tags）
.WithMany（T =＆GT; t.Products）
.MAP（X =＆GT;
{
x.MapLeftKey（产品编号）;
x.MapRightKey（TAGID）;
x.ToTable（ProductTag）;
}）;
这将创建，但因为你已经有了这显然已经有了相应的表 ProductTag 实体问题 ProductTag 。这不可能是在同一时间的多对一对多的关系连接表。连接表必须有另外一个名字，如 x.ToTable（ProductTagJoinTable）。
我想知道如果你真的想提到的那些三者的关系。或者，你为什么想到表的名称 ProductTag 属于 ProductTag 实体？此表和实体完全不涉及您的查询。
修改
关于修改模型：您 ProductTag 实体不包含除必需的许多一对多连接表中的字段的任何其他字段。因此，我将它映射为一个纯粹的许多一对多的关系。这意味着：
- 从模型中删除
ProductTag实体类 - 删除从
标签类ProductTags导航属性 - 定义用流利的API映射如上图（对应于命名连接表
ProductTag有两列产品编号和TAGID形成一个复合主键和外键的产品和标签表分别）
因此，您将只有一个关系（很多对多间产品和标签）而不是三个关系，我期待你的查询将正常工作。
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
ProductTagsorTagProducts. If you have disabled pluralization it will expectProductTagorTagProduct. 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
ProductTagentity class from your model - Delete the
ProductTagsnavigation property from yourTagclass - Define the mapping in Fluent API as shown above (corresponding to a join table named
ProductTagwith two columnsProductIdandTagIdwhich form a composite primary key and are foreign keys to theProductandTagtable 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屋！
