Filter linq query based on the results of other query results

Question

I want to filter the linq request

I have 2 linq statements

The first one gets all the stores that I want, and the second one where I filter information based on the results found in the first query.

var stores = ctx.Stores.Where(ps => ps.ParentStoreID == parent.ParentStoreID && ps.StoreID!=storeID); var query = (from a in ctx.TransactionTable from b in ctx.MappingTable.Where(x => x.TransactionId== a.TransactionId).DefaultIfEmpty() where a.StoreID!=storeID select new { Transactions = a, Mapping = b }).ToList(); 

How to add another where clause to my second query to return results that contain a.StoreId as a result of storage?

Answer 1

Like this:

 var stores = ctx.Stores.Where(ps => ps.ParentStoreID == parent.ParentStoreID && ps.StoreID!=storeID); var query = (from a in ctx.TransactionTable from b in ctx.MappingTable.Where(x => x.TransactionId==a.TransactionId).DefaultIfEmpty() where a.StoreID!=storeID && stores.Select(s => s.StoreID).Contains(a.StoreID) select new { Transactions = a, Mapping = b }).ToList(); 

You can find more information here: Linq to Entities - SQL "IN" clause