CRL Rapid Development Framework Series Tutorial II (based on lambda expression query)

Catalog of this series

1. CRL Rapid Development Framework Series tutorial One (Code First Data Sheet no need to care)
2. CRL Rapid Development Framework Series Tutorial II (based on lambda expression query)
3. CRL Rapid Development Framework Series Tutorial III (update data)
4. CRL Rapid Development Framework Series Tutorial IV (delete data)
5. CRL Rapid Development Framework Series tutorial five (using cache)
6. CRL Rapid Development Framework Series Tutorial VI (Distributed cache solution)
7. CRL Rapid Development Framework Series Tutorial VII (using transactions)
8. CRL Rapid Development Framework Series tutorial eight (using Crl.package)
9. CRL Rapid Development Framework Series Tutorial nine (Import/Export data)
10. CRL Rapid Development Framework Series Tutorial 10 (Export object structure)
11. CRL Rapid Development Framework Series Tutorial 11 (Big Data sub-database sub-table solutions)
12. CRL Rapid Development Framework Series Tutorial 12 (MongoDB support)

Body

The CRL uses lambda expressions to represent query logic, expressions, and string-based syntax comparisons, with the following advantages:

• Native Syntax & operator support
  Easy to use based on linq.expressions syntax
• Strong type Check Compilation
  Don't worry about the wrong name, the type is wrong, the IDE will prompt you when compiling
• Easy to pass the reference
  Example: b=>b.id==1 parameter direct write, unified processing, do not worry about the injection problem
• An extension method that is essentially equivalent to a SQL function
  Example: b=>b.name.substring (0,2) = "22" means SQL Substring (name,0,2)

The CRL object management base class is an abstract class that encapsulates the usual methods, which means that you don't have to be silly to write any findone,findall methods.

ORM is only part of the CRL, and the object management class is further encapsulated, so there is a CRL. Baseprovider<t>

Implementing Object Management

public class PRODUCTDATAMANAGE:CRL. Baseprovider<productdata>public static Productdatamanage Instance        {            get {return new Productdatamanage ( ); }        }var instance = productdatamanage.instance;

Querying directly with an expression

var item = instance. Queryitem (b = b.id > 1); var list = instance. Querylist (b = b.id > 1);

Create full query syntax

var query = instance. Getlambdaquery ();//Create query queries. TOP (100);//How many query to take. Where (b = = b.ID < 700);//Query condition. Order BY (b = b.id, true);//Sort condition var list = query. ToList ();

If you are paging, you only need to

Query. Page (10, 1);//page size, page index

The above is to return the current object, to return the specified properties, to return the dynamic type

such as group query

Using the CRL to get the extension method            var query = instance. Getlambdaquery ();            Query. Where (b = b.id > 0);            Query. Top (ten);            Select the Group field            query.  Select (b =            = new {                B.barcode,                sum2 = b.sum (x = X.number * x.id),//equivalent SUM (number*id) as sum2 total                = B.barcode.count (),//is equivalent to COUNT (BarCode) as total                sum1 = B.number.sum (),//equivalent to SUM (number) as Sum1                B. ProductName            });            Group Condition            Query. GroupBy (b = new {b.barcode, b.productname});            Having            query. Grouphaving (b = b.number.sum () >= 0);            Set sort            query. Order BY (b = = B.barcode.count (), true), or//equivalent to Count (BarCode) descvar list = query. todynamic ();

Make a simple association query

Typically only the selected partial field is returned, the Select method is called, and the dynamic type is returned

Returns the filter value            var query = instance. Getlambdaquery ();            Query. Top (ten);            var member = new Code.member ();            Member. Id = one;            Query. Join<code.member> ((a, b) = = A.userid = = Member. Id && b.id > 0,                 CRL. LambdaQuery.JoinType.Left                ). Select ((A, b) = = new {BarCode1 = A.barcode, Name1 = B.name,a.productname});            var list = query. todynamic ();

If you want to return all the fields of the main table, and the associated table partial fields, call the Selectappendvalue method

The main object and the built-in index values are now returned

Put the associated value into the object internal index            //The associated object value is indexed access to            var query = instance. Getlambdaquery ();            Query. Top (ten);            Query. Join<code.member> ((a, b) = = A.userid = = b.id && b.id > 0,                CRL. LambdaQuery.JoinType.Left                ). Selectappendvalue (b = new {Name1 = b.name});            var list = query. ToList (); foreach (var item in list)            {                var str = string. Format ("{0}______{1}<br>", item. BarCode, item. BAG.NAME1);//name of index value                Response.Write (str) called Name1            ;}

If there are multiple associations, then call the Join method to

With these two return values, I believe we can adapt to most of the scenarios.

Extension methods

In order to implement SQL function calls, extension methods are used instead of representations

The following are the extension methods supported by CRLs

Using CRLs to get extension methods//For unary operations, you can judge by! =, such as B. Productname.contains ("122") and!b.productname.contains ("122") var query = instance.            Getlambdaquery (); Query. Where (b = b.id < B.number);//Direct comparison can be resolved through query. Where (b = b.productname.contains ("122"));//contains a string query. Where (b =!b.productname.contains ("122"));//Does not contain a string query. Where (b = b.productname.in ("111", "222"));//string in query. where (b = B.addtime.between (DateTime.Now, DateTime.Now));//query within the time period. Where (b = B.addtime.datediff (DATEPART.DD, DateTime.Now) > 1);//Time comparison query. Where (b = b.productname.substring (0, 3) = = "222");//intercept string query. Where (b = b.id.in (1, 2, 3));//in query. Where (b =!b.id.in (1, 2, 3));//not in query. Where (b = b.userid.equals (Code.productchannel. Other));//By value equals, enum equals int query. Where (b = = B.productname.startswith ("abc"));//start with a value of query. Where (b =&GT B.id.between (1, 10));//numeric range query. Where (b = b.productname.like ("123"));//%like% query. Where (b = B.productname.likeleft ("123"));//%like query. Where (b = b.productname.likeright ("123"));//like% query. Where (b =!string.            IsNullOrEmpty (B.barcode));            int lastdays = 30; Query. Where (b = B.addtime.datediff (CRL). DATEPART.DD, DateTime.Now) < lastdays);

Some conversion functions also support

Query. Where (b = b.id.tostring () = = "123");//Support cast conversion query.where (b = convert.tostring (b.id) = = "1");

Wait a minute...

About the results returned

• The page method can be paged using the Lambdaquery full query, and the result of the specified type is returned by the ToList and Todynamic methods
• When the association is set, the group syntax is also associated, and group syntax is parsed
• When the Select Method Filter field is called, the result type needs to be returned according to the actual situation
• The returned results can be of several types
  • List<dynamic> todynamic () return dynamic type by filter value
  • List<tresult> tolist<tresult> () returns the specified type by filter value
  • List<t> ToList () returns the current type directly
  • Dictionary<tkey, tvalue> Todictionary<tkey, tvalue> () returns a dictionary by filter value (pagination not supported)

Print Query

When it is a full query, call query. Printquery () Returns the generated query statement and parameters

If the associated query is printed out as

[Sql]:select Top ten T1. [BarCode] as barcode1,t2. [Name] as NAME1,T1. [ProductName1] as ProductName from  [productdata] T1 with  (NOLOCK) left  join [Member] T2 with   (NOLOCK) on (t1. [UserId] [Email protected]) and (t2.[ Id]> @par1))  [par0]:[11][par1]:[0]

CRL Rapid Development Framework Series Tutorial II (based on lambda expression query)