Why this LINQ expressoin returns the sublist with only one item

Question

Here is my object model.

public class ItemViewModel
{
    public Item ItemObject { get; set; }
    public IEnumerable<LineItem> Fields { get; set; } = new List<LineItem>();
}

public class Item
{
    public int ItemId { get; set; }
    public DateTime DatePurchased { get; set; }
}

public class LineItem
{
    public int ItemId { get; set; }
    public string Name { get; set; }
}

The database contains Items and LineItems tables, corresponding to the object model. I am using the below written LINQ expression to retrieve an ItemViewModel object, provided an Item ID.

var i = from i in _dbContext.Items
        where i.ItemId == 123
        select new ItemViewModel
        {
            ItemObject = i,
            Fields = from field in _dbContext.LineItems
                     where field.ItemId == i.ItemId
                     select field
        };            
var viewModel = await i.SingleOrDefaultAsync();

The code compiles fine but after its execution the viewModel.Fields property contains only one list item whereas there are three Line Items against ItemID:123, in the database table.

The SQL generated by this snippet (captured through SQL Profiler) returns three line items, through a LEFT JOIN. That means the generated SQL is fine but something is not going well with Entity Framework but I don't know what?

Update: Here is the generated SQL query which correctly gives me three rows:

SELECT 
    [t].[ItemId], [t].[DatePurchased], 
    [s0].[ItemId], [s0].[Name]
FROM 
    (
    SELECT 
        TOP(2) [s].[ItemId], [s].[DatePurchased]
    FROM 
        [Items] AS [s]
    WHERE 
        [s].[ItemId] = 123
    ) AS [t]
LEFT JOIN [LineItems] AS [s0] ON [t].[ItemId] = [s0].[ItemId]
ORDER BY [t].[ItemId]

Tech stack is .NET Core 3.1 and Entity Framework Core.

@John yes, I can but that would make this question unnecessarily lengthy. Do you think the generated SQL will help you anyway? — Nabeel, Mar 10 '20 at 03:46
Is `LineItems` **keyless** entity type? If yes, this is the second time I hear about this and I can confirm it is EFC bug. Also it not only returns 1 child per parent, but also one and the same (probably first) record, right? I can't find a bug filled in EF Core issue tracker, consider posting the issue there - https://github.com/dotnet/efcore/issues. — Ivan Stoev, Mar 10 '20 at 07:53
@IvanStoev bug filed: https://github.com/dotnet/efcore/issues/20247 — Nabeel, Mar 10 '20 at 23:27

Nabeel · Accepted Answer · 2020-03-11T04:47:48.863

As suggested by Ivan Stoev in the comments under this question, the reason is a bug in Entity Framework Core, when dealing with keyless entities as a child entity. I tested it first-hand by adding a primary key to my earlier keyless table and then verifying the same LINQ statement. After adding a primary key, it starts giving me a List of 3 items, instead of only 1. So after this question and my testing, I have reported it to EF Core issue tracker.

For anyone who stumbles upon this question in search of answer, we have two options here. Either add a primary key to your keyless table or define a composite primary key in your EF entity model. I fixed my situation using the below line:

modelBuilder.Entity<LineItem>().HasKey(i => new { i.ItemId, i.Name });

It works because LineItem.ItemId and LineItem.Name combination is unique for my entities. Credit for this answer goes to an answer (although not officially accepted answer) to an earlier SO question.

Why this LINQ expressoin returns the sublist with only one item

1 Answers1