LINQ expression could not be translated. Either rewrite the query in a form that can be translated

Question

I'm trying to write a LINQ query to get a result depending on some joins but I'm not sure how to write it properly

Here are my classes

public class Result
    {
        public Response Response { get; set; }
        public IEnumerable<Item> Items { get; set; }

    } 

public class Response
    {
        public TableA TableA { get; set; }
        //other properties
    }

public class TableA
    {
        public IEnumerable<ItemA> ListA { get; set; }
        //other properties
    }

LINQ query I'm trying to write

var query = from tableA in _context.Set<TableA>().Where(t => t.Id == id).Include(t => t.ListA)
            //some joins with other tables
            // ....
            from itemA in tableA.ListA
            join itemB in _context.Set<TableB>() on itemA.Id equals itemB.Id into items
                                                                           
            select new Result
            {
               Response = new Response
               {
                    TableA = tableA,
                    //other properties from different joins                                                              
               },
               Items = items.AsEnumerable()                                                               

            };

the previous query gives me this error

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

when I tried to divid the previous query into two queries as below, it works as expected

var query1 = from tableA in _context.Set<TableA>().Where(t => t.Id == id).Include(t => t.ListA)
             //some joins with other tables
             // ....                                                               
             select new Result
             {
               Response = new Response
               {
                    TableA = tableA,
                    //other properties from different joins                                                              
               },
             };   
                                                                           
                                                                           
var query2 = from itemA in query1.FirstOrDefault().Response.TableA.ListA
             join itemB in _context.Set<TableB>() on itemA.Id equals itemB.Id into items
             from item in items.DefaultIfEmpty()
             select item;

var result = new Result()
                {
                    Response = query1.FirstOrDefault().Response,
                    Items = query2
                };

I wonder what is the problem with the first query and how to write it without dividing it into two queries if possible ?

Thanks in advance

Have you tried to write `Items = items.AsEnumerable()` as `Items = items`, simply? — Fildor, Nov 16 '22 at 11:07
Ok. I was suspecting the explicit call to `AsEnumerable()` to be the culprit but this goes deeper. — Fildor, Nov 16 '22 at 11:22
Does this answer your question? [Group join in EF Core 3.1](https://stackoverflow.com/questions/60588364/group-join-in-ef-core-3-1) — Svyatoslav Danyliv, Nov 16 '22 at 11:35
Or much [better](https://stackoverflow.com/a/66845654/10646316) — Svyatoslav Danyliv, Nov 16 '22 at 11:42
IOW, don't use `GroupJoin` for eager loading, only for LEFT JOIN. It is known EF Core limitation. — Svyatoslav Danyliv, Nov 16 '22 at 11:44
@SvyatoslavDanyliv I tried `from itemA in tableA.ListA select new Result {Response = new Response {TableA = tableA }, Items = _context.Set().Where(itemB => itemA.Id == itemB.Id).ToList()` but this gives me the same error I mentioned in @vernou's answer — Merna Mustafa, Nov 16 '22 at 19:43

score 2 · Accepted Answer · answered Nov 16 '22 at 13:28

I think the error come from this part from itemA in tableA.ListA. I don't see how to translate this into SQL.

Why I understand, you want retrieve tableA rows with their itemA and itemB. A solution is to retrieve itemB from sub query like :

var query =
    from tableA in _context.Set<TableA>().Include(t => t.ListA)
    where tableA.Id == id
    select new Result {
        Response = new Response {
            TableA = tableA,
        },
        Items = (from itemA in tableA.ListA
                    join itemB in _context.Set<ItemB>()
                    on itemA.Id equals itemB.Id
                select itemB).AsEnumerable()
    };

Generated SQL :

SELECT [t].[Id], [t].[Name], [i].[Id], [i].[Name], [i].[TableAId], [t0].[Id], [t0].[Name], [t0].[Id0]
FROM [TableAs] AS [t]
LEFT JOIN [ItemAs] AS [i] ON [t].[Id] = [i].[TableAId]
LEFT JOIN (
    SELECT [i1].[Id], [i1].[Name], [i0].[Id] AS [Id0], [i0].[TableAId]
    FROM [ItemAs] AS [i0]
    INNER JOIN [ItemBs] AS [i1] ON [i0].[Id] = [i1].[Id]
) AS [t0] ON [t].[Id] = [t0].[TableAId]
WHERE [t].[Id] = @__id_0
ORDER BY [t].[Id], [i].[Id], [t0].[Id0]

This gives me this error `InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side.This can happen when trying to correlate on keyless entity type.This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'.These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions` — Merna Mustafa, Nov 16 '22 at 19:25
In my project, `ItemB` is a view that was created from other tables, and yes when I checked its configuration I found out it has no key, I've fixed it and your query is working perfectly now! Thanks @vernou you nailed it :) — Merna Mustafa, Nov 17 '22 at 16:37

LINQ expression could not be translated. Either rewrite the query in a form that can be translated

1 Answers1