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

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.

CodePudding user response：

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 };

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);