Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Left Outer join LINQ query with four entities in CRM 2011 and with multiple where clauses RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a situation in Microsoft Dynamics CRM 2011
    Here I have a set of Accounts which has Permits and permits ae related to permitCounty and permitCounty is related to County

    I would like to get get an output like this query.

    But it is not proper 

    strSearch="Some text";
    var lstItems = (from county in x.De_countySet
    join permitcounty in x.de_permitcountySet on county.De_countyId equals permitcounty.de_CountyId.Id
    join permit in x.de_permitSet on permitcounty.de_PermitId.Id equals permit.de_permitId 
    join account in x.AccountSet on permit.de_AccountId.Id equals account.AccountId into outer 
    where permit.de_Type.Equals("Type1") && permit.statuscode.Equals("Active")                // Permit Type as Type1 and Status as Active
    where account.De_LegalName.Contains(strSearch) || account.Name.Contains(strSearch)        // Search in Legal Name and Account full name   
    where county.de_CountyName.Contains(strSearch)                                            // Search in County Name 
    orderby county.de_CountyName
    from account in outer.DefaultIfEmpty()
    select new clsTemp 
    {Name = account.Name,
    De_LegalName = account.De_LegalName,
     Address1_Line1 = account.Address1_Line1,
    Address1_City = account.Address1_City,
    Address1_PostalCode = account.Address1_PostalCode,
    de_CountyName = county.de_CountyName
    }).ToList();

    Any help is appreciated.

    Vinu



    Friday, October 19, 2012 5:11 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    This isn't going to work with the CRM2011 Linq provider - there are limitations around outer joins where they precede inner joins.

    I suggest you write your query in fetchxml (or a queryexpression) using the link-entity element with  a link-type of inner or outer.

    hth,

    Scott

    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, October 25, 2012 3:09 PM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Venu,

    Can you refer following posts for the "left outer joins' in LINQ

    http://smehrozalam.wordpress.com/2009/06/10/c-left-outer-joins-with-linq/

    http://stackoverflow.com/questions/525194/linq-inner-join-vs-left-join

    Hope this helps,

    Yusuf

    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Thanks and Regards, Mohammad Yusuf Ansari http://microxrm.blogspot.in

    Saturday, October 20, 2012 9:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ansari,

    Thank you very much for the suggestions.

    Currently I am trying to deal with 4 entities with a relation ships like

    County is related to PermitCounty

    permit County is related to Permit

    and Permit is related to Account

    I expect an output of all the Counties and multiple accounts relatede to all the counties, that is the left outer of Counties.

    Vinu

    Monday, October 22, 2012 1:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can try the following :

    var query = from country in abc.Country.Include("PermitCountry").Include("Permit").Include("Account") select country

    Thursday, October 25, 2012 1:50 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    This isn't going to work with the CRM2011 Linq provider - there are limitations around outer joins where they precede inner joins.

    I suggest you write your query in fetchxml (or a queryexpression) using the link-entity element with  a link-type of inner or outer.

    hth,

    Scott

    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, October 25, 2012 3:09 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Scott,

    the article at following link has implemented it,

    http://blogs.inkeysolutions.com/2012/06/left-outer-join-using-linq.html

    can you please tell me the drawbacks of the implemenation ?

    i am planning to use but reading your comments above i am in dilemma.


    Even in CRM 2011 SDK they have given sample
    • Proposed as answer by sagarps Tuesday, March 19, 2013 12:25 PM
    • Unproposed as answer by sagarps Tuesday, March 19, 2013 12:25 PM
    • Edited by sagarps Tuesday, March 19, 2013 12:26 PM
    Tuesday, March 19, 2013 11:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Saga,

    You can do LEFT OUTER JOINS using the LINQ provider for CRM2011 provided there is just a single level - but the OP was trying to do more than one level deep.

    hth

    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Tuesday, March 19, 2013 1:52 PM
    Answerer