Linq关联多IQueryable时按ID分组并限制单条记录

原代码的问题 #

原代码中，每个关联查询（如clientNames、clientAddresses）可能包含同一client_id的多条记录，后续左连接会产生笛卡尔积，导致最终结果中同一client_id对应多条不同属性组合的记录。Distinct()方法因为对象属性组合不同，无法有效去重，达不到每个ID仅保留一条记录的需求。

优化方案 #

对每个关联查询先按client_id分组，每组选取任意一条记录（比如使用FirstOrDefault()），确保每个client_id在每个关联数据源中仅存在一条记录，再进行左连接即可避免笛卡尔积，最终自然每个ID仅返回一条结果。

优化后的代码 #

// 先对每个关联表按client_id分组，取每组第一条记录
var clientNames = await (
    from n in db.Names
    join cnl in db.ClientNameLinks on n.name_id equals cnl.name_id
    where request.Ids.Contains(cnl.client_id)
          && n.name_id > 0
          && n.nametype_id != null
    group n by cnl.client_id into g
    select new
    {
        ClientId = g.Key,
        DisplayName = g.FirstOrDefault()?.display_name
    }).ToListAsync();

var clientAddresses = await (
    from a in db.Addresses
    join cal in db.ClientAddressLinks on a.address_id equals cal.address_id
    where request.Ids.Contains(cal.client_id)
        && a.address_id > 0
    group a by cal.client_id into g
    select new
    {
        ClientId = g.Key,
        DisplayAddress = g.FirstOrDefault()?.display_address
    }).ToListAsync();

var clientEmails = await (
    from e in db.Emails
    join cel in db.ClientEmailLinks on e.email_id equals cel.email_id
    where request.Ids.Contains(cel.client_id)
        && e.email_id > 0
    group e by cel.client_id into g
    select new
    {
        ClientId = g.Key,
        EmailAddress = g.FirstOrDefault()?.email_address
    }).ToListAsync();

var clientPhones = await (
    from p in db.Phones
    join cpl in db.ClientPhoneLinks on p.phone_id equals cpl.phone_id
    where request.Ids.Contains(cpl.client_id)
        && p.phone_id > 0
    group p by cpl.client_id into g
    select new
    {
        ClientId = g.Key,
        PhoneNumber = g.FirstOrDefault()?.phone_num,
        PhoneExtension = g.FirstOrDefault()?.phone_ext
    }).ToListAsync();

// 以clientNames为基础，左连接其他已去重的数据源
var result = clientNames
    .GroupJoin(clientAddresses, 
        n => n.ClientId, 
        a => a.ClientId, 
        (n, aList) => new { n, Address = aList.FirstOrDefault() })
    .GroupJoin(clientEmails, 
        x => x.n.ClientId, 
        e => e.ClientId, 
        (x, eList) => new { x.n, x.Address, Email = eList.FirstOrDefault() })
    .GroupJoin(clientPhones, 
        x => x.n.ClientId, 
        p => p.ClientId, 
        (x, pList) => new PiiDiamondClient
        {
            ClientId = x.n.ClientId,
            DisplayName = x.n.DisplayName,
            DisplayAddress = x.Address?.DisplayAddress,
            EmailAddress = x.Email?.EmailAddress,
            PhoneNumber = pList.FirstOrDefault()?.PhoneNumber,
            PhoneExtension = pList.FirstOrDefault()?.PhoneExtension
        })
    .OrderBy(x => x.ClientId)
    .ToList();

return result;

关键说明 #

• 分组去重：每个关联查询通过group by client_id将同一ID的记录分组，再用FirstOrDefault()取组内任意一条记录，确保每个ID在每个数据源中唯一。
• 避免笛卡尔积：经过分组去重后，后续左连接不会产生多条组合记录，最终每个ID仅对应一条结果。
• 性能优化：如果使用的是EF Core，分组操作会被转换为SQL的GROUP BY或ROW_NUMBER()（取决于数据库提供商），在数据库端完成去重，减少内存处理压力。

内容的提问来源于stack exchange，提问作者fjps