Implementation of SQL IN clause in LINQ

To implement SQL IN clause in LINQ there are 4 steps

1.       GetTable from  which you need to filter rows.

2.       GetTable that contains the values(only one clumn) you need to match in IN clause.

3.       Type cast data of step # 2 DataTable column data identical to step # 1 DataTable filtering column/property.

4.       Use step# 2 DataTable’s Contains() method of step # 1 DataTable’s where caluse

public List<CustomerCoupon> getCustomerNonSpecials(int StoreID, int customerID)

        {

           

           

//Step # 1

List<CustomerCoupon> coupons = new List<CustomerCoupon>();

coupons = customerCouponsTable.Where(coupon => coupon.Customer.CustomerID == customerID).ToList();

           

//Step # 2

Table<CategoryItem> specialsTable = customersTable.Context.GetTable<CategoryItem>();

            Table<CustomerCoupon> customerCouponsTable = customersTable.Context.GetTable<CustomerCoupon>();

List<CategoryItem> specials = new List<CategoryItem>();

specials = specialsTable.Where(cItem => cItem.StoreCategory.CategoryName == cItem.Item.Store.StoreCategorys[0].CategoryName && cItem.Item.Store.StoreID == StoreID).ToList();

var query = from c in coupons

join s in specials on c.ItemID equals s.ItemID

      select new { c.Item.ItemID };

//Step # 3           

List<int> SpecialsCouponsIDs = new List<int>();

SpecialsCouponsIDs = query.ToList().ConvertAll<int>(i=> Convert.ToInt16(i.ItemID));

//Step # 4

List<CustomerCoupon> nonSpecialsCoupons =new List<CustomerCoupon>();

nonSpecialsCoupons = (from c in coupons where SpecialsCouponsIDs.Contains(c.ItemID) select c).ToList();

return nonSpecialsCoupons;

}