Answered by:
Microsoft CRM 5 - LINQ provider "constraints"

Question
-
Hi,
A classical "SQL IN / .Contains()" condition. Any work arounds for the CRM LINQ provider?
List<string> ids = new List<string>(); ids.Add("123");ids.Add("456"); var xyz= (from x in y where ids.Contains(x.Id)) select x).ToList();
This obviously does not work with the CRM LINQ provider. How have you gotten around this?- Edited by Jeremias K Tuesday, January 3, 2012 9:23 AM Typo
Tuesday, January 3, 2012 9:22 AM
Answers
-
Hi Jerimias,
See : http://www.crmcodex.com/2011/12/tip-getting-around-a-contains-issue-in-linq-for-mscrm2011/
This uses the PredicateBuilder and AsExpandable to basically construct a dynamic LINQ query that is the equivalent of:
.Where(c=>comparison1 || comparison2 || ...)
You will need to download and reference LinqKit: http://www.albahari.com/nutshell/linqkit.aspx
If you want to test this out using LinqPad (http://www.linqpad.net/), first add a reference to LinqKit (press F4 in LinqPad), and add the following c# statements:
List<string> lastNames = new List<string>() { "test 45", "test 46", "test 48"}; var predicate = PredicateBuilder.False<Contact>(); foreach (string lastName in lastNames) { string lastNameValue = lastName; predicate = predicate.Or(c => c.LastName == lastNameValue); } var contacts = ( from c in ContactSet.AsExpandable().Where (predicate ) select new {Id = c.Id,LastName=c.LastName}); contacts.Dump();
Execute and look at the Query Expression created, it uses:<Filters> <FilterExpression> <Conditions> <ConditionExpression> <AttributeName>lastname</AttributeName> <Operator>Equal</Operator> <Values xmlns:d7p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d7p1:anyType xmlns:d8p1="http://www.w3.org/2001/XMLSchema" i:type="d8p1:string">test 48</d7p1:anyType> </Values> </ConditionExpression> </Conditions> <FilterOperator>Or</FilterOperator> <Filters> <FilterExpression> <Conditions> <ConditionExpression> <AttributeName>lastname</AttributeName> <Operator>Equal</Operator> <Values xmlns:d9p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d9p1:anyType xmlns:d10p1="http://www.w3.org/2001/XMLSchema" i:type="d10p1:string">test 45</d9p1:anyType> </Values> </ConditionExpression> <ConditionExpression> <AttributeName>lastname</AttributeName> <Operator>Equal</Operator> <Values xmlns:d9p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d9p1:anyType xmlns:d10p1="http://www.w3.org/2001/XMLSchema" i:type="d10p1:string">test 46</d9p1:anyType> </Values> </ConditionExpression> </Conditions> <FilterOperator>Or</FilterOperator> <Filters /> </FilterExpression> </Filters> </FilterExpression> </Filters>
It's interesting that the CRM Linq provided uses a single Filter Expression for the first comparison, then groups the rest as multiple filters in another filter expression.Scott
www.develop1.net
- Edited by Scott Durow (MVP)MVP, Editor Tuesday, January 3, 2012 12:20 PM
- Marked as answer by Jeremias K Monday, January 9, 2012 7:57 AM
Tuesday, January 3, 2012 11:56 AMAnswerer
All replies
-
Hi,
I suggest you to use FetchXML in CRM, which allows all operations, and then you can transfer fetchxml result to IEnumberable ot List
If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Many Thanks -Bhautik Desai xRM Technologies- Proposed as answer by Bhautik Desai-XRM Tech Tuesday, January 3, 2012 9:29 AM
Tuesday, January 3, 2012 9:29 AM -
Hi,
Yeah. I'm aware of this, but the main reason for me to use the LINQ provider is to get rid of FetchXML.
- Edited by Jeremias K Tuesday, January 3, 2012 9:39 AM typo
Tuesday, January 3, 2012 9:39 AM -
Hi Jerimias,
See : http://www.crmcodex.com/2011/12/tip-getting-around-a-contains-issue-in-linq-for-mscrm2011/
This uses the PredicateBuilder and AsExpandable to basically construct a dynamic LINQ query that is the equivalent of:
.Where(c=>comparison1 || comparison2 || ...)
You will need to download and reference LinqKit: http://www.albahari.com/nutshell/linqkit.aspx
If you want to test this out using LinqPad (http://www.linqpad.net/), first add a reference to LinqKit (press F4 in LinqPad), and add the following c# statements:
List<string> lastNames = new List<string>() { "test 45", "test 46", "test 48"}; var predicate = PredicateBuilder.False<Contact>(); foreach (string lastName in lastNames) { string lastNameValue = lastName; predicate = predicate.Or(c => c.LastName == lastNameValue); } var contacts = ( from c in ContactSet.AsExpandable().Where (predicate ) select new {Id = c.Id,LastName=c.LastName}); contacts.Dump();
Execute and look at the Query Expression created, it uses:<Filters> <FilterExpression> <Conditions> <ConditionExpression> <AttributeName>lastname</AttributeName> <Operator>Equal</Operator> <Values xmlns:d7p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d7p1:anyType xmlns:d8p1="http://www.w3.org/2001/XMLSchema" i:type="d8p1:string">test 48</d7p1:anyType> </Values> </ConditionExpression> </Conditions> <FilterOperator>Or</FilterOperator> <Filters> <FilterExpression> <Conditions> <ConditionExpression> <AttributeName>lastname</AttributeName> <Operator>Equal</Operator> <Values xmlns:d9p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d9p1:anyType xmlns:d10p1="http://www.w3.org/2001/XMLSchema" i:type="d10p1:string">test 45</d9p1:anyType> </Values> </ConditionExpression> <ConditionExpression> <AttributeName>lastname</AttributeName> <Operator>Equal</Operator> <Values xmlns:d9p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d9p1:anyType xmlns:d10p1="http://www.w3.org/2001/XMLSchema" i:type="d10p1:string">test 46</d9p1:anyType> </Values> </ConditionExpression> </Conditions> <FilterOperator>Or</FilterOperator> <Filters /> </FilterExpression> </Filters> </FilterExpression> </Filters>
It's interesting that the CRM Linq provided uses a single Filter Expression for the first comparison, then groups the rest as multiple filters in another filter expression.Scott
www.develop1.net
- Edited by Scott Durow (MVP)MVP, Editor Tuesday, January 3, 2012 12:20 PM
- Marked as answer by Jeremias K Monday, January 9, 2012 7:57 AM
Tuesday, January 3, 2012 11:56 AMAnswerer -
Hi Jeremias,
I've added an example on how to do this at: http://www.develop1.net/public/post/Dynamically-construct-a-where-query-on-a-Dynamics-CRM-2011-Linq-query.aspx
Hope it helps,
Scott
www.develop1.netTuesday, January 3, 2012 3:03 PMAnswerer -
Thanks Scott! I will check your example tomorrow. Will let you know.
Wednesday, January 4, 2012 2:00 PM -
Sorry for the late reply. I managed to solve my problem by using your example and some toying with my own code. Thanks a bunch.
Monday, January 9, 2012 7:57 AM