Linq Join Duplicates records c# -

Linq Join Duplicates records c# -

i having problem , grateful if help. have 3 lists 3 db tables. accounts, contact , contact details. issue when seek add together 2 fields query duplicates records illustration result should contain 700 rows in list if add together contact.firstname , contact.lastname results 8000.

each business relationship can have many contacts, each contact have many contactdetails

var dataset = (from business relationship in ctx.accounts contact in ctx.contacts 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 , contact.lastname causing duplicates records when alter below: results go 700 8000.

select new { account.id, account.reference, account.accountname, contactdetails.title, account.balance, contact.firstname , contact.lastname }).distinct().tolist();

this not give right results

you're doing outer bring together accounts, contacts , contact details. every account, every contact, every contactdetail matches criteria, you'll end entry in result set. tried around throwing .distinct() on result set, happened filter results downwards bit. add together properties contact, end bunch of different first , lastly names each object query returning before.

you meant this:

var dataset = (from business relationship in ctx.accounts contact in account.contacts contactdetails in contact.contactdetails select new { account.id, account.reference, account.accountname, contactdetails.title, account.balance, contact.firstname , contact.lastname }) .tolist(); update

if don't have navigation properties, can accomplish same results using joins or where clauses:

var dataset = (from business relationship in ctx.accounts bring together contact in ctx.contacts on account.id equals contact.accountid bring together 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 business relationship in ctx.accounts contact in ctx.contacts account.id == contact.accountid contactdetails in ctx.contactdetails 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 same execution plan, join clauses represent intent improve semantic standpoint.

c# linq list