Dynamic Linq for IN Clause

[Devanand Chahal]

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

[Petko_I]

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

[Devanand Chahal]

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

[Petko_I]

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