none
How to do this as linq 2 sql? RRS feed

  • Question

  • Hi folks,

        I'm not sure how to handle some dynamic linq statement in some more complex linq statement. I have some code that generates some 'where clause' logic.

     

    Code Snippet

    eg.

    // GenerateDynamicWhereAddressWhereClause(string street, string state, string country)

    // Expected result: Address.State == \"CA\"" and Address.Country == \"USA\""

    string whereClause = GenerateDynamicWhereAddressWhereClause(string.Empty, "CA", "USA");

     

     

     

    Normally, i do the following to 'filter' my addresses...

     

    from a in db.Addresses.Where(whereClause) because i'm using the Microsoft class that ScottGu suggest people to use for dynamic queries. Works great.

     

    Code Snippet

    eg. var x = (from a in db.Addresses.Where(whereClause)

                     select a).Take(5).ToList(); // up to 5 results.

     

     

     

    because the custom Where extension method works on an IQueryable only, i'm not sure how to apply it to the following ...

     

    Code Snippet

    var x = (from u in db.UserInfos

    select new Foo{

    UserInfo = u,

    LocalScore = u.Audits.Where(whereClause).Sum(aa => (int)aa.Score) +

    u.Audits.Where(whereClause).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

    }).Distinct().OrderByDescending(r => r.LocalScore).Take(5).ToList();

     

     

     

    Notice how i'm trying to filter out the Audits by the dynamic address? That code doesn't compile, because the Audit is an EntitySet, and the Where extension method i use is for an IQueryable. Even if i do u.Audits.AsQueryable().Where(whereClause)..... i get the error 'The query operator 'AsQueryable' is not supported.'.

     

    Anyone have any suggestion about how to dynamically filter the result set i'm after?

    Cheers.

    Monday, July 28, 2008 6:48 AM

Answers

  • You can write this function in a type-safe fashion with PredicateBuilder:

    Expression<Func<Address, bool>> GetAddressPredicate (string street, string state, string country)
    {
        
    var predicate = PredicateBuilder.True<Address>();
        
    if (!string.IsNullOrEmpty (street)) predicate = predicate.And (a => a.AddressLine1 == street);
        
    if (!string.IsNullOrEmpty (state)) predicate = predicate.And (a => a.StateProvince == state);
        
    if (!string.IsNullOrEmpty (country)) predicate = predicate.And (a => a.CountryRegion == country);
        
    return predicate;
    }


    Here's how you'd then use it:

    Addresses.Where (GetAddressPredicate ("Station E", "Ontario", null)).Dump();
    Addresses.Where (GetAddressPredicate (
    null, "Ontario", "Canada")).Dump();


    Joe


    Monday, August 4, 2008 2:30 AM
    Answerer
  • Add a reference to the LINQKit assembly, and then modify your query as follows (note the changes in bold):

    var x = (from u in db.UserInfos.AsExpandable()

               select new Foo

               {

                   UserInfo = u,

                   LocalScore = u.Audits.Where(whereClause.Compile()).Sum(aa => (int)aa.Score) +

                       u.Audits.Where(whereClause.Compile()).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

               }).Distinct().OrderByDescending(r => r.LocalScore).Take(5).ToList();



    It will then compile and run correctly!

    Joe
    Monday, August 11, 2008 5:52 AM
    Answerer

All replies

  • I've also tried to do the following .. it compiles but at runtime errors with saying that the linq query is bad .. as opposed to a sql error.

     

    Code Snippet

    LocalScore = (from a in db.tblAudits.Where(whereClause)

    select (int)a.Score).Sum()

     

     

    any help?
    Tuesday, July 29, 2008 2:53 AM
  • *bump*

    Monday, August 4, 2008 12:26 AM
  • You can write this function in a type-safe fashion with PredicateBuilder:

    Expression<Func<Address, bool>> GetAddressPredicate (string street, string state, string country)
    {
        
    var predicate = PredicateBuilder.True<Address>();
        
    if (!string.IsNullOrEmpty (street)) predicate = predicate.And (a => a.AddressLine1 == street);
        
    if (!string.IsNullOrEmpty (state)) predicate = predicate.And (a => a.StateProvince == state);
        
    if (!string.IsNullOrEmpty (country)) predicate = predicate.And (a => a.CountryRegion == country);
        
    return predicate;
    }


    Here's how you'd then use it:

    Addresses.Where (GetAddressPredicate ("Station E", "Ontario", null)).Dump();
    Addresses.Where (GetAddressPredicate (
    null, "Ontario", "Canada")).Dump();


    Joe


    Monday, August 4, 2008 2:30 AM
    Answerer
  • Hi Joe (and yep! i love using LINQPad btw),

     

    are you suggesting i do the following...

     

    Code Snippet

    var whereClause = GetAddressPredicate(null, "Ontario", "Canada");

    LocalScore = u.Audits.Where(whereClause).Sum(aa => (int)aa.Score) +

    u.Audits.Where(whereClause).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

     

     

     

     

    ??

    Monday, August 4, 2008 7:22 AM
  • Yes - that should work (unless I've misssed something).

    Cheers
    Joe
    Monday, August 4, 2008 2:44 PM
    Answerer
  • @ Joe: na mate... not working. Compiler error. this is the code i've got.

     

     

    Code Snippet

    using System.Linq.Dynamic;

    using System.Linq.Expressions;

    using LinqKit;

     

    ** Take note of the Dynamic linq namespace .. which implements its own Where extension methods.

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

    Download: http://msdn.microsoft.com/en-us/vcsharp/bb894665.aspx

       or

     http://tomvangaever.be/Data/Dynamic.cs

     

     

    Audit.cs

    Code Snippet

    public static Expression<Func<tblAudit, bool>> GetAddressPredicate(string primaryCity,

        string subdivision,

        string countryRegion)

    { ... }

     

     

    UserInfo.cs

    Code Snippet

    var whereClause = Audits.GetAddressPredicate(null, ""Ontario", "Canada");

    var x = (from u in db.UserInfos

               select new Foo

               {

                   UserInfo = u,

                   LocalScore = u.Audits.Where(whereClause).Sum(aa => (int)aa.Score) +

                       u.Audits.Where(whereClause).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

               }).Distinct().OrderByDescending(r => r.LocalScore).Take(5).ToList();

     

    ** Green highlights the errors.

     

    Error:

     

    Error 3 'System.Data.Linq.EntitySet' does not contain a definition for 'Where' and the best extension method overload 'System.Linq.Dynamic.DynamicQueryable.Where(System.Linq.IQueryable, string, params object[])' has some invalid arguments R:\Visual Studio Projects\.......blah blah blah ........\Audit.cs 52 61 MyApplication


     Error 4 Instance argument: cannot convert from 'System.Data.Linq.EntitySet' to 'System.Linq.IQueryable' R:\Visual Studio Projects\.......blah blah blah ........\Audit.cs 52 61 MyApplication

     

    Error 5 Argument '2': cannot convert from 'System.Linq.Expressions.Expression<SYSTEM.FUNC>' to 'string' R:\Visual Studio Projects\.......blah blah blah ........\UserInfo.cs 52 79 MyApplication

     

    I don't get it?? Sad

     

    PS. When i comment out the namespace System.Linq.Dynamic, I still get compile time errors.

    Error 1 The type arguments for method 'System.Linq.Enumerable.Where<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,bool>)' cannot be inferred from the usage. Try specifying the type arguments explicitly. R:\Visual Studio Projects\.......blah blah blah ........\UserInfo.cs 52 61 MyApplication

    Tuesday, August 5, 2008 6:47 AM
  •  

    *another polite bump*
    Thursday, August 7, 2008 1:20 AM
  • The problem here is that you're trying to plug an expression into a Where clause that operates over an EntitySet. This is not possible because EntitySet doesn't implement IQueryable<>. There is a fairly simple workaround, however - check out LINQKit.

    Note that you don't need to reference dynamic LINQ for this solution - using PredicateBuilder is simply a case of copying those few lines of code into your project.

    Regards

    Joe
    Sunday, August 10, 2008 11:24 AM
    Answerer
  • Hi Joe,

    thanks for taking to time to help me out here Smile

     

    In my last reply to this thread, just above, I tried replacing my dynamic LINQ with your LINQKit PredicateBuilder suggestion ... but got some compiler errors.

     

    Here's the code i did that was using LINQKit & PredicateBuilder :-

     

    Code Snippet

    public static Expression<Func<tblAudit, bool>> GetAddressPredicate(string primaryCity,

        string subdivision,

        string countryRegion)

    { ... }

     

     

    Code Snippet

    var whereClause = GetAddressPredicate(null, ""Ontario", "Canada");

    var x = (from u in db.UserInfos

               select new Foo

               {

                   UserInfo = u,

                   LocalScore = u.Audits.Where(whereClause).Sum(aa => (int)aa.Score) +

                       u.Audits.Where(whereClause).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

               }).Distinct().OrderByDescending(r => r.LocalScore).Take(5).ToList();

     

    ** Green highlights the errors.

     

     

    But it didn't work. Could you see what I was doing wrong and why it was wrong? I thought i was passing my PredicateBuilder result to the EntitySet as suggested (or at least what i thought you said)...

    Monday, August 11, 2008 12:24 AM
  • Add a reference to the LINQKit assembly, and then modify your query as follows (note the changes in bold):

    var x = (from u in db.UserInfos.AsExpandable()

               select new Foo

               {

                   UserInfo = u,

                   LocalScore = u.Audits.Where(whereClause.Compile()).Sum(aa => (int)aa.Score) +

                       u.Audits.Where(whereClause.Compile()).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

               }).Distinct().OrderByDescending(r => r.LocalScore).Take(5).ToList();



    It will then compile and run correctly!

    Joe
    Monday, August 11, 2008 5:52 AM
    Answerer
  • Thanks Joe -- that helped perfectly. I've never seen the AsExpandable() and .Compile() methods before .. and they were not mentioned in the PredicateBuilder example i based my code off, from your website.

     

    a few questions thought (because i hate not know WHY something works).

     

    1) what does the AsExpandable() do?

    2) If i have the following.. (take note of the .Compile() method)...

     

    Code Snippet

    var whereClause = tblAudit.GetAddressPredicate(primaryCity, subdivision, countryRegion).Compile()

     

     

    and then have this (notice the LACK of .Compile() method)

     

    Code Snippet

    LocalScore = u.tblAudits.Where(whereClause).Sum(aa => (int)aa.Score) +

        u.tblAudits.Where(whereClause).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)

     

     

    the code doesn't work. Why would it require me to compile the same Expression<..> twice (for the code to work) instead of once (in my example, here) which dies when the code is ran?

     

    3) Finally, is this the best way to write this line (when the where clause returns NULL as the result)

    Code Snippet
    ((
    Nullable<int>)(u.tblAudits.Where(whereClause.Compile()).Where(ab => ab.IdActivity == 9).Sum(ac => (int)ac.Score)) ?? 0)

     

     

    Notice how i have to make the result nullable .. and then check against it? otherwise it tries do to + NULL == runtime error (unless i make the LocalScore an int?, which i don't want to do).

     

    thanks heaps joe and love the kit (and can't wait for intellisence, for pad).

    Monday, August 11, 2008 8:04 AM
  • > 1. What does the AsExpandable do?

    Like with sausages, it's sometimes better just to enjoy the taste and not know what's inside! Seriously, if you're interested, the following explains it:

    http://www.albahari.com/nutshell/linqkit.html
    http://tomasp.net/blog/linq-expand.aspx

    Regarding calling Compile(), you must do this to satisfy the compiler. It's because EntitySet<> implements only IEnumerable and not IQueryable, and so the Expression<> based operators cannot be used. Compiling an expression turns it into an ordinary delegate, which works with the Enumerable-based query operators. The Compile() method never actually executes - it gets stripped out when the expression tree is revisited.

    I don't quite understand your third question.

    Joe

    Monday, August 11, 2008 11:53 AM
    Answerer
  • with my third question, i was asking for the sum, for a single column, of all the results which the where clause returns. In that particular piece of code, the where clause is so specific, that the result returns no results ... so the sum is trying to sum zero results ... which returns a result on 'null'.

     

    as such, i was wondering if that was the best way to handle a specific Where(..) scenario using the linqkit, but casting the returned result to a Nullable<T> and then 'handling' the result if it was null (ie. using the ?? syntax).

     

    Basically, i thought that if i did a sum on the Where(..) method, when there was no results, the 'sum' result would be 0, not null.

    Monday, August 11, 2008 2:17 PM