System.InvalidOperationException: 'The LINQ expression => EntityShaperExpression:

I am getting this error on this line of code

        var attachment = await _context.Attachments
                            .Where(a => list.Any(i => i.Id == a.Id))
                            .ToListAsync();

Although the error is fixed when I change my code to

        var attachment = _context.Attachments
                            .ToList()
                            .Where(a => list.Any(i => i.Id == a.Id))
                            .ToList();

Does anyone have any idea on what is going on exactly here?

Solution

The error message you received suggests that Entity Framework couldn't translate this LINQ query into a valid SQL query, and that's why you got the exception. The second approach works because it retrieves all the data from the Attachments table into memory first (using the ToList() method), and then applies the filtering using LINQ to Objects in memory. This approach is generally less efficient if you have a large amount of data in the Attachments table because you are loading all records into memory.

You can do:

 var listOfIds = list.Select(item => item.Id);
 var attachmentS = await _context.Attachments
                            .Where(a => listOfIds.Contains(a.Id))
                            .ToListAsync();