Dynamic Linq for IN Clause

4 posts, 0 answers
  1. Devanand Chahal
    Devanand Chahal avatar
    26 posts
    Member since:
    Mar 2009

    Posted 14 Sep 2010 Link to this post

    Hi,

    I am facing a similar problem for IN Clause of the SQL.
    I am using the dynamic expressions to build the lambda expression for applying the IN clause .  I have used the code like below to generate the lambda expression.

     

     

     

     

     Expression.Call(ContainsMethodInfo, new Expression[] { rightExp, leftOp })

    ContainsMethodInfo:- It is the MethodInfo type object having the information regarding the information for "Contains" method of IEnumerable interface.
    rightExp:= this contains the expression for list of values for which data is searched
    leftOp := This is the expression for the field in which search to be made.

    My problem is that , it works for the long type of fields ,but not for String type of field.
    Let me know if more information is required.

    Thanks
    Devanand
  2. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 14 Sep 2010 Link to this post

    Hi Devanand Chahal,

    I see that you have focused your attention on building dynamic LINQ queries. As far as I understand you want to build a predicate which checks whether an item is present in a collection specified as a query. Is that right? Can you tell us what the exact query you are trying to build is – for example, how would you want your query to look in SQL? The approach you have chosen for building queries dynamically can turn out to be much more complicated than it is actually needed. Our LINQ implementation runs wide but there are some features regarding subqueries which are in our to-do list and haven't made into the latest official release. Currently, a subquery which uses Contains cannot be pushed to the server for execution. I suspect this might be causing you the problem.

    Can you have a look at the link Dynamic filter expressions in an OpenAccess LINQ query and see if the scenario described there applies to your case?

    I am looking forward to your reply.

    Regards,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Devanand Chahal
    Devanand Chahal avatar
    26 posts
    Member since:
    Mar 2009

    Posted 14 Sep 2010 Link to this post

    Yes, I want to build a predicate which checks whether an item is present in a collection specified as a query.

    The query which I am trying to achieve is some thing like this

    Select * from Employee where Name IN ('Peter','Jon');

    and the strange part is that it works for query like
    Select * from Employee where Empid IN (1,2);

    The only difference between the two query is that the first one is on VarChar type field and second one is for integer.

    The error which I get on running the first query is "SQL IN statement only allowed with column on left side."
    I hope I have answer your questions.

    Thanks
    Devanand
  5. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 17 Sep 2010 Link to this post

    Hi Devanand Chahal,

    I am still not able to reproduce your problem. The exception you receive actually leads me to believe there is something wrong in the definition of the dynamic expression. Is there any chance that you have swapped the places of the left and right expressions?

    Can you also tell me why you prefer the dynamic LINQ query to a common query? If your search collection is defined as a list (and not a query) then the LINQ query should work fine.

    IList<string> names = new List<string>(){ "Peter", "John" };          
    var targetQuery = from e in context.Employees
                      where names.Contains(e.FirstName)
                      select e;
    If you have a subquery, then the following lines should work as well.


    // retrieve the collection in memory
    IList<string> names = collectionQuery.ToList();          
    var targetQuery = from e in context.Employees
                      where names.Contains(e.FirstName)
                      select e;

    The subquery which builds the collection (and on which the Contains method will be invoked) cannot be propagated directly for execution to the server. You will need to retrieve the collection under question in memory for your target query to work. Building expression trees dynamically makes sense when the whole composite query will be executed on the server.

    I think I might be missing something essential here. I would be glad if you could elaborate more on your scenario or give me the exact code snippet that produces your error. There should not be any difference in the collection type you are using – be it string-based or integer-based.

    I am looking forward to resolving this issue.

    Regards,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top