Linq 动态查询

1.linq动态查询就是采用表达式树来完成：

    表达式树表示树状数据结构的代码，树状结构中的每个节点都是一个表达式

    System.Linq.Expressions命名空间下含有很多类来表示不同的表达式，

    这些类都继承自抽象的Expression基  类，Expression含有丰富的静态方法用于创建各种各样的表达式类

注意：并不是所有的lambda表达式都能转换成表达式树，只有单一表达式的lambda表达式才可以转化为表达式树，

      而且表达式 中不能包含赋值

  select动态查询示例：

 using(DBContext db  =new DBContext ()){
           // 根据iqueryable数据源构造一个查询
           IQueryable<Books>  customerbooks=db.Books;
           //组建一个表达式数来创建一个参数
           ParameterExpression parameterExpress = Expression.Parameter(typeof(Books), "book");
           //组建表达树
           Expression seBookName = Expression.Property(parameterExpress, "BookName");
           Expression lamdba = Expression.Lambda(seBookName, parameterExpress);
           Expression express =Expression.Call(typeof(Queryable),"Select",

               new Type[]{typeof(Books),typeof(string)},
               Expression.Constant(customerbooks),lamdba);
           IQueryable<string> query = db.Books.AsQueryable().Provider.CreateQuery<string>(express);
           //输出执行sql语句
           System.Data.Common.DbCommand cmd = db.GetCommand(query);
           Page.ClientScript.RegisterStartupScript(this.GetType(), "info", cmd.CommandText);
       }

sql：

 SELECT [t0].[bookName] FROM [dbo].[books] AS [t0]

 where 条件的示例查询：

using (DBContext db  =new DBContext ()){
            IQueryable<Books> customerbooks = db.Books;
            //组建一个表达式树的参数
            ParameterExpression parameterExpress = Expression.Parameter(typeof(Books), "book");
            //组建一个表达式树
            Expression bookname = Expression.Property(parameterExpress,      typeof(Books).GetProperty("BookName"));
            Expression newbookname = Expression.Constant("C#高级编程");
            Expression equalsbookname = Expression.Equal(bookname, newbookname);
            Expression expresslamdba = Expression.Lambda(equalsbookname, parameterExpress);
            Expression exp = Expression.Call(typeof(Queryable), "Where", new Type[] { typeof(Books) }, Expression.Constant(customerbooks), expresslamdba);
            //生成查询
            IQueryable<Books> query = db.Books.AsQueryable().Provider.CreateQuery<Books>(exp);
            //获取动态sql语句 并且输出
            System.Data.Common.DbCommand cmd = db.GetCommand(query);
            Page.ClientScript.RegisterStartupScript(this.GetType(), "info", cmd.CommandText);

        }

sql:

SELECT [t0].[bookId] AS [BookId], [t0].[bookName] AS [BookName], [t0].[bookPublicationDate] AS [BookPublicationDate], [t0].[bookPrice] AS [BookPrice], [t0].[bookAuthorId] AS [BookAuthorId] FROM [dbo].[books] AS [t0] WHERE [t0].[bookName] = @p0

orderby动态查询示例：

using(DBContext db  =new DBContext ()){
            //构造动态数据
            IQueryable<Books> customerbooks = db.Books;
            //构造表达式树的参数
            ParameterExpression parameterExpress = Expression.Parameter(typeof(Books), "book");
            Expression expresswhere = Expression.Equal(Expression.Property(parameterExpress, "BookName"), Expression.Constant("C#高级编程"));
            Expression exlamdba = Expression.Lambda(expresswhere, parameterExpress);
            MethodCallExpression MethodCallwhere = Expression.Call(typeof(Queryable), "Where", new Type[] { typeof(Books) }, Expression.Constant(customerbooks), exlamdba);
            MethodCallExpression MethodCallorderby = Expression.Call(typeof(Queryable), "OrderBy", new Type[] { typeof(Books), typeof(string) },
                MethodCallwhere,Expression.Lambda(Expression.Property(parameterExpress, "BookName"),parameterExpress));
            //生成动态查询
            IQueryable<Books> query = db.Books.AsQueryable().Provider.CreateQuery<Books>(MethodCallorderby);
            //获取动态sql语句 并且输出
            System.Data.Common.DbCommand cmd = db.GetCommand(query);
            Page.ClientScript.RegisterStartupScript(this.GetType(), "info", cmd.CommandText);

        }

sql:

SELECT [t0].[bookId] AS [BookId], [t0].[bookName] AS [BookName], [t0].[bookPublicationDate] AS [BookPublicationDate], [t0].[bookPrice] AS [BookPrice], [t0].[bookAuthorId] AS [BookAuthorId] FROM [dbo].[books] AS [t0] WHERE [t0].[bookName] = @p0 ORDER BY [t0].[bookName]

2：比较好我感觉还是用dynamic这个插件：

    如果用这个查询：上面的查询就变的清楚简单：

    using(DBContext db  =new DBContext ()){
            var query = db.Books.Where("BookName==@0 and  BookPrice== @1", "C#高级编程", 102)
                .OrderBy("BookName")
                .Select("New(BookName,BookPrice,BookPublicationDate)"); //里面可以用as 来重新命名列名
            //获取动态sql语句 并且输出
            System.Data.Common.DbCommand cmd = db.GetCommand(query);
            Page.ClientScript.RegisterStartupScript(this.GetType(), "info", cmd.CommandText);   
        }

sql:

SELECT [t0].[bookName] AS [BookName], [t0].[bookPrice] AS [BookPrice], [t0].[bookPublicationDate] AS [BookPublicationDate] FROM [dbo].[books] AS [t0] WHERE ([t0].[bookName] = @p0) AND ([t0].[bookPrice] = @p1) ORDER BY [t0].[bookName]