Filter a DbSet by the content of an IList using LINQ

user_6744483 0
Viewed 204

I have 2 ILists Suppliers and VwSrmAhmSuppliers. They are both queried from a database. I first fill Suppliers. Then, when I'm querying VwSrmAhmSuppliers, I want to filter the results based on what I've already pulled in Suppliers.

public IList<Supplier> Suppliers { get;set; }
public IList<Models.ExternalData.VwSrmAhmSupplier> VwSrmAhmSuppliers { get; set; }

public async Task OnGetAsync(Boolean? All)
{
   //don't show all records unless explicity asked to!
   if (All == true)
   {
      Suppliers = await _context.Supplier
         .Include(s => s.Status)
         .Include(c => c.Category)
         .Include(c => c.Comments)
         .OrderByDescending(c => c.CreateDate)
         .ToListAsync();

      //these do not work
      //VwSrmAhmSuppliers = await _externalcontext.VwSrmAhmSuppliers.Where(d => Suppliers.Any(s=>s.SupplierNo == d.AhmSupplierNo)).ToListAsync();
      //VwSrmAhmSuppliers = await _externalcontext.VwSrmAhmSuppliers.Where(v => Suppliers.Any(s=> s.SupplierNo.Equals(v.AhmSupplierNo))).ToListAsync();

      //This does work, it gets all suppliers but it's too many
      //VwSrmAhmSuppliers = await _externalcontext.VwSrmAhmSuppliers.ToListAsync();


      VwSrmAhmSuppliers = await _externalcontext.VwSrmAhmSuppliers
         .Where(v => Suppliers
            .Any(s => s.SupplierNo == v.AhmSupplierNo))
         .ToListAsync();
   }
}

The error generated is:

InvalidOperationException: The LINQ expression 'DbSet .Where(v => __Suppliers_0 .Any(s => s.SupplierNo == v.AhmSupplierNo))' 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.

And it is not clear to me.

razor-pages asp.net-core linq c#
1 Answers

You need to project out an in-memory collection of simple reference types first (int, string etc.), rather than a list of type Supplier, then use that for your Any or Contains condition, e.g:

Suppliers = await _context.Supplier
         .Include(s => s.Status)
         .Include(c => c.Category)
         .Include(c => c.Comments)
         .OrderByDescending(c => c.CreateDate)
         .ToListAsync();

//Project out the required references
var supplierNos = Suppliers.Select(s => s.SupplierNo).ToList();

//Use the simple reference type collection in your query
VwSrmAhmSuppliers = await _externalcontext.VwSrmAhmSuppliers
    .Where(d => supplierNos.Any(s=> s == d.AhmSupplierNo)).ToListAsync();
user_4902820
Related