Question

c# Linq left join 多个条件连接查询

如下面的sql 用查询表达式怎么写？
SELECT A.*,B.ITEM_NAME AS MoneyType_DESC,C.ITEM_NAME AS PayMentType_DESC,D.ITEM_NAME AS ApplyType_DESC
FROM DFZFORMT055 A
LEFT JOIN FM_CODE_LIST B ON A.MoneyType = B.ITEM_VALUE AND B.FORM_KIND = 'DFZ.FORM.T055' AND B.FIELD_NAME= 'MoneyType'
LEFT JOIN FM_CODE_LIST C ON A.PayMentType = C.ITEM_VALUE AND C.FORM_KIND = 'DFZ.FORM.T055' AND C.FIELD_NAME= 'PayMentType'
LEFT JOIN FM_CODE_LIST D ON A.APPLYTYPE = D.ITEM_VALUE AND D.FORM_KIND = 'DFZ.FORM.T055' AND D.FIELD_NAME= 'ApplyType'
WHERE A.form_no=9
自己搞定了，还是要谢谢 w362435819和 fansui1983
var query = from MainTable in T055
join B in (from codelist in CODELIST where codelist.FORM_KIND=="DFZ.FORM.T055" &&codelist.ITEM_NAME=="MoneyType" select codelist)
on MainTable.MoneyType equals B.ITEM_VALUE into B_Join
from BB in B_Join.DefaultIfEmpty()
join C in (from codelist in CODELIST2 where codelist.FORM_KIND == "DFZ.FORM.T055" && codelist.ITEM_NAME == "PayMentType" select codelist)
on MainTable.PayMentType equals C.ITEM_VALUE into C_Join
from CC in C_Join.DefaultIfEmpty()
join D in ....
where MainTable.FORM_NO == m_intFormNo

Answer 1

这代码看的晕啊....
一般 linq 连接用的是 from a in A jion b in B on ....条件
外连接用的是 from a in A jion b in B on ....条件 into C from a in C.DefaultIfEmpty()

Answer 2

from A in DFZFORMT055 join B in FM_CODE_LIST on
A.MoneyType = B.ITEM_VALUE AND B.FORM_KIND = 'DFZ.FORM.T055' AND B.FIELD_NAME= 'MoneyType'
join C in JOIN FM_CODE_LIST on
A.PayMentType = C.ITEM_VALUE AND C.FORM_KIND = 'DFZ.FORM.T055' AND C.FIELD_NAME= 'PayMentType'

join D in FM_CODE_LIST on
A.APPLYTYPE = D.ITEM_VALUE AND D.FORM_KIND = 'DFZ.FORM.T055' AND D.FIELD_NAME= 'ApplyType'

where A.form_no=9
select A

追问

不行，报错。DFZFORMT055为主表，FM_CODE_LIST为下拉菜单的数据存储表，以FORM_KIND,FIELD_NAME两栏位确定是那一下拉菜单的数据，ITEM_NAME,ITEM_VALUE为下拉菜单的值和显示名。
请表栏位:MoneyType ,PayMentType ,APPLYTYPE 为存储拉菜单值，但不一定会存入，所以就用left join 查找主管中的数据及下拉菜单有储存值的数据名。写sql很简单实现，后来改成linq实现就绑手绑脚的，又找不到相关的资料，还请各位高手多多指点。