How do you add dynamic 'where' clauses to a linq query?

Question

I've got a User table with a bitmask that contains the user's roles. The linq query below returns all the users whose roles include 1, 4 or 16.

var users = from u in dc.Users
            where ((u.UserRolesBitmask & 1) == 1)
               || ((u.UserRolesBitmask & 4) == 4)
               || ((u.UserRolesBitmask & 16) == 16)
            select u;

I'd like to rewrite this into the method below to returns all the users from the given roles so I can reuse it:

private List<User> GetUsersFromRoles(uint[] UserRoles) {}

Any pointers on how to dynamically build my query? Thanks

Stomachic

Answer 1

You can use the PredicateBuilder class.

PredicateBuilder has been released in the LINQKit NuGet package

LINQKit is a free set of extensions for LINQ to SQL and Entity Framework power users.

Kosaka

Apparantly the PredicateBuilder class is available in two versions: the source code example on the website, which is © Albahari & O'Reilly, all rights reserved; and as part of LinqKit, which is "under a permissive free license, which means you can modify it as you please, and incorporate it into your own commercial or non-commercial software." – Gatias

LinqKit is available via NuGet. – Tergal

Answer 2

Assuming your UserRoles values are themselves bitmasks, would something like this work?

private List<User> GetUsersFromRoles(uint[] UserRoles) {
    uint roleMask = 0;
    for (var i = 0; i < UserRoles.Length;i++) roleMask= roleMask| UserRoles[i];
    // roleMasknow contains the OR'ed bitfields of the roles we're looking for

    return (from u in dc.Users where (u.UserRolesBitmask & roleMask) > 0) select u);
}

There's probably a nice LINQ syntax that'll work in place of the loops, but the concept should be the same.

Fanelli

This should work. Instead of dynamic where clause you reduced it to a single one. And that nice LINQ syntax you mentioned could be: uint roleMask = UserRoles.Aggregate(0, (combined, role) => combined | role); – Megalith

Answer 3

There are a couple of ways you can do this:

LINQ Dynamic query libraries: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Expression Trees & Lamda expressions: http://msdn.microsoft.com/en-us/library/bb882637.aspx

Scabrous

Answer 4

Here's one way of adding a variable number of where clauses to your LINQ query. Note that I haven't touched your bitmask logic, I just focused on the multiple wheres.

// C#
private List<User> GetUsersFromRoles(uint[] UserRoles)
{
   var users = dc.Users;

   foreach (uint role in UserRoles)
   {
      users = users.Where(u => (u.UserRolesBitmask & role) == role);
   }

   return users.ToList();
}

---

EDIT: Actually, this will AND the where clauses and you wanted to OR them. The following approach (a inner join) works in LINQ to Objects but can not be translated to SQL with LINQ to SQL:

var result = from user in Users
             from role in UserRoles
             where (user.UserRolesBitmask & role) == role
             select user;

Megalith

Answer 5

How's this? It is not dynamic linq, but accomplishes the goal.

private List<User> GetUsersFromRoles(uint[] userRoles) 
{
    List<User> users = new List<User>();

    foreach(uint userRole in UserRoles)
    {
        List<User> usersInRole = GetUsersFromRole(userRole);
        foreach(User user in usersInRole )
        {
            users.Add(user);
        }
    }
    return users;
}    

private List<User> GetUsersFromRole(uint userRole) 
{
    var users = from u in dc.Users
            where ((u.UserRolesBitmask & UserRole) == UserRole)
            select u;

    return users;    
}

Schramke

Also, you need to ToList in GetUsersFromRole – Rhinology

Answer 6

private List<User> GetUsersFromRoles(uint UserRoles) {
  return from u in dc.Users            
         where (u.UserRolesBitmask & UserRoles) != 0
         select u;
}

UserRoles parameter should be provided, however, as a bit mask, instead of array.

Copeland

This will return the users who match ALL the given roles. His method return users who match ANY of the given roles. – Megalith

Answer 7

for those of you who are looking for Expression class usage example (in case you want to avoid additional libraries)

public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string,List<string>> filterDictionary)
{
    var q = db.ProductDetail.Where(BuildFilter(productGroupName,productTypeName)).Orderby(c=>c.ProductTypeName);
    return q;
}

private static Expression<Func<ProductDetail, bool>> BuildFilter(string productGroupName, string productTypeName)
{
    var p = Expression.Parameter(typeof(ProductDetail));
    return Expression.Lambda<Func<ProductDetail, bool>>(
        Expression.AndAlso(
            Expression.Equal(
                Expression.Property(p, "productGroupName"),
                Expression.Constant(productGroupName)
                ),
            Expression.OrElse(
                Expression.Equal(
                    Expression.Property(p, "productTypeName"),
                    Expression.Constant(productTypeName.ToLower())
                    ),
                Expression.Equal(
                    Expression.Property(p, "productTypeName"),
                    Expression.Constant(productTypeName)
                    )
                )
            ),
            p
        );
}

this would be equivalent to

c.ProductGroupName == productGroupName 
 && (c.ProductTypeName == productTypeName.toLower()
     || c.ProductTypeName == productTypeName
    )

Concentrated