1.1 Query‐Simple

Query all

List<Student> list=db.Queryable<Student>().ToList() 
//select * from Student

Query Count

int count=db.Queryable<Student>().Count()
//select count(1) from Student

Conditional query

db.Queryable<Student>().Where(it=>it.Id==1).ToList()
//select * from Student where id=1

db.Queryable<Student>().Where(it=>it.name !=null).ToList()//is not null 
//select * from Student where name is not null
db.Queryable<Student>().Where(it=>it.name ==null).ToList()//is null
//select * from Student where name is null

db.Queryable<Student>().Where(it=>it.name !="").ToList()//is not empty  
//select * from Student where name <> ''

Multi-condition query

db.Queryable<Student>().Where(it=>it.Id>10&&it.Name=="a").ToList()
//select * from Student where id>10 and name='a'
db.Queryable<Student>().Where(it=>it.Id>10).Where(it=>it.Name=="a").ToList()
//select * from Student where id>10 and name='a'
//if or use ||

Dynamic OR query

var exp= Expressionable.Create<Student>();
exp.OrIF(condition,it=> it.id ==1); //.OrIf splices OR only if the condition is true
exp.Or(it =>it.Name.Contains("jack")); // Splice OR
var list=db.Queryable<Student>().Where(exp.ToExpression()).ToList();

like query

db.Queryable<Student>().Where(it =>it.Name.Contains("jack")).ToList();
//select  * from  Student where name like %jack%
Query by primary key
single in susgar is equivalent to SingleOrDefault in EF

Single primary key query

db.Queryable<Student>().InSingle(2) // Query SingleById by primary key
db.Queryable<Student>().single (it=>it.Id==2) // Query by ID
//select * from Student where id=2 
/* Multi-primary key query */
var getAll=db.Queryable<Order>().WhereClassByPrimaryKey(new Order(){Pk1=1,Pk2=xx}).ToList(); //单个实体  
vargetAll=db.Queryable<Order>().WhereClassByPrimaryKey(List<Order>).ToList(); //支持集合

Query the first item, first row

. First() is equivalent to FirstOrDefault in C #, returns null without a value
db.Queryable<Student>(). First (it=>it. ID==1)//No null returned
//select top 1 * from Student where id=1

Query the last item

First() is equivalent to FirstOrDefault in C #, returns null without a value
db.Queryable<Student>(). OrderBy (it=>it. ID, OrderByType. Desc)//Reverse order
. First (it=>it. ID>10)//No null returned

//select top 1 * from Student order by id desc where id>10

Top/Take

db.Queryable<Student>(). Take(10). ToList()

//select top 10 * from Student

Count

db.Queryable<Student>(). Where(it=>it.Id>11). Count()//Sync

db.Queryable<Student>(). Where(it=>it.Id>11). CountAsync()//Asynchronous

//select count(*) from Student where id>11

//You can also use functions

SqlFunc.AggregateCount

Set new table name

//Example 1: Updating Table Names

db.Queryable<School>(). AS("Student"). ToList();

//Generate SQL SELECT [ID], [NAME] from Student

//Dynamic table name and table alias specification indicate


//Example 2: Adding a prefix to a table name
db.Queryable<School>().AS("dbo.School").ToList();
//SQL:  SELECT [ID],[NAME] FROM  dbo.School 

Is there a record

db.Queryable<Student>(). Where(it=>it.Id>11). Any()

db.Queryable<Student>(). Any (it=>it. ID>11)//Simplification of the above syntax

//Asynchronous is AnyAsync()

In Query (Single Field)

Single field

int [] allIds =new int[]{2,3,31};

db.Queryable<OrderItem>(). Where(it => allIds. Contains(it.OrderId)). ToList()

//orderid in (2,3,31)

//Supports over 1000, as long as the database is not slow and has no upper limit



//String types varchar and nvarchar (default varchar to ensure performance)

NameList. Contains (it. Name, true)//True and false to control whether it is varchar or nvarchar

In Query (Multiple Fields)

Multiple Fields (Upgrade: 5.1.4.67-preview04)
 
List<OrderItem> list=xxx;

db.Queryable<OrderItem>(). Where(it => list. Any(s => s.Id == it.Id && s.Name == it.Name))

//It can also be achieved by using dynamic expressions to spell OR

In Like (OR)

List<OrderItem> list=xxx;

db.Queryable<OrderItem>(). Where(it => list. Any(s => it.Name. Contanins(s.Name)))
//List.Any (OR)  List.All (AND)

Not In

int [] allIds =new int[]{2,3,31};

db.Queryable<OrderItem>(). Where(it => ! allIds.Contains(it.OrderId)). ToList()

//orderid NOT in (2,3,31)

Simple sorting

db.Queryable<Student>(). OrderBy((st,sc)=>sc.Id,OrderByType.Desc).ToList()

Query a column

db.Queryable<Student>(). Select(it=>it.Name). ToList()//Single value query column query individual column

Query single entry

db.Queryable<Student>(). Single (it=>it. ID=1)//No null returned. If the result is greater than 1, an error will be thrown

//Select * from Student where id=1//Query a single record with id=1

Get maximum value

db.Queryable<Order>(). Max(it=>it.Id);// Synchronize

db.Queryable<Order>(). MaxAsync(it=>it.Id);// asynchronous

//You can also use the function SqlFunc AggregateMax

Get minimum value

db.Queryable<Order>(). Min(it=>it.Id);// synchronization

db.Queryable<Order>(). MinAsync(it=>it.Id);// asynchronous

//You can also use the function SqlFunc AggregateMin

Summation

db.Queryable<Order>(). Sum(it=>it.Id);// synchronization

db.Queryable<Order>(). SumAsync(it=>it.Id);// asynchronous

//You can also use the function SqlFunc AggregateSum

average value

db.Queryable<Order>(). Avg(it=>it.Id);// synchronization

db.Queryable<Order>(). AvgAsync(it=>it.Id);// asynchronous

//You can also use the function SqlFunc AggregateAvg

Query Filter Exclude a Field

The generated SQL Select will not have Files

/***Single Table***/

db.Queryable<Order>(). IgnoreColumns(it=>it.Files). ToList();// Only supports single table queries




/***Joint search***/

//It's the main table

var leftQuery=db.Queryable<Order>(). IgnoreColumns(it=>it.Files);

var list=db.Queryable(leftQuery). LeftJoin<OrderDetails>((o,d)=>o.id==d.orderid).Select(o=>o). ToList();



//It's Join's table

var rightQuery= db.Queryable<OrderItem>(). IgnoreColumns(it=>it.Files);

var list=db.Queryable<Order>(). LeftJoin(rightQuery,(o,d)=>o.Id == d.OrderId).Select(o=>o). ToList();