none
How do I dynamically create a LINQ expression using System.Linq.Expressions for this following example? RRS feed

  • Question

  • Hey everyone, I am trying to dynamically build a LINQ expression to be used as a filter.  For example, say I want to filter users by 10 different properties of the User.  However, sometimes I want to filter by 2 properties, and sometimes by all 10.  What I would like to do, is dynamically build a LINQ expression to accomodate this.  Does anyone know how to illustrate how to dynamically build this LINQ expression?

     

    int[] validStatuses = new[] { 10, 100, 1000 };

    int[] validUserTypes = new[] { 1, 4, 5 };

    Expression exp = ????? (Here, I want to dynamically create this: u => u.Status == validStatuses && u.UserType == validUserTypes)

    int count = db.Users.Where(exp).Count();

    Friday, November 11, 2011 3:49 PM

Answers

  • I have heard that selection type queries are better done in Entity SQL because the query is built as a string.  Another possibility is setting up a stored procedure to handle this.  That said, I was able to handle this type of query in a search that I had by using Contains for each property as follows (used a join only because there was no foreign key defined in the table):

    var query = (from p in Context.Users

                 join ac in Context.UserAccounts

                 on p.UserID equals ac.UserID

                 where p.LastName.Contains(LastName) &&

                       p.FirstName.Contains(FirstName) &&

                       ac.Location.Contains(Location)

                 orderby p.LastName ascending, p.FirstName ascending

                 select new { selectedUser = p, userAccount = ac });

    If the user does not select a FirstName or a LastName (or either), the Contains is looking for a "", which is valid for any field.  Unfortunately, since UserAccount is a child of User, if the account location is not chosen in the search, I had to run a different query returning multiple child records.  From your example, it looks as if you are searching something more specific, so Entity SQL might be the best option. 


    Christine A. Piffat
    Friday, November 11, 2011 7:34 PM

All replies

  • I have heard that selection type queries are better done in Entity SQL because the query is built as a string.  Another possibility is setting up a stored procedure to handle this.  That said, I was able to handle this type of query in a search that I had by using Contains for each property as follows (used a join only because there was no foreign key defined in the table):

    var query = (from p in Context.Users

                 join ac in Context.UserAccounts

                 on p.UserID equals ac.UserID

                 where p.LastName.Contains(LastName) &&

                       p.FirstName.Contains(FirstName) &&

                       ac.Location.Contains(Location)

                 orderby p.LastName ascending, p.FirstName ascending

                 select new { selectedUser = p, userAccount = ac });

    If the user does not select a FirstName or a LastName (or either), the Contains is looking for a "", which is valid for any field.  Unfortunately, since UserAccount is a child of User, if the account location is not chosen in the search, I had to run a different query returning multiple child records.  From your example, it looks as if you are searching something more specific, so Entity SQL might be the best option. 


    Christine A. Piffat
    Friday, November 11, 2011 7:34 PM
  • Hi,

    I think can follow here to achieve your goal: http://blogs.msdn.com/b/mattwar/archive/2007/07/31/linq-building-an-iqueryable-provider-part-ii.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, November 17, 2011 3:18 AM
    Moderator