1.1 Query‐Simple (original) (raw)

Query all

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

Query Count

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

Conditional query

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

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

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

Multi-condition query

db.Queryable().Where(it=>it.Id>10&&it.Name=="a").ToList() //select * from Student where id>10 and name='a' db.Queryable().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(); 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().Where(exp.ToExpression()).ToList();

like query

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

Query the first item, first row

. First() is equivalent to FirstOrDefault in C #, returns null without a value db.Queryable(). 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(). 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(). Take(10). ToList()

//select top 10 * from Student

Count

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

db.Queryable(). 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(). 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().AS("dbo.School").ToList(); //SQL: SELECT [ID],[NAME] FROM dbo.School

Is there a record

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

db.Queryable(). 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(). 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 list=xxx;

db.Queryable(). 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 list=xxx;

db.Queryable(). 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(). Where(it => ! allIds.Contains(it.OrderId)). ToList()

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

Simple sorting

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

Query a column

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

Query single entry

db.Queryable(). 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(). Max(it=>it.Id);// Synchronize

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

//You can also use the function SqlFunc AggregateMax

Get minimum value

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

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

//You can also use the function SqlFunc AggregateMin

Summation

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

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

//You can also use the function SqlFunc AggregateSum

average value

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

db.Queryable(). 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(). IgnoreColumns(it=>it.Files). ToList();// Only supports single table queries

/Joint search/

//It's the main table

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

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

//It's Join's table

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

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