Linq Join重复记录c#
本文关键字:记录 Join Linq | 更新日期: 2023-09-27 18:28:35
我有问题,如果有人能帮忙,我将不胜感激。我有3个数据库表中的3个列表。帐户、联系人和联系方式。问题是,当我试图在查询中添加2个额外的字段时,它会重复记录。例如,结果应该在列表中包含700行,但如果我添加contact.firstname和contact.lastname,结果是8000。
每个帐户可以有许多联系人,每个联系人都有许多联系方式
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();
当我改为以下时,我似乎不明白为什么添加contact.firstname和contact.lastname会导致重复记录:结果从700到8000。
select new { account.id, account.Reference, account.AccountName, contactdetails.Title,
account.Balance, contact.firstname , contact.lastname }).Distinct().ToList();
这不会给出正确的结果
您正在使用帐户、联系人和联系人详细信息进行外部联接。因此,对于每个帐户、每个联系人、每个符合您条件的ContactDetail,您最终都会在结果集中找到一个条目。有人试图通过在结果集上抛出.Distinct()
来绕过这一点,这恰好过滤了一些结果。但是,一旦从contact
添加属性,查询之前返回的每个对象都会有一堆不同的名字和姓氏。
你可能想做这样的事情:
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();
更新
如果您没有导航属性,则可以使用join
或where
子句来获得相同的结果:
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();
或
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();
这两种方法中的任何一种都应该产生完全相同的执行计划,但从语义角度来看,join
子句更能代表您的意图。
从StriplingWarrior的帖子中,它帮助我找到了一个可行的解决方案。
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();