c# - Replace usage of unwanted foreach with new query in EF

Question

I have a table like below

Employee
-------------------
id    role 
1      a
2      a
3      b
4      c
5      b
----------------------

The filters which I have are ids {1,3,5} and roles {a,b}

So I need to get the rows which have id=1 and role=a , id=2 and role=b , id=3 and role=a, id=3 and role=b etc...

So basically id * roles which I need to get from database.

The current code used in my project is like below

foreach (int role in roles)
                    {

                        foreach (int id in ids)
                        {
//get the data using id and role and append to a list 
}
}

And each iteration a db hit is happening so this makes a huge permanence issue. So I am trying to improve the code which I have and I have replaced the foreach with below code.

var itemsTobeDeleted = context.TableName.Where
                       (
                        item =>
                            obj.ids.Contains(item.Id) &&
                            obj.roles.Contains(item.Role)
                        ).ToList();

My question is is this my new query will give same result as the for each give? If yes then what about the performance ?

Or do we have any better method for doing the same ?

标签： c#entity-frameworklinq

Answer 1

Yes, your second solution is more efficient!

You use a DbContext to access your table. Apparently this table is in a database. Database management systems are extremely optimized in querying data.

However one of the slower parts of a query is the transport of the query and the selected data between your process and the DBMS. Hence it is wise to limit this.

Your foreach method will perform one query per [role, id] combination. This is very inefficient. Your 2nd method will only take one query and return only one resulting sequence. This is way more efficient.

Addition:
Ivan Stoev showed me that the method below does not work: As opposed to an IEnumerable, IQueryable.Contains can only handle primitive types. Hence the Contains below won't work.

Therefore following doesn't work as IQueryable.
This query can be optimized a little: let your process create a sequence of the required [role, id] combinations and query the database for all TableNames that match this combination.

class RoleIdCombination
{
    public char Role {get; set;}
    public int Id {get; set;}
}

// let your process create the requested RoleIdCombinations:
var roleIdCombinations = CreateRoleIdCombinations(roles, ids);

// do only one query:
var result = dbContext.TableNames
    .Select(tableName => new
    {
         // for easier equality check: make a RoleIdCombination:
         RoleIdCombination = new RoleIdCombination
         {
              Role = tableName.Role,
              Id = tableName.Id,
         }
         // remember the original item
         TableName = tableName,
     })
    .Where(item => roleIdCombinations.Contains(item.RoleIdCombination));

So now you know that your database management system will enumerate your table only once.

Function CreateRoleIdCombinations:

IEnumerable<RoleIdCombination> CreateRoleIdCombinations(
    IEnumerable<char> roles,
    IEnumerable<int> ids)
{
     foreach (var role in roles)
     {
         foreach (var id in ids)
         {
              yield return new RoleIdCombination
              {
                  Role = role,
                  Id = id,
              };
         }
     }
}

For this we need (number of roles * number of ids) enumerations. I expect that this will be much smaller than the number of items in your table, after all, you won't be deleting half of your table every time, will you?