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.

SelectedIds is just a list having properties like {Id,CreatedDate and other fields} — user3501613
– user3501613, Commented Sep 21, 2021 at 14:17
Please give more details on the question. Is "SelectedIds" part of "Table1"? — Bazil
– Bazil, Commented Sep 21, 2021 at 14:30
Then, it seems to me a little bit out of context, and that may be the reason why it cannot be translated. If you want to retrieve the rows of these two lists, why don't you use a join? — Bazil
– Bazil, Commented Sep 21, 2021 at 15:00

Bazil · Accepted Answer · 2021-09-21 16:38:06Z

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

Thanks for the answer,, even this is not the exact solution but i can modify this for my purpose

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related