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??
Dim oppties = From opt In context.OpportunitySet
From act In context.AccountSet.Where(Function(A) A.AccountId = opt.ParentAccountId.Id).DefaultIfEmpty()
From cnt In context.ContactSet.Where(Function(C) C.ContactId = opt.ParentContactId.Id).DefaultIfEmpty()
Where opt.ParentAccountId.Equals(accId)
Select New OpportunityInfo With {.OpportunityId = opt.OpportunityId.Value.ToString("B"),
.ParentAccountId = opt.ParentAccountId.Id,
.ParentAccountName = act.Name,
.ParentAccountNumber = act.AccountNumber,
.ProductGroup = opt.productgroup.Value,
.MktSegment = act.DIVTeam,
.EstRevenue = opt.ssco_EstimatedRevenue,
.Topic = opt.Name,
.CreatedOn = opt.CreatedOn,
.Contact = If(cnt Is Nothing, String.Empty, cnt.FullName)}
Dim oppties = (From opt In context.OpportunitySet Group Join
act In context.AccountSet On opt.ParentAccountId.Id Equals act.AccountId Into gr = Group From act In gr.DefaultIfEmpty() Group Join
cnt In context.ContactSet On opt.ParentContactId.Id Equals cnt.ContactId Into ct = Group From cnt In ct.DefaultIfEmpty()
Where opt.ParentAccountId.Equals(accId)
Select New OpportunityInfo With {.OpportunityId = If(opt.OpportunityId.Value.ToString("B") Is Nothing, String.Empty, opt.OpportunityId.Value.ToString("B")),
.Topic = If(opt.Name Is Nothing, String.Empty, opt.Name),
.MktSegment = If(act.DIVTeam Is Nothing, String.Empty, act.DIVTeam),
.EstRevenue = If(opt.ssco_EstimatedRevenue Is Nothing, 0.0, Math.Round(Decimal.Parse(opt.EstimatedRevenue), 2)),
.CreatedOn = If(opt.CreatedOn Is Nothing, Nothing, opt.CreatedOn),
.ProductGroup = If(opt.productgroup Is Nothing, String.Empty, opt.productgroup.Value),
.Contact = If(cnt.FullName Is Nothing, String.Empty, cnt.FullName),
.ParentAccountId = If(opt.ParentAccountId Is Nothing, Nothing, opt.ParentAccountId.Id),
.ParentAccountName = If(act.Name Is Nothing, String.Empty, act.Name),
.ParentAccountNumber = If(act.AccountNumber Is Nothing, String.Empty, act.AccountNumber)
})
