CODEWITHSUNDEEP
c#linqdynamic
Nick
Nick

Reputation: 5696

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

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

Upvotes: 26

Views: 68538

Answers (7)

miha
miha

Reputation: 401

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)
{
    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
    )

Upvotes: -1

Lucas
Lucas

Reputation: 17434

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;

Upvotes: 4

ilitirit
ilitirit

Reputation: 16352

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.

Upvotes: 33

Michael Damatov
Michael Damatov

Reputation: 15653

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.

Upvotes: 0

alexmac
alexmac

Reputation: 4201

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

Upvotes: 4

Carlton Jenke
Carlton Jenke

Reputation: 3025

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;    
}

Upvotes: 1

Dylan Beattie
Dylan Beattie

Reputation: 54160

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.

Upvotes: 4

Related Questions