Using ".Any()" in lambda query throws error in .net core EF project

Question

I have this below query in which throws the error below mentioned,It used to work in the other project but no able to run in the .net core project.

var lstAppForm = await _appDBContext.ApplicationForms.Where(qr => appFormViewModel.Any(any => any.kycId == qr.id )).ToListAsync();

The LINQ expression 'DbSet .Where(a => __appFormViewModel_0 .Any(any => any.kycId == a.id))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Answer 1

The problem is that the expression is not translatable to sql because the appFormViewModel is not an entity in your database. it is your view model for UI which has nothing to do directly in the database so it shouldn't be part of your EF query any way.

What we can do is project the needed data outside the EF query using Select and then use it in the Linq query for EF:

var ids = appFormViewModel.Select(x => x.kycId).ToList();

var lstAppForm = await _appDBContext.ApplicationForms
                      .Where(qr => ids.Contains(qr.id))
                      .ToListAsync();

or:

var ids = appFormViewModel.Select(x => x.kycId).ToList();

var lstAppForm = await _appDBContext.ApplicationForms
                      .Where(qr => ids.Any(a => a == qr.id))
                      .ToListAsync();