LINQ generates incorrect SQL (reference to a non-existent table)

Question

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

}

Answer 1

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.