今天某个界面时出现了多表查询(而且表之间没有外键),当然带外键的可能好处理些了,可以参照网上一篇不错的文章
http://www.dotblogs.com.tw/asdtey/archive/2009/10/08/10946.aspx
表之间没有外键:直接看代码吧,你明白的.............
类似的sql 语句如下(主要是查询order表,其中附加有users表和enterprise表信息):
SELECT o.id,o.enterpriseID,o.`code`,o.orderState,o.dueAmount,o.paidAmount,o.remarks,o.needInvoice,o.createdBy,o.createdUserID,o.createdTime,o.auditedUserID,
o.auditedTime,o.lastModifiedUserID,o.lastModifiedTime,e.enterpriseFlag,e.enterpriseName,users.UserName FROM orders o join enterpriseinfos e on o.enterpriseID=e.enterpriseID JOIN users on users.UserID=o.createdUserID WHERE e.enterpriseFlag='lt' and o.dueAmount='10'
用linq to entity有3中方式(当然这是我尝试之后的,我是个EF的新手,可能还有其他的方法)
第一种:是先查出enterpriseinfos表中的信息在和其他的关联
private List<OrderInfo> QueryOrders(csdbEntities entities, string condition, List<ObjectParameter> paras, string conditionE, List<ObjectParameter> parasE)
{
var enList = entities.enterpriseinfos.Where(conditionE, parasE.ToArray());
var query = (from record in entities.orders.Where(condition, paras.ToArray())
join data in enList
on record.enterpriseID equals data.enterpriseid
into datas
from e in datas
join user in entities.users
on record.createdUserID equals user.UserID
into userss
from u in userss.DefaultIfEmpty()
orderby record.code
select new OrderInfo
{
.............
}).ToList();
return query;
}
第二种:
是直接在整体预设语句上跟上enterpriseinfos表查询条件
private List<OrderInfo> QueryOrders(csdbEntities entities, string condition, List<ObjectParameter> paras, string enterpriseFlage, string enterpriseName)
{
var query = (from record in entities.orders.Where(condition, paras.ToArray())
join data in entities.enterpriseinfos.Where(conditionE, parasE.ToArray())
on record.enterpriseID equals data.enterpriseid
into datas
from e in datas
join user in entities.users
on record.createdUserID equals user.UserID
into userss
from u in userss.DefaultIfEmpty()
orderby record.code
select new OrderInfo
{
..................
}).ToList();
return query;
}
当然以上两种都是可以在调用时动态组件查询条件的(如参数),这两种的灵活度比较大,缺点是分别要动态组件两种查询条件
第三种:指定具体的附加查询变量
private List<OrderInfo> QueryOrders(csdbEntities entities, string condition, List<ObjectParameter> paras, string enterpriseFlage, string enterpriseName)
{
var query = (from record in entities.orders.Where(condition, paras.ToArray())
join data in entities.enterpriseinfos//.Where(conditionE, parasE.ToArray())
on record.enterpriseID equals data.enterpriseid
into datas
from e in datas
where e.enterpriseFlag.Contains(enterpriseFlage) && e.enterpriseName.Contains(enterpriseName)
join user in entities.users
on record.createdUserID equals user.UserID
into userss
from u in userss.DefaultIfEmpty()
orderby record.code
select new OrderInfo
{
.......................
}).ToList();
return query;
}
但是这种方法不用之前动态组件代码,但是缺点是只能针对比较少的附加条件
这几种方法你可以灵活选择,可能还有其他更好的方法,因为我也是刚接触linq to entity希望大侠们指导啊!