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