Dbmodel中联合查询

来源：互联网发布：淘宝上图片发不出去编辑：程序博客网时间：2024/04/28 23:39

项目中需要用微软自带的Dbmodel模版引擎。多表联合查询方法：

1，引用命名空间：

using BLToolkit.Data;
using BLToolkit.Data.Linq;
using DbModel;

2，动态添加条件例子：

var awhere = DbModel.PredicateBuilder.True<DbModel.OrderDetail_30>();
            awhere = awhere.And(v=>v.Mark==2);
            var sstr = DbModel.PredicateBuilder.True<DbModel.Seller_30>();
            if (model.SiteAreaType == 0)
            {
                if (!string.IsNullOrEmpty(model.ProvinceID))
                {
                    sstr = sstr.And(p=>p.ProvinceID==model.ProvinceID);
                }
            }

3，语句

var db = new DbModel.Shop30();//30sjop是你数据库的名称
            var query = from a in db.OrderDetail_30.Where(awhere)
                        orderby a.BuyDate descending
                        join b in db.Store_30 on a.StoreID equals b.ID
                        join c in db.Users_30 on a.UsersID equals c.ID
                        join d in db.Seller_30.Where(sstr) on b.SellerID equals d.ID
                        select new { a.OrderNo, a.Price, b.StoreName, b.StoreCommission, a.BuyDate, a.ObjectID, a.ObjectType, a.UsersID, c.FirstRecommandID, c.SecondRecommandID, c.ThirdRecommandID, a.ObjectName };

4，注意：要想用到动态添条件的功能。必须修改sqlext.cs.在dbmodel里

   public static class PredicateBuilder
    {
        public static Expression<Func<T, bool>> True<T>() { return f => true; }
        public static Expression<Func<T, bool>> False<T>() { return f => false; }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                            Expression<Func<T, bool>> expr2)
        {
            var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
            return Expression.Lambda<Func<T, bool>>
                  (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                             Expression<Func<T, bool>> expr2)
        {
            var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
            return Expression.Lambda<Func<T, bool>>
                  (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
        }
    }

。如果要包含的话；

   [SqlExpression(" CONTAINS({0},{1}) ", ServerSideOnly = true)]
        public static bool FtContains(string s1, string s2) { throw new NotImplementedException(); }