C # LAMBDA expression generates SQL query statement

Due to some historical reasons, leading to the company's existing projects in the database there are a large number of Chinese table names, the text segment name, and the way to operate the database or SQL statement stitching + ADO, of course, the way to operate the database a little problem, but the most I can not accept is the SQL statement splicing, Because the database in a large number of Chinese table names, the reason for the name of the field, resulting in an open related code, a dense mass of Chinese characters, really spicy eyes, in order to solve this problem, wrote the Tqueryhelper help class.

The main function of Tqueryhelper is to avoid the appearance of Chinese in the splicing SQL statement, so my solution is: Chinese table name, the middle of the text field name, you can use the attribute (Attribute) to the entity class with attribute one by one mapping, query statements through the LAMBDA expression block generation, and then the use of StringBuilder stitching, because it is improved on existing projects, so linqtosql and EF are not suitable, because both linqtosql and EF need to connect to the database to create the corresponding entity class, which is not in line with the requirements, and the network did not find the expected class library, so you want to build, It is necessary to note that the LAMBDA expression generates the SQL reference for this article, "The Expression tree (ii) Traversal expression tree takeoff (Jesse)", in particular thanks to the author.

Described above is the preparation of tqueryhelper motivation and process, the purpose of this article is to hope that the help of the class can help others, avoid others to create the wheel, in addition, due to the limited level of this article, but also hope that we can give good advice, let me improve, and if there are ready-made class library, I can directly use the Mature class library, so also avoid myself continue detours.

The following describes the way Tqueryhelper generates SQL statements, additions and deletions are included, but the following only describes the query, we first define two entity classes:

[Databaset ("Article table")]public class topicmodel{public    int Identityid {get; set;}    [Databaset ("title")]    public string Title {get; set;}    [Databaset ("category")]    public string TypeCode {get; set;}} [Databaset ("Article Category table")]public class topictypemodel{public    int Identityid {get; set;}    [Databaset ("number")]    public string TypeCode {get; set;}    [Databaset ("name")]    public string TypeName {get; set;}}

The single-table simple query code is as follows:

String querysql = new Tqueryhelper<topicmodel> (). Query ()                  . Tosql ();//The resulting SQL statement is as follows: SELECT [Article Table].* from [Article table] with (NOLOCK)

Query the first 10 data, and only query the Identityid and header field code as follows:

String querysql = new Tqueryhelper<topicmodel> (). Query (Ten)                  . Select (p = = new {ID = P.identityid, p.title})                  . Tosql ();//The resulting SQL statement is as follows: Select top 10 [Article table]. [Identityid] as id,[article table]. [title] from [Article table] with (NOLOCK)

A more complex single-table query, including the Where statement, the order BY statement, and the code for the Group by statement are as follows:

String querysql = new Tqueryhelper<topicmodel> (). Query ()                  . Select (p = = new {ID = P.identityid, p.title})                  . COUNT (p = = P.identityid, "Count")                  . Where (p = = P.identityid = 1)                  . Orderdesc (p = P.identityid)                  . Group (p = new {P.identityid, p.title})                  . Tosql ();//The resulting SQL statement is as follows: SELECT [Article Table]. [Identityid] as id,[article table]. [Title],count ([Article Table].[ Identityid]) as [Count] from [Article table] with (NOLOCK) where [Article table]. [Identityid] = 1 GROUP by [Article table]. [identityid],[article table]. [title] Order BY [Article table]. [Identityid] Desc

The following is a look at the multi-table query, simple two-table inline connection query code is as follows:

String querysql = new Tqueryhelper<topicmodel> (). Query ()                  . Innerjoin<topictypemodel> (p, y) = = P.identityid = = Y.identityid)                  . Tosql ();//generated SQL statements are as follows: SELECT [Article Table].*, [Article Category table].* from [Article table] with (NOLOCK) inner join [Article category table] with (NOLOCK) on [Article table]. [identityid]=[article category table]. [Identityid]

A more complex two-table inline query code is as follows:

String querysql = new tqueryhelper<topicmodel> ()                  . Query ()                  . Select (p = = new {ID = P.identityid, p.title})                  . Select<topictypemodel> (p = new {TypeID = P.identityid, P.typecode, p.typename})                  . Innerjoin<topictypemodel> (p, y) = = P.typecode = = Y.typecode)                  . Where (p = = P.typecode = = "Life" && P.identityid <=)                  . Order (p = = P.identityid)                  . Tosql ();//The resulting SQL statement is as follows: SELECT [Article Table]. [Identityid] as id,[article table]. [Title],[article category table]. [Identityid] as typeid,[article category table]. [Ref.],[article category table]. [Name] from [Article table] with (NOLOCK) inner join [Article category table] with (NOLOCK) on [Article table]. [Category]=[article category table]. [number] where [Article table]. [Category] = ' life ' and [Article table]. [Identityid] <= ORDER BY [Article table]. [Identityid] ASC

Tqueryhelper supports any table inline connection/LEFT JOIN/Right connection query, Tqueryhelper also supports federated queries and subqueries, and the Code for simple Union queries is as follows:

String querysql = new tqueryhelper<topicmodel> ()                  . Query ()                  . Union<topicmodel> (                      new tqueryhelper<topicmodel> (). Query ()                  )                  . Tosql ();//The resulting SQL statement is as follows: SELECT [Article Table].* from [Article table] with (NOLOCK) union (SELECT [Article Table].* from [Article table] with (NOLOCK))

Complex joint query code is still supported, through the code can also be seen, joint queries have no effect on each other, so the logic can be relatively complex, sub-query processing method is similar to a federated query, the from in subquery code is as follows:

String querysql = new tqueryhelper<topicmodel> ()                  . Query ()                  . Select ()                  . Fromin<topicmodel> (                      new tqueryhelper<topicmodel> ()                      . Query ()                  )                  . Tosql ();//The resulting SQL statement is as follows: SELECT [Article Table].* from (SELECT [Article Table].* from [Article table] with (NOLOCK)) as [Article table]

Currently only the Where in support is provided, the code is as follows:

String querysql = new tqueryhelper<topicmodel> ()                  . Query ()                  . Select ()                  . Where (p = = P.identityid = 1)                  . Wherein<topictypemodel> (                      p = p.typecode,                      new tqueryhelper<topictypemodel> ()                      . Distinct ()                      . Select (p = = new {TypeCode = P.typecode}                  )                  . Tosql ();//The resulting SQL statement is as follows: SELECT [Article Table].* from [Article table] with (NOLOCK) where [Article table]. [Identityid] = 1 and [Article table]. [Category] In (select DISTINCT [Article category table].[ Number] from [Article Category table] with (NOLOCK))

At this point, Tqueryhelper's main functions are described, tqueryhelper support most of the logic implementation of SQL splicing, of course, also supports the generation of parameters (@) SQL statements, not listed here, it should be explained that, although support CacheKey (is based on CacheKey the query is cached), but it is still impossible to avoid the argument passing of the expression, because the test found, for example, the following code:

private void X<t> (expression<func<t, object>> Lambda) {}for (int index = 0; index < 10000; index + +) {    x<topicmodel> (p = = new {P.identityid, p.title});}

The above code, the X function does not do anything, but the test found that it needs to consume about 130 milliseconds, here is really no way to optimize, so the call generated once the SQL should be based on the KEY value in a timely manner cache, so the next call can be directly from the cache to take out the spliced SQL statements, And no longer need to generate SQL statements through Tqueryhelper, and finally, I would like to see the friends of this article to put forward valuable suggestions, or to give a mature class library link, I am grateful not to enter. Source: https://pan.baidu.com/s/1jHG3mSm Password: YTM5

C # LAMBDA expression generates SQL query statement