CODEWITHSUNDEEP
c#linqlist
Jimbo Jones
Jimbo Jones

Reputation: 1002

Linq Join Duplicates records c#

I am having a problem and I would be grateful if anyone could help. I have 3 lists from 3 db tables. Accounts, contact and contact details. The issue is when I try to add to 2 extra fields to my query it duplicates records for example result should contain 700 rows in the list but if I add contact.firstname and contact.lastname the results are 8000.

Each Account can have many contacts, Each contact have many contactdetails

var Dataset = (from account in ctx.Accounts
from contact in ctx.Contacts
from contactdetails in ctx.ContactDetails.Where(x => x.id == account.id || x.id == 
account.contactdetailsid)

select new { account.id, account.Reference, account.AccountName, contactdetails.Title, 
account.Balance }).Distinct().ToList();

I cant seem figure out why adding contact.firstname and contact.lastname is causing duplicates records when i change to below: Results go from 700 to 8000.

select new { account.id, account.Reference, account.AccountName, contactdetails.Title, 
account.Balance, contact.firstname , contact.lastname }).Distinct().ToList();

This does not give the correct results

Upvotes: 1

Views: 6348

Answers (2)

StriplingWarrior
StriplingWarrior

Reputation: 156524

You're doing an outer join with accounts, contacts and contact details. So for every Account, for every Contact, for every ContactDetail that matches your criteria, you'll end up with an entry in the result set. Someone tried to get around this by throwing a .Distinct() on the result set, which happened to filter the results down a bit. But as soon as you add properties from the contact, you end up with a bunch of different first and last names for each object that the query was returning before.

You probably meant to do something like this:

var Dataset = (from account in ctx.Accounts
from contact in account.Contacts
from contactdetails in contact.ContactDetails
select new { account.id, account.Reference, account.AccountName, contactdetails.Title, 
    account.Balance, contact.firstname , contact.lastname })
.ToList();

Update

If you don't have navigation properties, you can achieve the same results using joins or where clauses:

var Dataset = (from account in ctx.Accounts
    join contact in ctx.Contacts 
        on account.id equals contact.accountid
    join contactdetails in ctx.ContactDetails 
        on contact.contactdetailsid equals contactdetails.id
    select new { account.id, account.Reference, account.AccountName, contactdetails.Title, 
        account.Balance, contact.firstname , contact.lastname })
.ToList();

... or ...

var Dataset = (from account in ctx.Accounts
    from contact in ctx.Contacts 
    where account.id == contact.accountid
    from contactdetails in ctx.ContactDetails 
    where contact.contactdetailsid == contactdetails.id
    select new { account.id, account.Reference, account.AccountName, contactdetails.Title, 
        account.Balance, contact.firstname , contact.lastname })
.ToList();

Either of these approaches should produce exactly the same execution plan, but the join clauses represent your intent better from a semantic standpoint.

Upvotes: 5

Jimbo Jones
Jimbo Jones

Reputation: 1002

From StriplingWarrior post it helped me find a working solution. 

   var Dataset = (
   from account in ctx.Accounts
   from contact in ctx.Contacts.Where(x => x.accountid == account.id)
   from contactdetails in ctx.ContactDetails.Where(x =>  x.id == contact.contactdetailsid)

                              select new {
                                   AccountID    = account.id,
                                   Reference    = account.Reference,
                                   AccountName  = account.AccountName,
                                   External     = contact == null ? String.Empty : (contact.External),
                                   Phone        = contactdetails == null ? String.Empty : (contactdetails.Title),
                                   Balance      = account.Balance,
                                   ContactName  = contact == null ? String.Empty : (contact.firstname + " " + contact.lastname),                            
                               }).ToList();

Upvotes: 0

Related Questions