The Query (LINQ) expression could not be Translated Entity Framework Core

Question

I am using EntityFrameworkCore 3.1.11 and having below query

 var list = _context.Table1
                  .Include(i => i.ListofGroupIds)
                  .Where(i => 
                          i.ListofGroupIds.Select(x => x.GroupId).Any(m =>
                          SelectedIds.Where(z => z.CreatedDate <= i.SentDate).Select(y => y.Id).Contains(m)
                       ))
                   );

Here i need to check whether any of the item(Id) present in SelectedIds(list having properties like {Id,CreatedDate and other fields}) is a part of ListOfGroupIds, According to this i need to fetch the rows. But i am getting run time exception as

The Query (LINQ) expression could not be Translated Entity Framework Core,Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I checked different post related to this and even tried enter link description here

I got only one solution which is working is by adding AsEnumerable to the query.But i dont want it to be AsEnumerable because i am dealing with huge data, and i can't split the contains query seprate because i need to check one condition(i.SentDate) inside that ANY.

So if there is any way to do this in a single query without making AsEnumerable.

Answer 1

Assuming this is the structure you have (I'm ignoring all the foreign keys you may have on purpose, this is just an example!)

public class Table1
{
    public int Id { get; set; }

    public virtual ListofGroupIds ListofGroupIds { get; set; }
}

public class ListofGroupIds
{
    public int GroupId { get; set; }
    public DateTime SentDate { get; set; }
}

public class SelectedIds
{
    public int Id { get; set; }
    public DateTime CreatedDate { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Table1> Table1 { get; set; }
    public DbSet<ListofGroupIds> ListofGroupIds { get; set; }
    public DbSet<SelectedIds> SelectedIds { get; set; }
}

You can rewrite your query as

var query = from tab1 in _context.Table1
                    join logi in _context.ListofGroupIds on tab1.Id equals logi.GroupId
                    join sids in _context.SelectedIds on logi.GroupId equals sids.Id
                    where sids.CreatedDate <= logi.SentDate
                    select new { tab1.Id, logi.GroupId, sids.CreatedDate }; //You can select any other columns within the tables joined

Or, if possible, simply join the two tables needed

var query2 = from logi in _context.ListofGroupIds
                     join sids in _context.SelectedIds on logi.GroupId equals sids.Id
                     where sids.CreatedDate <= logi.SentDate
                     select new { logi.GroupId, logi.SentDate, sids.Id, sids.CreatedDate };

Or

var query3 = _context
            .ListofGroupIds.Join(_context.SelectedIds, logi => logi.GroupId, sids => sids.Id, (logi, sids) => new { logi.GroupId, logi.SentDate, sids.Id, sids.CreatedDate })
            .Where(result => result.CreatedDate <= result.SentDate);