linq to entity没有外键的多表查询


今天某个界面时出现了多表查询(而且表之间没有外键),当然带外键的可能好处理些了,可以参照网上一篇不错的文章

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希望大侠们指导啊!


阅读更多
个人分类: linq
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

linq to entity没有外键的多表查询

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭