LinqToSql 动态查询条件,join, isnull 综合示例

LinqToSql 出来好久了. 一直没怎么用过这种写法.. 最近项目既要支持orcale 又要支持SqlServer.
涉及到跨数据库, 就尽量不要执行原生的SQL, EF默认对join也是支持的, 就是不太好写.
选来选去,还是选择了使用 LinqToSql的形式实现 join .

  var hisFeeItemFiltered = DB.Set<HisFeeItem>().AsQueryable();

             if (! string.IsNullOrWhiteSpace(filter))
             {
             //动态添加查询条件
                hisFeeItemFiltered = hisFeeItemFiltered.Where(a=>a.HItemName.Contains(filter) || a.NameAB.Contains(filter));
             }


             var data = 
                (
                from a in hisFeeItemFiltered
                join b in DB.Set<WHisFeeItemFavorite>().Where(a=>a.HospitalCode == HospitalCode && a.UserID == user ) on a.Code equals b.FeeItemCode 
                //into temp 
                //from t in temp 
                orderby b.FavoriteNum descending 
                select new
                { 
                    FavoriteNum = (b.FavoriteNum == null ? 0 : b.FavoriteNum ),  //isnull 示例
                    a.Code,
                    a.CreateDate,
                    a.Creator,
                    a.Description,
                    a.HItemCode,
                    a.HItemName,
                    a.Id,
                }
                ).ToList();

下面是改成left join 的写法..

             var hisFeeItemFiltered = DB.Set<HisFeeItem>().AsQueryable();

             if (! string.IsNullOrWhiteSpace(filter))
             {
                hisFeeItemFiltered = hisFeeItemFiltered.Where(a=>a.HItemName.Contains(filter) || a.NameAB.Contains(filter));
             }


             var data = 
                (
                from a in hisFeeItemFiltered
                join b in DB.Set<WHisFeeItemFavorite>().Where(a=>a.HospitalCode == HospitalCode && a.UserID == user )  on a.Code equals b.FeeItemCode 
                into temp  //left join 必须建立在这个表上
                from t in temp.DefaultIfEmpty()//left join  
                orderby t.FavoriteNum descending 
                select new
                { 
                    FavoriteNum = (t.FavoriteNum == null ? 0 : t.FavoriteNum ), 
                    a.Code,
                    a.CreateDate,
                    a.Creator,
                    a.Description,
                    a.HItemCode,
                    a.HItemName,
                    a.Id,
                }
                ).ToList();