Where IN for linq

Question

I have seen questions with this subject but mine is different.

I have stored procedure (EmpsByManager) imported in EF. it returns data of following fields:

EmpId, EmpName, PrimaryMobile

I have a claimTable in the db having the following fields

EmpId, ClaimId, ClaimDetails...

I want to return all claims from the claimTable IN the Employees of EmpsByManager(ManagerId)

I could manage to do this with a loop:

public dynamic getActiveClaims(int ManagerId)
        {
            db.Configuration.ProxyCreationEnabled = false;

            var myEmps = db.getEmpDetofManager(ManagerId).ToList();

            List<List<claimJSON>> claimsList = new List<List<claimJSON>>();

            foreach(var Emp in myEmps)
            {
                claimsList.Add(db.claimJSONs.Where(e => e.EmpId == Emp.EmpId && e.claimstatus != 0 && e.claimstatus != 8).ToList());
            }

            return claimsList;
        }

This is giving correct results but, I myself am not convinced with the complexity and number of database hits to get the required result.

Answer 1

Currently you are hitting the db everytime inside your loop. You can replace the Where clause inside your foreach loop with the use of the Contains() method.

var myEmps = db.getEmpDetofManager(ManagerId).ToList();

// Get all EmpIds from the result and store to a List of Int

List<int> empIds = myEmps.Select(f=>f.EmpId).ToList();

// Use the List of EmpId's in your LINQ query.

var claimsList  = db.claimJSONs.Where(e => empIds.Contains(e.EmpId) 
                             && e.claimstatus != 0 && e.claimstatus != 8).ToList();

Also, not that the result in claimsList variable will be a List<claimJSON> , not List<List<claimJSON>>>

This will result in 2 hits to the db. One for the stored proc and another for getting data from the claimJSON table for the list of EmpIds we got from the stored proc result.