数据来自3张表,contract_list为主表,相关字段内容为编码,显示在Table中相关编码字段需要转换成相应的编码说明,org为在数据库中建立的视图(来自同实例下的另一数据库),cti为一字典表
(来自同实例下的另一数据库)的视图,存放各类编码及其说明。
实现一:
var query = from u in data.contract_list
join or in data.org on u.contractor equals or.org_id
join st in data.cti.Where(r => r.code_table == "WTS").Select(r => r) on u.po_status equals st.code_table_item
join loc in data.cti.Where(r => r.code_table == "YN").Select(r => r) on u.is_lock equals loc.code_table_item
where u.pms_id == pms_id
orderby u.po_no
select new
{
u.po_no,
u.po_name,
contractor = or.org_name,
po_status = st.item_name,
is_lock = loc.item_name
};
foreach (var u in query)
{
lhpms_contract_list_simple cls = new lhpms_contract_list_simple();
cls.po_no = u.po_no;
cls.po_name = u.po_name;
cls.contractor = u.contractor;
cls.po_status = u.po_status;
cls.is_lock = u.is_lock;
clsl.Add(cls);
}
join or in data.org on u.contractor equals or.org_id
join st in data.cti.Where(r => r.code_table == "WTS").Select(r => r) on u.po_status equals st.code_table_item
join loc in data.cti.Where(r => r.code_table == "YN").Select(r => r) on u.is_lock equals loc.code_table_item
where u.pms_id == pms_id
orderby u.po_no
select new
{
u.po_no,
u.po_name,
contractor = or.org_name,
po_status = st.item_name,
is_lock = loc.item_name
};
foreach (var u in query)
{
lhpms_contract_list_simple cls = new lhpms_contract_list_simple();
cls.po_no = u.po_no;
cls.po_name = u.po_name;
cls.contractor = u.contractor;
cls.po_status = u.po_status;
cls.is_lock = u.is_lock;
clsl.Add(cls);
}
可以实现程序目的,但主表与子表为多约束条件,子表中的
code_table字段约束与主表中的字段无关,当主表与子表的约束条件不成立时,无法检索到该条记录,这一点不能满足要求。
实现二:
var query = (from u in data.contract_list
join or in data.org on u.contractor equals or.org_id
let st= data.cti.Where(r => r.code_table == "WTS"&&r.code_table_item== u.po_status).Select(r => r.item_name).FirstOrDefault()
let loc= data.cti.Where(r => r.code_table == "YN"&&r.code_table_item== u.is_lock).Select(r=>r.item_name).FirstOrDefault()
orderby u.po_no
select new
{
u.po_no,
u.po_name,
contractor = or.org_name,
po_status = st,
is_lock = loc
}).ToList();
int num = 0;
join or in data.org on u.contractor equals or.org_id
let st= data.cti.Where(r => r.code_table == "WTS"&&r.code_table_item== u.po_status).Select(r => r.item_name).FirstOrDefault()
let loc= data.cti.Where(r => r.code_table == "YN"&&r.code_table_item== u.is_lock).Select(r=>r.item_name).FirstOrDefault()
orderby u.po_no
select new
{
u.po_no,
u.po_name,
contractor = or.org_name,
po_status = st,
is_lock = loc
}).ToList();
int num = 0;
foreach (var u in query)
{
lhpms_contract_list_simple cls = new lhpms_contract_list_simple();
num = num+1;
cls.serial= num;
cls.po_no = u.po_no;
cls.po_name = u.po_name;
cls.contractor = u.contractor;
cls.po_status = u.po_status;
cls.is_lock = u.is_lock;
clsl.Add(cls);
}
{
lhpms_contract_list_simple cls = new lhpms_contract_list_simple();
num = num+1;
cls.serial= num;
cls.po_no = u.po_no;
cls.po_name = u.po_name;
cls.contractor = u.contractor;
cls.po_status = u.po_status;
cls.is_lock = u.is_lock;
clsl.Add(cls);
}
把多表多条件查询变为子查询,用let关键字存为中间变量,就灵活多了,较好地实现了多表子查询。