locked
Dynamics CRM developer Question :- Linq Query to do 2 left outer join on Opportunity, Account, Contact RRS feed

  • Question

  • I am trying to use Linq query to get the Opportunity data and its Parentaccountid Account Fields and its ParentCotnactId contact Full Name in one Linq query.

    Most of the time Opportunity may have account associated with that but it may not have parentcontactid associated with that. So i need to do left outer join but for some reason it didn't work.

    can any one assist on this??

    1. Dim oppties = From opt In context.OpportunitySet
    2. From act In context.AccountSet.Where(Function(A) A.AccountId = opt.ParentAccountId.Id).DefaultIfEmpty()
    3. From cnt In context.ContactSet.Where(Function(C) C.ContactId = opt.ParentContactId.Id).DefaultIfEmpty()
    4. Where opt.ParentAccountId.Equals(accId)
    5. Select New OpportunityInfo With {.OpportunityId = opt.OpportunityId.Value.ToString("B"),
    6. .ParentAccountId = opt.ParentAccountId.Id,
    7. .ParentAccountName = act.Name,
    8. .ParentAccountNumber = act.AccountNumber,
    9. .ProductGroup = opt.productgroup.Value,
    10. .MktSegment = act.DIVTeam,
    11. .EstRevenue = opt.ssco_EstimatedRevenue,
    12. .Topic = opt.Name,
    13. .CreatedOn = opt.CreatedOn,
    14. .Contact = If(cnt Is Nothing, String.Empty, cnt.FullName)}


    1. Dim oppties = (From opt In context.OpportunitySet Group Join
    2. act In context.AccountSet On opt.ParentAccountId.Id Equals act.AccountId
    3. Into gr = Group From act In gr.DefaultIfEmpty() Group Join
    4. cnt In context.ContactSet On opt.ParentContactId.Id Equals cnt.ContactId
    5. Into ct = Group From cnt In ct.DefaultIfEmpty()
    6. Where opt.ParentAccountId.Equals(accId)
    7. Select New OpportunityInfo With {.OpportunityId = opt.OpportunityId.Value.ToString("B"),
    8. .Topic = If(opt.Name Is Nothing, String.Empty, opt.Name),
    9. .MktSegment = If(act.DIVTeam Is Nothing, String.Empty, act.DIVTeam),
    10. .EstRevenue = opt.EstimatedRevenue,
    11. .CreatedOn = If(opt.CreatedOn Is Nothing, Nothing, opt.CreatedOn),
    12. .ProductGroup = opt.productgroup.Value,
    13. .Contact = If(cnt.FullName Is Nothing, String.Empty, cnt.FullName),
    14. .ParentAccountId = opt.ParentAccountId.Id,
    15. .ParentAccountName = If(act.Name Is Nothing, String.Empty, act.Name),
    16. .ParentAccountNumber = act.AccountNumber
    17. })

    • Edited by Nicksoft2011 Tuesday, September 20, 2016 1:14 PM
    Tuesday, September 20, 2016 1:12 PM

All replies

  • https://social.microsoft.com/Forums/en-US/775b2366-a969-494a-830d-970a4c56d4a4/linq-to-crm-sample-left-and-inner-join?forum=crm
    Wednesday, September 21, 2016 2:08 PM