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

CodePudding user response：

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]