locked
Microsoft CRM 5 - LINQ provider "constraints" RRS feed

  • 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

    Tuesday, January 3, 2012 11:56 AM
    Answerer

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
    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

    Tuesday, January 3, 2012 11:56 AM
    Answerer
  • Tuesday, January 3, 2012 3:03 PM
    Answerer
  • 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