7

I am wanting to filter a linq query

I have 2 linq statements

The 1st gets all the stores I want and the 2nd is where I filter information based on the results found in the 1st 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 do I add another where clause into my 2nd query to only return results where a.StoreId is contained within the stores result?

Diver Dan
  • 9,953
  • 22
  • 95
  • 166

1 Answers1

5

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 info here: Linq to Entities - SQL "IN" clause

Community
  • 1
  • 1
david.s
  • 11,283
  • 6
  • 50
  • 82
  • Instead of `stores.Select(s => s.StoreID).Contains(a.StoreID)` you can also write the shorter `stores.Any(s => s.StoreID == a.StoreID)`. I believe the translation to SQL will be the same either way. – Timwi Nov 20 '11 at 02:17
  • Thanks David. I was missing the stores.Select(s=>s.StoreId) before trying the contains. – Diver Dan Nov 20 '11 at 02:18