c# - 内部连接和联合的 Sql 到 LinQ 转换

Question

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 (pp.person_payer_id = dd.human_payment_id)
AND (per.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 (pp.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 (pp.person_payer_id = dd.human_payment_id)
AND (per.person_id = dd.human_id)
AND dd.person_id = dd.human_id)
AND (pp.payer_id = pm.payer_id)
ORDER BY 4

我想将其转换为 LINQ 查询。

这是我对 LINQ 的试用：

 return from ep in m_Context.EnchantPayment
                   join hp in m_Context.HumanPayment on hp.human_payment_id equals hp.human_payment_id
                   join pm in m_Context.PaymentMasterEntries on hp.payment_id equals pm.payment_id
                   select new EnchantPaymentDto
                   {
                       practice_id = dd.practice_id,
                       enterprise_id = dd.dd.enterprise_id,
                       enchant_id = dd.enchant_id,
                       human_payment_id = dd.human_payment_id,
                       payment_id = dd.payment_id,
                       human_id =dd.human_id,
                       payer_nbr=pm.payer_nbr,
                       plan_nbr = pm.plan_nbr,
                       group_name = pm.group_name,
                       group_nbr = pm.group_nbr,
                       plan_type = pm.plan_type,
                       payer_exp_date = pm.payer_exp_date
                   };

任何人都可以帮助我吗？我哪里错了？

标签： c#sqlentity-frameworklinqlambda