none
Dynamic Where clause with multiple tables RRS feed

  • Question

  • I have 3 tables:

    User(id, name) AttributeSet(id, userid, date_created) and Attribute(id, attribute_set_id, name, value)

    I need to select users, who had one or multiple attributes on certain date. So depending on how many attributes client passes I need to create a dynamic select statement.

    Thank you,

    Paul.

    Monday, April 26, 2010 11:51 PM

Answers

  • Hi Paul,

     

    The dynamic LINQ query is fine based on my test.   One thing to point out, it is recommended to use parameter  to pass the DateTime type into the query like:

    ============================================================================

    var date = new DateTime(2000, 1, 1);

    var query = context.User.AsQueryable().Where("AttributeSet.Any(date_created = @0 && Attribute.Any((name=\"title\" && value=\"director\")))", date);

    ============================================================================

    Besides, in this method, the dynamic query string should be generated based on the user input.  It seems to be a string operation.  

     

    Another workaround is to use the PredicateBuilder as I mentioned in my last post.   After we refer to the LINQKit assembly, the query can be similar like:

    ============================================================================

    var predicate = PredicateBuilder.False<Attribute>();

    var date = new DateTime(2000, 1, 1);

     

    List<UserInputAttribute> list = new List<UserInputAttribute>()

    {

        new UserInputAttribute { Name = "title", Value = "director" },

        new UserInputAttribute { Name = "title", Value = "vp" },

        new UserInputAttribute { Name = "title", Value = "project manager" }

    };

     

    foreach (var l in list)

    {

        string name = l.Name;

        string value = l.Value;

        predicate = predicate.Or(a => a.name == name && a.value == value);

    }

     

    var query = context.User.AsExpandable().Where(u => u.AttributeSet.AsQueryable().Any(at => at.date_created == date && at.Attribute.AsQueryable().Any(predicate)));

     

     

        class UserInputAttribute

        {

            public string Name { get; set; }

            public string Value { get; set; }

        }

    ============================================================================

    In this method, all the dynamic parts are based on lambda expressions.  

     

     

    Please note:  in both the methods, the return data contains the single User object instead of duplicate ones.  But your SQL statement should return the same User name multiple times if the user contains multiple Attributes which are qualified for the filter.   So the LINQ query returns the results of the SQL statement plus a DISTINCT operation. 

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 28, 2010 7:24 AM
    Moderator
  • Hi Lingzhi,

    Thank you for a great explanation.

    Paul.

    • Marked as answer by pvsherman Wednesday, April 28, 2010 7:15 PM
    Wednesday, April 28, 2010 7:15 PM

All replies

  • Hello Paul,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    Based on your scenario, I would recommend you to use PredicateBuilder or Dynamic LINQ Library.   To use the PredicateBuilder in EF, please install LINQKit as the prerequisite. 

     

    Besides, please provide us with more detailed information about your request, like your VS, EF versions and more detailed sample of the EF query you want.   I will do my best to provide some sample codes for your references. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 27, 2010 7:42 AM
    Moderator
  • Hello Lingzhi,
    Thank you for the answer.

    I use VS 2008 and .NET 3.5. I need an equivalent of the following SQL statement:

    SELECT u.name
    FROM User u, AttributeSet as, Attribute at
    WHERE u.id = as.userid
    AND as.id = at.attribute_set_id
    AND as.date_created = @date_in
    AND
    (
      (at.name = 'title' AND at.value = 'director')
       OR
      (at.name = 'title' AND at.value = 'vp')
       OR
      (at.name = 'title' AND at.value = 'project manager')
       OR ...
       OR ...
    )

    List of OR statements depends on the number of attribute objects passed to the function by the client.

    Thank you,
    Paul.

     

    Tuesday, April 27, 2010 3:10 PM
  • I am not an SQL expert, but I believe the above query can be written (using the IN clause) like this:

    SELECT u.name
    FROM User u, AttributeSet as, Attribute at
    WHERE u.id = as.userid
    AND as.id = at.attribute_set_id
    AND as.date_created = @date_in
    AND
    (
      at.name = 'title' AND at.value IN ('director', 'vp', 'project manager' .......etc....)

    )

    In this case, Lingzhi's suggestion will be helpful.

    Tuesday, April 27, 2010 8:35 PM
  • Thank you Srinivas.

    Good suggestion.

    OK. Solution using  Dynamic LINQ Library :

    IQueryable x = _myEntities.User.AsQueryable();

    x = x.Where("AttributeSet.Any(date_created = \"1/1/2000\" && Attribute.Any((name=\"title\" && value=\"director\") || (name=\"shoe_size\" && value=\"14\") ))");

     

    Is this correct? Is there a better way to do that?

    Thanks,

    Paul.

    Tuesday, April 27, 2010 10:50 PM
  • Hi Paul,

     

    The dynamic LINQ query is fine based on my test.   One thing to point out, it is recommended to use parameter  to pass the DateTime type into the query like:

    ============================================================================

    var date = new DateTime(2000, 1, 1);

    var query = context.User.AsQueryable().Where("AttributeSet.Any(date_created = @0 && Attribute.Any((name=\"title\" && value=\"director\")))", date);

    ============================================================================

    Besides, in this method, the dynamic query string should be generated based on the user input.  It seems to be a string operation.  

     

    Another workaround is to use the PredicateBuilder as I mentioned in my last post.   After we refer to the LINQKit assembly, the query can be similar like:

    ============================================================================

    var predicate = PredicateBuilder.False<Attribute>();

    var date = new DateTime(2000, 1, 1);

     

    List<UserInputAttribute> list = new List<UserInputAttribute>()

    {

        new UserInputAttribute { Name = "title", Value = "director" },

        new UserInputAttribute { Name = "title", Value = "vp" },

        new UserInputAttribute { Name = "title", Value = "project manager" }

    };

     

    foreach (var l in list)

    {

        string name = l.Name;

        string value = l.Value;

        predicate = predicate.Or(a => a.name == name && a.value == value);

    }

     

    var query = context.User.AsExpandable().Where(u => u.AttributeSet.AsQueryable().Any(at => at.date_created == date && at.Attribute.AsQueryable().Any(predicate)));

     

     

        class UserInputAttribute

        {

            public string Name { get; set; }

            public string Value { get; set; }

        }

    ============================================================================

    In this method, all the dynamic parts are based on lambda expressions.  

     

     

    Please note:  in both the methods, the return data contains the single User object instead of duplicate ones.  But your SQL statement should return the same User name multiple times if the user contains multiple Attributes which are qualified for the filter.   So the LINQ query returns the results of the SQL statement plus a DISTINCT operation. 

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 28, 2010 7:24 AM
    Moderator
  • Hi Lingzhi,

    Thank you for a great explanation.

    Paul.

    • Marked as answer by pvsherman Wednesday, April 28, 2010 7:15 PM
    Wednesday, April 28, 2010 7:15 PM
  • It's my pleasure!  :-)

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 29, 2010 1:07 AM
    Moderator