Rafy framework-Use Sqltree query

This article describes how to use SQL Tree queries in the Rafy framework:

In addition to the LINQ queries commonly used by developers, the RAFY framework provides a way to query with SQL syntax trees.

In this way, developers do not need to write real SQL statements directly, but instead use a set of intermediate SQL syntax tree objects. This isolates the coupling to the specific database, allowing developers to write queries that can be run across a variety of different databases, even in non-relational databases. At the same time, the framework combines managed properties, provides APIs that are easy for developers to use, and tries to keep syntax similar to traditional SQL so that developers can quickly understand and write.

This article contains the following chapters:

• Quick Example
• Usage Scenarios
• Code snippet
• More examples

Quick Example

Sqltree queries are query patterns that are directly in a format similar to SQL syntax and that combine entity managed property imanagedproperty to query. As follows:

[repositoryquery]public  virtual     Chapterlist Getby (string  name, Paginginfo pi) {var f = queryfactory.instance;    var t = f.table<chapter> (); var q = f.query (Selection:f.selectall (), //query all columns  from:t,//to query the real The body of the table  where : T.column (Chapter.nameproperty). Contains (name) //where condition,  by: new  list<iorderby> {
   
    //sort  f.orderby (source.    Column (Chapter.nameproperty), orderdirection.ascending)}); 
    return  (chapterlist) 
    this . Querydata (q, PI);} 
   

As you can see, the sqltree syntax is very simple:

• An entire Sqltree query object is defined by a singleton object of type Queryfactory.instance.
• The query uses the entity type (Chapter) and the entity's managed properties (Chapter.nameproperty) to define the tables and fields.

For more examples of query syntax, see more examples later in this section.

Usage Scenarios

You need to use the Sqltree query when you are in the following scenarios:

2. Some scenarios that LINQ queries cannot support.
   LINQ queries currently only support the parsing of a limited number of operators, as well as the analysis of less complex relationships. So when your queries are more complex and cannot be implemented using LINQ queries, consider using Sqltree queries.
4. SQL statements need to be controlled more precisely.
   You also need to use Sqltree if you want to control the resulting SQL statements more precisely.
   For example, a LINQ query requires that two entities have an exact entity relationship in order to eventually generate a join statement, but the sqltree is no different than the SQL statement, and the developer is free to Join the table of two entities.
6. Better performance is required.
   The Sqltree query is the core implementation of the RAFY framework query data (tables, entities). At the bottom of the framework, LINQ queries are also entirely based on sqltree queries. When a developer uses a Linq query, the compiler actually generates a set of objects to represent an expression tree, and the Rafy framework parses the tree to generate a more underlying Sqltree object before it is handed to the execution engine to generate the actual SQL statement and ultimately execute it. Therefore, the direct use of Sqltree saves the generation of expression trees (large amounts of reflection and objects) and the performance cost of parsing.
   Similarly, Rafy does not have a new set of string-based query syntax like the Hibernate framework (such as HQL), but also because developers write hql, not only can not get compile-time syntax support, but also the performance needs to consume HQL parsing and generating Sqltree, It is better to use direct sqltree directly.
   Of course, rafy on the basis of sqltree the reason for the introduction of LINQ query, because sqltree itself needs a certain period of learning to use, and developers are more familiar with the LINQ syntax to query, basically can be considered to be ready-to-use, so support LINQ query can simplify most of the simple Single development scenario.
8. You want to write a more generic query.
   The GetAll, GetById and other methods in the warehouse base class entityrepository are very common queries for all entity types. For developers of Rafy-based upper-level frameworks, there are many times when you need to write similar generic queries on your own, in addition to using their own generic queries directly.
   The attribute expressions (e.name) in the LABMDA syntax of Linq need to bind specific entity types (book e), which leads to the need to use reflection to generate expression trees in order to write cleaning rod ants. However, Sqltree's syntax is based on the Managed property framework, which does not require the use of an exact entity property expression, only the run-time object Imanagedproperty (Book.nameproperty) that uses the managed property. This makes it easier for developers to write generic queries. For example, all query methods in the warehouse base class Entityrepository are implemented directly by using the entity's managed properties, for example: GetById, Getbyparentid, GetAll, and so on.
10. You can write queries for extended properties.
    Because extended properties are written in the additional assembly plug-in, the query cannot be made through a Linq expression. At this point, you have to define Sqltree to complete the query through the managed property Imanagedproperty.
    For extended properties, see: Extended Properties.
12. Supports multiple databases.
    In the above scenario, you can actually write SQL statements directly to query. However, it is difficult to ensure that the SQL statements written by the developer can run correctly on multiple databases.
14. The query needs an extensibility point that supports the warehouse data tier.
    Since Rafy's query cores are all based on sqltree, all internal extensibility points are dependent on sqltree. If developers write SQL statements directly to query, then many of these extension points will not be valid and cannot be extended to this SQL statement written by the developer.
    For example, when the ghost plugin is used to automatically filter all ghost data, if the developer uses the manually written SQL syntax to query, then the automatic filtering function is not valid, it is necessary for the developer to filter the ghost data.

Code snippet

RAFYSDK provides two code snippets to assist developers in generating basic SQLTREE query structures: rafy_query, rafy_query_tablequerycontent.

For details, see Code Snippet.

More examples

Some examples of common sqltree queries are listed below. With this code, you'll learn how to use Sqltree with a variety of query requirements.

Basic query:

[repositoryquery]public  virtual     Chapterlist Getby (string  name, Paginginfo pi) {var f = queryfactory.instance;    var t = f.table<chapter> ();  var q = f.query (//selection:f.selectall (),//No selection, default means query all columns  From:t,//the table of the entity to be queried  where : T.column (Chapter.nameproperty).    Contains (name) //where condition ); return  (chapterlist) this . Querydata (q, PI);} 

Tabular data query:

[Repositoryquery]  Public Virtual Litedatatable Getby (string name, Paginginfo pi) {    var f = queryfactory.instance;    var t = f.table<chapter> ();    var q = f.query (        from:t,        where: T.column (Chapter.nameproperty). Contains (name)    );    return this. QueryTable (q, PI); //By querying the entity into a query data table, just replace this line of code. }


Compare the conditions of the two columns:

var table = f.table (this); //Use the current warehouse to represent the current table var q = f.query (    from:table,    where: table. Column (Chapter.nameproperty). Equal (table. Column (Chapter.codeproperty))//two columns equal );


Use and, or:

var table = f.table (this); var q = f.query (    from:table,    where: F.and (        table). Column (Chapter.nameproperty). Equal (name),        f.or (            table. Column (Chapter.idproperty). Lessequal (Ten),            table. Column (Chapter.idproperty). Greaterequal ())        )    ;

Join (real code in Serialnumbervaluerepository):

// <summary>/// Gets the most recent value under a rule. // </summary>/// <param name= "Autocodename" ></param>// <returns></returns>[Repositoryquery] Public VirtualSerialnumbervalue Getlastvalue (stringAutocodename) {var f = queryfactory.instance;    var t = f.table<serialnumbervalue> ();    var t2 = f.table<serialnumberinfo> (); var q = f.query (From:t.join (T2),//Because Serialnumbervalue has a serialnumberinfo reference property, you do not need to give the join condition when using join.         where: T2. Column (Serialnumberinfo.nameproperty). Equal (Autocodename), by:Newlist<iorderby> {F.orderby (T.column (Serialnumbervalue.lastupdatedtimeproperty), OrderDirection.Descending)} );return(Serialnumbervalue) This. Querydata (q);}

Use the full Join:

var t = f.table<serialnumbervalue> (), var t2 = f.table<serialnumberinfo> (), var q = f.query (    from:t.join ( T2, T.column (serialnumbervalue.serialnumberinfoidproperty). Equal (T2. Column (Serialnumberinfo.idproperty)), Jointype.inner),//Can not only give a specific join condition, but also give the join type.     where: T2. Column (Serialnumberinfo.nameproperty). Equal (autocodename),    new list<iorderby> {F.orderby (T.column ( Serialnumbervalue.lastupdatedtimeproperty), orderdirection.descending)});

Exists:

var booktable = f.table (this ); var chaptertable = f.table< Chapter> (); var q = f.query (from:booktable, where : F.exists (F.query (From:chap Tertable, where : Chaptertable.column (Chapter.bookidproperty). Equal (Booktable.idcolumn))); 

Not Exists:

var book = f.table (this ); var chapter = f.table<chapter> (); var q = f.query (From:book, where : F.not (f.exists (F.query (From:chapter, Span class= "KWRD" >where : F.and (F.constraint (chapter). Column (Chapter.bookidproperty), book. Idcolumn), F.constraint (chapter. Column (Chapter.nameproperty), propertyoperator.notequal, Chaptername))))); 

For more examples, refer to Tablequery related methods in the ormtest of unit tests in source code.

PS: This article has been included in the Rafy user manual.

Rafy framework-Use Sqltree query