Definition and practice of a common Query Language

Recently, we found that users may be asked to build their own Query expressions in the project. For example, you need to enter a set of Query expressions with logical relationships through a configurable interface, and then filter and return the required data based on the query expression. This type of User case is actually very common. Inspired by this, we may be able to define a general query-oriented domain-specific language (DSL) for serialization and Dynamic Construction of queries.

Overview

As a result, I released an open-source project called uniied queries (uq). uq defines a DSL to describe a specific query structure. It also provides the ability to convert query specification into SQL where clauses and lambda expressions. Uq provides a flexible framework design that allows you to easily implement the iqueryspecificationcompiler interface or inherit the queryspecificationcompiler <t> abstract class to customize the Conversion Function of the query conventions.

DSL Structure Definition

The following XSD Schema defines the DSL semantics of uq. Note that it contains a set of recursive hierarchies:

Example

Assume that in queryspecificationsample. the following query conventions are defined in the XML file. When the query conventions are executed, the system returns all the query conventions whose names start with "Peter" and whose surnames do not contain the "r" character, and customers with an annual income of more than 30000.

<?xml version="1.0" encoding="utf-8"?><QuerySpecification>  <LogicalOperation Operator="And">    <Expression Name="FirstName" Type="String" Operator="StartsWith" Value="Peter"/>    <UnaryLogicalOperation Operator="Not">      <LogicalOperation Operator="Or">        <Expression Name="LastName" Type="String" Operator="Contains" Value="r"/>        <Expression Name="YearlyIncome" Type="Decimal" Operator="LessThanOrEqualTo" Value="30000"/>      </LogicalOperation>    </UnaryLogicalOperation>  </LogicalOperation></QuerySpecification>

The following C # code generates the SQL WHERE clause based on the XML file:

static void Main(string[] args){    var querySpecification = QuerySpecification.LoadFromFile("QuerySpecificationSample.xml");    var compiler = new SqlWhereClauseCompiler();    Console.WriteLine(compiler.Compile(querySpecification));}

The SQL WHERE clause is as follows:

((FirstName LIKE ‘Peter%‘) AND (NOT ((LastName LIKE ‘%r%‘) OR (YearlyIncome <= 30000))))

However, in many cases, developers of ADO. Net prefer to use dbparameter to specify the parameter values contained in the query, rather than simply concatenating the parameters in SQL statements. The uq sample can generate an SQL WHERE clause with a parameter list. To achieve this, you only need to set the constructor parameter to true when initializing sqlwhereclausecompiler:

var compiler = new SqlWhereClauseCompiler(true);

The SQL WHERE clause is:

((FirstName LIKE @fvP8gN) AND (NOT ((LastName LIKE @ESzoyd) OR (YearlyIncome <= @fG5Z7e))))

The parameter value can be obtained through the parametervalues attribute of sqlwhereclausecompiler.

In fact, the SQL where clause generated by sqlwhereclausecompiler meets the requirements of Microsoft SQL Server. If you want to generate a where clause that complies with the oracle or MySQL syntax, you can expand the sqlwhereclausecompiler class by yourself.

Next, the following C # code can compile the query conventions defined in the preceding XML file into lambda expressions:

static void Main(string[] args){    var querySpecification = QuerySpecification.LoadFromFile("QuerySpecificationSample.xml");    var compiler = new LambdaExpressionCompiler<Customer>();    Console.WriteLine(compiler.Compile(querySpecification));}

The generated Lambda expression is as follows:

p => (p.FirstName.StartsWith("Peter") AndAlso Not((p.LastName.Contains("r") OrElse (p.YearlyIncome <= 30000))))

The following C # example describes in detail how to apply the query conventions on a group of customer objects and return the customer data that meets the conditions:

private static Customer[] GetAllCustomers(){    return new[]               {                   new Customer { FirstName = "Sunny", LastName = "Chen", YearlyIncome = 10000 },                   new Customer { FirstName = "PeterJam", LastName = "Yo", YearlyIncome = 10000 },                   new Customer { FirstName = "PeterR", LastName = "Ko", YearlyIncome = 50000 },                   new Customer { FirstName = "FPeter", LastName = "Law", YearlyIncome = 70000 },                   new Customer { FirstName = "Jim", LastName = "Peter", YearlyIncome = 30000 }               };}static void Main(string[] args){    var querySpecification = QuerySpecification.LoadFromFile("QuerySpecificationSample.xml");    var compiler = new LambdaExpressionCompiler<Customer>();    var customers = GetAllCustomers();    foreach (var customer in customers.Where(compiler.Compile(querySpecification).Compile()))    {        Console.WriteLine(            "FirstName: {0}, LastName: {1}, YearlyIncome: {2}",            customer.FirstName,            customer.LastName,            customer.YearlyIncome);    }}

Summary

Now we have a DSL definition of the query structure, which enables a query protocol to be stored in the memory object or persisted to the external storage system, for example, in an XML file or in a database. Next we can design a general interface through which we can design a query protocol, you can use compiler to convert the query specification into another form that can be accepted by existing systems. Furthermore, we can design a series of builders to convert SQL where clauses or lambda expressions to query conventions in uq.

I hope this small project will inspire and help you.