含内连接与UNION的SQL转LINQ求助：排查现有LINQ写法错误

先看你提供的原始SQL查询，它包含两个查询块通过UNION合并，最后按第4列（也就是human_id）排序。我们先拆解你的现有LINQ代码里的明显错误，再给出完整的正确实现。

你的原始SQL查询 #

SELECT dd.payment_id, dd.enterprise_id, dd.practice_id, dd.human_id, dd.enchant_id, dd.human_payment_id, ct.description, pm.payer_nbr, pm.group_name, pm.group_nbr, pm.plan_nbr, pm.plan_type, pm.payer_exp_date 
FROM enchant_payment dd, human_payment hp, human hum, person_relationship pr, code_tables ct, payment_mstr pm 
WHERE ((dd.practice_id = '0001' AND dd.person_id = 'CCDDA2BA-ACBE-4728-AC67-8B6043573345' AND dd.enchant_id = 'CB65D1A2-FED7-4EAF-BAA6-DB7085D8D00F') AND (hp.person_payer_id = dd.human_payment_id) AND (hum.person_id = dd.human_id) AND (dd.human_id = pr.person_id AND dd.person_id = pr.related_person_id) AND (pr.relation_code = ct.code AND ct.code_type = 'relation')) AND (hp.payer_id = pm.payer_id) 
UNION 
SELECT dd.payment_id, dd.enterprise_id, dd.practice_id, human_id, dd.enchant_id, dd.human_payment_id, 'Patient', pm.payer_nbr, pm.group_name, pm.group_nbr, pm.plan_nbr, pm.plan_type, pm.payer_exp_date 
FROM enchant_payment dd, human_payment hp, human hum, payment_mstr pm 
WHERE ((dd.practice_id = '0001' AND dd.person_id = 'CCDDA2BA-ACBE-4728-AC67-8B6043573345' AND dd.enchant_id = 'CB65D1A2-FED7-4EAF-BAA6-DB7085D8D00F') AND (hp.person_payer_id = dd.human_payment_id) AND (hum.person_id = dd.human_id) AND dd.person_id = dd.human_id) AND (hp.payer_id = pm.payer_id) 
ORDER BY 4

你现有LINQ代码的错误点 #

• 连接条件完全错误：你写的join hp in m_Context.HumanPayment on hp.human_payment_id equals hp.human_payment_id是让表自己和自己关联，完全没和EnchantPayment的human_payment_id建立关联，正确应该是on ep.human_payment_id equals hp.human_payment_id
• 变量引用混乱：你在select里用了dd，但LINQ的数据源变量定义的是ep（from ep in m_Context.EnchantPayment），还出现了dd.dd.enterprise_id这种明显的笔误
• 缺失UNION逻辑：完全没实现SQL里第二个查询块的内容，也没有做合并操作
• 遗漏筛选与关联表：没加上SQL里的practice_id、person_id、enchant_id筛选条件，也没处理第一个查询块里person_relationship和code_tables的关联
• 缺少排序逻辑：没对应SQL最后的ORDER BY 4（按human_id排序）

正确的LINQ实现 #

假设你的实体类与数据库表字段对应正确，EnchantPaymentDto包含所有需要的属性，完整实现如下：

// 构建UNION的第一个查询块：对应SQL中UNION上方的部分
var query1 = from ep in m_Context.EnchantPayment
             join hp in m_Context.HumanPayment on ep.human_payment_id equals hp.human_payment_id
             join hum in m_Context.Human on ep.human_id equals hum.person_id
             join pr in m_Context.PersonRelationship 
                 on new { ep.human_id, ep.person_id } equals new { pr.person_id, pr.related_person_id }
             join ct in m_Context.CodeTables 
                 on new { pr.relation_code, CodeType = "relation" } equals new { ct.code, ct.code_type }
             join pm in m_Context.PaymentMasterEntries on hp.payer_id equals pm.payer_id
             where ep.practice_id == "0001" 
                 && ep.person_id == "CCDDA2BA-ACBE-4728-AC67-8B6043573345" 
                 && ep.enchant_id == "CB65D1A2-FED7-4EAF-BAA6-DB7085D8D00F"
             select new EnchantPaymentDto
             {
                 PaymentId = ep.payment_id,
                 EnterpriseId = ep.enterprise_id,
                 PracticeId = ep.practice_id,
                 HumanId = ep.human_id,
                 EnchantId = ep.enchant_id,
                 HumanPaymentId = ep.human_payment_id,
                 Description = ct.description,
                 PayerNbr = pm.payer_nbr,
                 GroupName = pm.group_name,
                 GroupNbr = pm.group_nbr,
                 PlanNbr = pm.plan_nbr,
                 PlanType = pm.plan_type,
                 PayerExpDate = pm.payer_exp_date
             };

// 构建UNION的第二个查询块：对应SQL中UNION下方的部分
var query2 = from ep in m_Context.EnchantPayment
             join hp in m_Context.HumanPayment on ep.human_payment_id equals hp.human_payment_id
             join hum in m_Context.Human on ep.human_id equals hum.person_id
             join pm in m_Context.PaymentMasterEntries on hp.payer_id equals pm.payer_id
             where ep.practice_id == "0001" 
                 && ep.person_id == "CCDDA2BA-ACBE-4728-AC67-8B6043573345" 
                 && ep.enchant_id == "CB65D1A2-FED7-4EAF-BAA6-DB7085D8D00F"
                 && ep.person_id == ep.human_id // 对应SQL里的dd.person_id = dd.human_id条件
             select new EnchantPaymentDto
             {
                 PaymentId = ep.payment_id,
                 EnterpriseId = ep.enterprise_id,
                 PracticeId = ep.practice_id,
                 HumanId = ep.human_id,
                 EnchantId = ep.enchant_id,
                 HumanPaymentId = ep.human_payment_id,
                 Description = "Patient", // 固定字符串值
                 PayerNbr = pm.payer_nbr,
                 GroupName = pm.group_name,
                 GroupNbr = pm.group_nbr,
                 PlanNbr = pm.plan_nbr,
                 PlanType = pm.plan_type,
                 PayerExpDate = pm.payer_exp_date
             };

// 合并两个查询并排序：LINQ的Union和SQL的UNION行为一致，自动去重
var result = query1.Union(query2)
                   .OrderBy(dto => dto.HumanId) // 对应SQL的ORDER BY 4（human_id为第4列）
                   .ToList();

return result;

补充说明 #

• 如果需要保留重复项（对应SQL的UNION ALL），把Union换成Concat即可
• 多字段连接时，用匿名类型来匹配多个条件（比如person_relationship的双字段关联）
• 注意调整Dto的属性名，确保和你实际定义的EnchantPaymentDto字段一致

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