EF Core 5 Table Valued Functions Unable to translate collection subquery in projection since the parent query doesn't project key columns of all Exception

[lucianparvu]

Hello,

I get an Exception in EF Core 5 Table when I use: Table Valued Functions :

InvalidOperationException: Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.

var item = await (from p in context.Products                             
                              from  productPrice in context.GetProductPrice(p.ProductID)        
                             where p.ProductID == ItemID                                            
                              select new Product
                              {                                  
                                  Title = p.Title,                                  
                                  Price = productPrice.Price,
                                  ProductAtributes = p.ProductAtributes.
                                                                      .Select(pa => new ProductAtribute
                                                                      {
                                                                          Title = pa.Title,
                                                                          ProductAtributeID = pa.ProductAtributeID,
                                                                          StockQuantity = pa.StockQuantity,                                                                          
                                                                      }).ToList(),
                              }).AsNoTracking().FirstOrDefaultAsync();

// Entities

public class ProductPrice  
{        
        public int ProductPriceID { get; set; }       
        public int ProductID { get; set; }
}

public class Product
{
        public int ProductID { get; set; }
        public string Title { get; set; }
}

// App DB Context

public IQueryable<ProductPrice> GetProductPrice(           
            int ProductID) => FromExpression(() => GetProductPrice(ProductID));

 builder.Entity(typeof(ProductPrice)).HasNoKey();
builder.HasDbFunction(() => GetProductPrice(default));

[ryanbrandenburg]

https://developercommunity2.visualstudio.com/t/Entity-Framework-50-LINQ-error-when-que/1286011 appears to be a dupe of this, but with a slightly different scenario which should likely be tested when fixing the issue.

[amleonardccsd]

I'm experiencing this too. Queries that worked without any problems whatsoever in EF Core 3 don't work in EF Core 5 and give this error message.

The error occurs when:

• The query starts being built with a Keyless entity type.
• You Include and ThenInclude some collection navigation properties on that Keyless entity type OR
• You try to project the query with a collection property beyond 2 levels deep (referencing entities at the same level you would have to ThenInclude them)

Note: ThenInclude worked when including a non-collection navigation property, but didn't work for collection navigation properties.

I also used the workaround at the link in the comment above. I changed from HasNoKey to HasKey with some columns from the view that I knew would be unique, and then it started working. In the generated SQL it did not make use of the key in any of the joins.
Also, Distinct and GroupBy are not being used.

[aquacat123]

I am experiencing the exact same issue. Is there a fix or work around for this? I am upgrading to EF 5 and cannot move passed this. I am not using distinct or group by, i am doing a union on two queries that return a type with a collection property (using "select new"). Removing the part of the query that sets the collection property prevents the error from happening but also does not solve my problem:

systemLevelTemplates = (from template in _dbContext.TemplateLocationViews where let isDefaultTemplate = currentUser.DefaultTemplateId == template.TemplateId ? true : false select new TemplateDomainModel { TemplateTypeId = template.TemplateTypeId, TemplateTypeName = template.TemplateTypeName, TemplateCategoryDomainModels = (from templateCategory in _dbContext.TemplateTemplateCategories where templateCategory.TemplateId == template.TemplateId select new TemplateCategoryDomainModel { TemplateCategoryId = templateCategory.TemplateCategoryId, Name = templateCategory.TemplateCategory.Name }).AsEnumerable() }).AsEnumerable();

the break occurs here:

results = results.Union(systemLevelTemplates).ToList();

[aquacat123]

I have an update on this. This error seems to occur because the scaffolded view uses " entity.HasNoKey();" even though the view is using "ROW_NUMBER() OVER " in order to return a TempID. If i manually update the code produced by dbscaffold to "entity.HasKey" then my query works. The problem is that this is generated code so the next time a regenerate the dbcontext the change will be lost. I am trying to find a way on the sql side to tell EF core that the TempID column is a primary key.. still stuck on this.

[Mr-Technician]

As mentioned above, I worked around this by setting a key in my model class. Previously it was keyless. No error showed up until I added a Include.ThenInclude in the query.

[guidupuy]

This basically prevents the usage of AutoMapper's ProjectTo with nested projections... what's the latest on this? Are we going to see a fix?

[shubhambothara]

Any updates on this?

[ajcvickers]

@shubhambothara @guidupuy This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 6.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you. Currently, this issue only has 3 votes.

[ajcvickers]

Note from triage: putting this in the 7.0 milestone to make sure we consider the design in 7.0. The outcome of that will determine together with prioritization against other work will determine whether or not this is implemented in 7.0.

[dIeGoLi]

I had this bug as well in EF Core 5 with a similar query. To me the problem seems to be resolved in EF Core 6, maybe OP could verify.

[cmbkla]

@dIeGoLi I have bad news...

[carlsonaaron]

@dIeGoLi I have bad news...

Can confirm

[smitpatel]

In a LINQ query, the result can contain a complex structure which while have scalar values, it can also contain collection of scalar or non-scalar values. Since relational database result which uses row cannot contain anything other than scalar values, in order to generate results, EF Core has to iterate over multiple rows to generate single result in client side. There is extensive discussion around how to get related data in multiple rows for the query in single DbCommand vs using multiple DbCommands (single vs split query mode).
In either of the scenario, when iterating over rows, we need to correlate rows from outer element & inner element. For each outer element, a collection is created and it is populated with inner element which are correlated to given outer element. This process requires 3 different things to match

1. We need to make sure that outer element has changed so we can initialize new collection to populate.
2. We need to recognize if outer and inner elements are correlated with each other so that we can determine if we need to add to existing collection or we need to start a new collection.
3. We need to identify if the inner element is not duplicated and should create a new instance to be added to collection (whichever collection determined based on point 2).

While 2 is very obvious, 1 & 3 may not very straight-forward to understand why they are required. So let's look at some queries,
Query posted in #23830 (comment)
The outer element is a cross join between Product & ProductPrice where collection is being correlated with Product only. What cross join does is, repeat the Product in the result for each ProductPrice, so we will have duplicated Product in result and according to 1 we need to identify where to put ProductAttribute inner elements.
Similarly query in #28130 (comment) while performing LEFT JOIN (from..from with DefaultIfEmpty), it also repeats Items in results which are correlated to Color collection.
For inner element, if there are more than one collection or inner element has another collection on top of it, then it will duplicate elements of inner collection too in database result (same as what happens on outer element level).

In both cases, we need to identify each outer/inner element individually to correctly generate result. For an entity type in EF Core which has PK defined, the identifying column could be the set of primary key. When query has a join between 2 different tables, we can uniquely identify each row in the result by combining primary key of both the tables.
For scenarios involving Distinct & GroupBy we improved our logic of identifying rows.
For GroupBy case, each grouping key identify a unique row in the result.
For Distinct case, we consider all the columns projected on which Distinct is applied since Distinct operator makes sure same collection of values for given projection doesn't appear in different row.
With above set rules, when we generate joins or combine multiple tables in any way, we combine identifier for each of them so we can uniquely identify rows.

This whole thing doesn't work for keyless entity, because they don't have key. Regardless of collection result, in normal case, we cannot say if any row is duplicated in keyless entity. A keyless entity which is mapped to a view in database, can actually have multiple rows which exactly same set of values (essentially duplicate), but that is fine in normal case since each row is separate instance of keyless entity and we materialize as such.
When user starts combining keyless entity using joins, we lose ability to uniquely identify rows in order to do client side processing to combine multiple database rows into single client side result.

For people saying that it worked correctly in 3.1,
In 3.1 release, we actually ignored that one side was keyless entity when generating identifying information for results. If your data is structured in a way that outer/inner element on which collection is correlated doesn't contain duplicates in result, then it will give results which looks correct. When the underlying data changes the query results will change and it will create/populate collections incorrectly. We identified this issue and we fixed by throwing exception in 5.0 rather than ignoring it.

In both the linked issue, user has explicitly configured one of the entity type participating in join to be keyless. Hence it throws exception. If you have a unique way of identify each row for that entity type then configure that as primary key or rewrite query to perform join and collection projection in 2 separate queries so you can do stiching of parts manually based on your domain model what it makes sense.