ASP. net mvc & EF: Intelligent Query construction I. Requirements and Design of Intelligent Query

Reuse

In our daily development process,CodeThe reuse is actually a very important part. The ASP. net mvc framework itself provides us with a lot of good reuse mechanisms, allowing us to make full use of them to save our coding costs.

In simple coding, we can use constructor to reuse code segments. in OOP programming, we can use inheritance polymorphism to reuse classes, we can also use the design pattern to reuse code design between classes or objects.ProgramWe want to construct a better method of reuse, which can be abstracted to a higher level.

 

Application scenarios and objectives

In the information management system, we will develop a large number of list pages, which are similar in functions and generally contain a query condition group and a list.

For example

 

My goal is to encapsulate the query function to achieve the "Automatic query logic can be achieved as long as the conditions on the page are changed" function, that is: if you want to add a query condition, you only need to modify the page without modifying the logic code.

In fact, it is not difficult for an experienced developer to automatically generate query results based on the query conditions on the HTML page. The simplest is to traverse the submitted Query Form and connect it into an SQL statement for query.

However, SQL has many problems.

1. Injection problems. because you do not know the type, you have to spell quotation marks in many places. Even if you use parameter, you cannot handle type conversion problems.
2. The problem of or and coexistence cannot be solved.
3. Other query conditions cannot be processed, such as greater than or less than like

Let's take a look at how EF can be used to compile a query.

We usually spell SQL statements in logic or write the following EF:

 
1:VaR query = dB. User. asqueryable ();
 

 
2: If(String. Isnullorempty (name ))
 

 
3:Query = query. Where (C => C. Name = Name );
 

 
4: If(Id. hasvalue)
 

 
5:Query = query. Where (C => C. ID = ID );
 

6: If(String. Isnullorempty (email ))
 

 
7:Query = query. Where (C => C. Email = Email );
 

 
8: ReturnQuery. tolist ();
 

Then our goal is clear. We want the code to automatically complete the above process and support

1. Recognition type (EF is important)
2. Only query Valid Conditions
3. Processing can be empty type
4. Processing type conversion (for example, converting datetime to unixtime)
5. Some code logic (for example, when querying some dates, the result we actually need is not c => C. time = Time, but C => C. time> time & C. time <time. adddays (1 ))
6. Supports or and other hybrid queries
7. Processing of like
8. Processing of in operations
9. Join Query Processing

Theoretical Process

Let's take a look at the fact that we have done a lot of work, but I have already built a process like this, using ASP. NET MVC and EF features to complete these functions

In fact, the first problem is that when we request from a browser to the server, in addition to the fields to be queried, we should also include how to query (that is, operators such as = <> like in), whether it is or,

The only thing in HTML that can be directly transmitted to the server is the name of the form element. Therefore, we can do some tricks for the name to include the information.

For example, [equal] ID indicates that the value of the attribute ID is equal to the value of this form item.

[Equal] id =1

We can use postdata or querystring to construct

C => C. ID = 1 Lambda expression, and supports multiple conditions

 

 

Actual implementation

I added an extension to helper to implement this function, as shown in figure

1: <Form Action= "" Method= "Post">
 

 
2:Name: @ html. Textbox ("name"). forsearch (querymethod. Like)
 

 
3:Email: @ html. Textbox ("email"). forsearch (querymethod. Equal)<BR />
 

 
4:ID: @ html. Textbox ("ID"). forsearch (querymethod. Equal)
 

 
5:Birthday: @ html. Textbox ("Birthday"). forsearch (querymethod. Equal)<BR />
 

 
6: <Input Type= "Submit" Value= "Query" />
 

 
7: </Form>
 

Here we use the extension forsearch to add the predicate mark [equal] For the HTML name.

We only need to process the data on the server.

 
1: PublicActionresult index (querymodel Model)
 

 
2:{
 

 
3:Using(Var db =NewDbentities ())
 

 
4:{
 

 
5:VaR list = dB. Users. Where (model). tolist ();
 

6:ReturnView (list );
 

 
7:}
 

 
8:
 

 
9:}
 

We have rewritten the process of converting querymodel into a Lambda expression in this where extension method, before that, we also used our own modelbinder to initialize the querymodel before analyzing postdata/querystring.

 

The implementation of these processes will be detailed in several articles in the future.

ASP. net mvc & EF smart query construction II. Model Design and modelbinder