Linq多表查询

数据来自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);
                    }

可以实现程序目的，但主表与子表为多约束条件，子表中的 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;

                    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);
                    }

把多表多条件查询变为子查询，用let关键字存为中间变量，就灵活多了，较好地实现了多表子查询。