Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the methods of Linq to sql dynamic query". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the methods of Linq to sql dynamic query"?

1what is the query of object for the use of to sql to sql dynamic query?

We can simply give such an example. We went to the public security station to look for a man. First of all, we will give some characteristics of him, such as height, age, gender, nationality and so on. So, we input some of the characteristics of this person into the computer. We hope that the computer can give us information about this person.

In fact, there are so many people with the same characteristics that they often return a collection. So let's abstract this process into a program. We need new to come up with an object. This object contains the basic information we can know. Then pass this object to Linq To Sql and wait for the result to be returned.

Based on these basic requirements, let's define the following function, which is useful to any entity in order to implement it, we define it as generic. In order not to break the rule of lazy loading of Linq To Sql, we define its return type as IQueryable. As follows:

Public IQueryableFind (TEntity obj) where TEntity: class

The idea is to new an object and then pass it to this function, which we are eager to return a result set that matches this object. To make it relevant to DataContext, we put this function in the partial class of DataContext.

Right-click the Linq To Sql file and select view code. At this point, vs will create a partial class of DataContext for you, with fewer desiger in the middle than the mapping file. Please note that if you want to modify the innuendo file yourself, please put it in this file. In this way, when the innuendo code is refreshed, it will not wash out your own changes. Let's give a general description of our train of thought.

NorthwindDataContext db = new NorthwindDataContext (); / / first new an object Customer c = new Customer (); / / add the most basic information we know, and you can get c.City = "London"; c.Phone = "23236133" from ui; / / call function find returns the result var Q = db.Find (c)

2the principle of dynamic query of Linq to sql

Linq To Sql dynamic query enables users to generate lambda expressions dynamically. The method implemented in this article is reflection plus lambda dynamic expression. Let's first look at how to generate lambda expressions dynamically.

In Linq, the lambda expression is first transformed into Expression Tree, and Expression Tree is not explained in detail in this article. Expression Tree is the result of the transformation of lambda expressions from code to data, which is a more efficient in-memory data structure. For example:

Func f = x = > x + 1; / / Code Expression e = x = > x + 1; / / Data

The second, in fact, is the transformed form. Well, with this premise, we can dynamically construct the Expression Tree.

/ / first construct a ParameterExpression object, where c is the parameter in the Lambda expression. (C = >) ParameterExpression param = Expression.Parameter (typeof (TEntity), "c"); / / the right side of the construction expression, one side of the value Expression right = Expression.Constant (p.GetValue (obj, null)); / / the left side of the construction expression, property end. Expression left = Expression.Property (param, p.Name); / / generate filter expressions. That is, c.CustomerID = = "Tom" Expression filter = Expression.Equal (left, right); / / generate the complete Lambda expression. Expression pred = Expression.Lambda (filter, param); / / here, we are using the and condition. Queryquery = query.Where (pred)

3The role of the reflection of the dynamic query of Linq to sql in this method

Because we use templates, that is, we don't know what property the incoming object will have, so reflection provides a good way here. We can iterate through each property through reflection and treat it as a condition only if it is determined that the value of the property is not null. The complete code for this function is as follows:

Public IQueryable Find (TEntity obj) where TEntity: class {/ / get information about all property PropertyInfo [] properties = obj.GetType (). GetProperties (BindingFlags.Public | BindingFlags.Instance); / / construct the initial query IQueryable query = this.GetTable (). AsQueryable (); / / traverse each property foreach (PropertyInfo p in properties) {if (p! = null) {Type t = p.PropertyType; / / add object,Binary, XDocument, support the mapping of sql_variant,imager and xml, etc. If (t.IsValueType | | t = = typeof (string) | | t = = typeof (System.Byte []) | | t = = typeof (object) | | t = = typeof (System.Xml.Linq.XDocument) | | t = = typeof (System.Data.Linq.Binary)) {/ / conditional if (p.GetValue (obj, null)! = null) {ParameterExpression param = Expression.Parameter (typeof (TEntity), "c"); Expression right = Expression.Constant (p.GetValue (obj, null)) Expression left = Expression.Property (param, p.Name); Expression filter = Expression.Equal (left,right); Expression pred = Expression.Lambda (filter, param); queryquery = query.Where (pred);} return query;}

Test cases and Reflections on dynamic query of 4Personality Linq to sql

Let's test this function with the following example

Customer c = new Customer (); c.City = "London"; c.Phone = "23236133"; var Q = db.Find (c) .ToList ()

The generated sql statement is:

SELECT [t0]. [CustomerID], [t0]. [CompanyName], [t0]. [ContactName], [t0]. [ContactTitle], [t0]. [Address], [t0]. [City], [t0]. [Region], [t0]. [PostalCode], [t0]. [Country], [t0]. [Phone] [t0]. [Fax] FROM [dbo]. [Customers] AS [t0] WHERE ([t0]. [Phone] = @ p0) AND ([t0]. [City] = @ p1)-@ p0: Input NVarChar (Size = 8) Prec = 0; Scale = 0) [23236133]-@ p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

We can see that there are only two conditions, city and phone, in its Linq to sql dynamic query statement. And the relationship between them is and. Our initial vision came true, but is it *? What if it is an or condition? More often, we are using fuzzy queries, so what should we do? This question will be left to the second part.

*, introduces a method of writing log. Stream streams use static to avoid multiple datacontext while using log.txt files.

Partial class DataMappingDataContext {private static StreamWriter sw = new StreamWriter (Path.Combine (Directory.GetCurrentDirectory (), "log.txt"), true); / * / Try to create DataContext with log. / / summary > / / param > public DataMappingDataContext (bool withLog): this () {OnCreated (); if (withLog) {if (sw = = null) {sw = new StreamWriter (Path.Combine (Directory.GetCurrentDirectory (), "log.txt"), true);} this.Log = sw;}} / * / try to close streamwriter / / summary > / / param > protected override void Dispose (bool disposing) {base.Dispose (disposing) Sw.Flush ();}} in the dispose function, flush the output stream. When used, the following using (northwind db = new norhwind (true)) {/ / do something. } Thank you for your reading, the above is the content of "what are the methods of Linq to sql dynamic query". After the study of this article, I believe you have a deeper understanding of what the methods of Linq to sql dynamic query have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.