Select 位置
正常情况:应该在最后面, 一般是 .Where(..).OrderBy(..).Select(..).ToList()
特殊情况:如果Select不是最后一个位置,则Select要加MergeTable()合并成一个表, Select(...).MergeTable().Where
语法糖:
Select(...).MergeTable() 在新版本中可以用SelectMergeTable(it=>new xxx(){xx}).Where
返回一个字段和多个字段
List< int > listInt=db.Queryable<Student>().Select(it => it.Id).ToList(); //返回一个字段
DataTable list=db.Queryable<Student>().Select(it => new { id=it.Id,name=it.Name}).ToDataTable(); //2个字段
List<Class1>list=db.Queryable<Student>().Select(it => new Class1{id=it.Id,name=it.Name}).ToList(); //2个字段
List<dynamic>list=db.Queryable<Student>().Select(it =>(dynamic) new {id=it.Id,name=it.Name}).ToList(); //2个字段
//动态
List< int > listInt=db.Queryable<Student>().Select< int >( "id" ).ToList();
List<Order> listInt=db.Queryable<Student>().Select<ViewModel>( "id as id, name as name" ).ToList();
|
单表返回DTO
//返回匿名对象
var dynamic = db.Queryable<Student>().Select<dynamic>().ToList();
//Select * from Student
//手动:返回匿名集合 支持跨程序集
List<dynamic> dynamicList = db.Queryable<Student>().Select(it=>(dynamic) new { id=it.id}).ToList();
//Select id as id from Student Select只有一列所以只查一列
//手动:返回匿名集合 不能跨程序集用
var dynamic = db.Queryable<Student>().Select(it=> new { id=it.id}).ToList();
//手动:返回类集合-手动
List<Student> list= db.Queryable<Student>().Select(it=> new Student{ id=it.id}).ToList();
//Select id as id from Student Select只有一列所以只查一列
//自动返回DTO集合: 请升级 5.1.3.2
var listDto= db.Queryable<Student>().Select<StudentDto>().ToList(); //返回List
//自动返回DTO : 请升级 5.1.3.35
var listDto= db.Queryable<Student>()
.Select(it=> new StudentDto()
{
Count=100 //手动指定一列在自动映射
},
true ) //true表式开启自动映射
.ToList(); //返回List
|
多表返回DTO
手动DTO
var newClass= db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, st.Name == di.String
))
.Select((st,sc,di)=> new ClassName{ name=st.Name,scid=sc.Id }) //看这一行就行了
.ToList(); //实体转换
//指定了2列只查2列
//select st.name as name , sc.id as scid
|
想要自动看下面
实体自动映射1
语法最美,新功能(5.1.3.35)
var list4=db.Queryable<SchoolA>()
.LeftJoin<StudentA>((x, y) => (x.SchoolId == y.SchoolId))
.Select((x,y) => new UnitView01()
{
Name=x.SchoolName,
Count=100
},
true ) //true表示 其余字段自动映射,根据字段名字
.ToList();
|
生成的Sql如下:
SELECT [x].[ID] AS [id] , --自动
[x].[ Time ] AS [ Time ] , --自动
[x].[SchoolName] AS [ Name ] --手动
100 as [ Count ] --手动
FROM [SchoolA] x
Left JOIN StudentA y ON ( [x].[SchoolId] =[y].[SchoolId])
|
实体自动映射2
说明:通过x.*方式实现多表查询
//生成的SQL为 Select o.*, [c].[Name] AS [CustomName]
var oneClass = db.Queryable<Order>()
.LeftJoin<OrderItem>((o,i)=>o.Id == i.OrderId)
.LeftJoin<Custom>((o,i,c)=>o.CustomId == c.Id)
.Where(o=>o.Id>1)
.Select((o,i,c)=> new ViewOrder // 是一个新类
{
//Id是o任意一个属性
Id=o.Id.SelectAll(), // 等于 o.* (SelectAll建议用一张表,多表会容易重名)
CustomName=c.Name // 等于 [c].[Name] AS [CustomName]
}).ToList()
|
生成Sql如下
SELECT o.*, [c].[ Name ] AS [CustomName]
FROM [ Order ] o
Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )
Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] ) WHERE [o].[Id]>1
|
实体自动映射3
说明:通过约束实现自动映射
比如一个3表查询 Order 、 OrderItem、Custom
需要注意的是 Select用的是自动填充这样使用方便,高并发的地方还是写成上面那种方式(5.0.5.2性能优化提升)
public class ViewOrder
{
public string Name { get ; set ; } // ORDER表中的name 主表规则【字段名】
public string CustomName { get ; set ; } //查询的是Custom中的的name 从表规则【class+字段名】
public string OrderItemPrice { get ; set ; } //查询的是OrderItem中的name 从表规则【 class+字段名】
}
var viewModel= db.Queryable<Order>()
.LeftJoin<OrderItem>((o,i)=>o.Id == i.OrderId)
.LeftJoin<Custom>((o,i,c)=>o.CustomId == c.Id)
.Select<ViewOrder>().ToList();
|
sql:
SELECT
o.[ Name ] AS [ Name ],
c.[ Name ] AS [CustomName],
i.[Price] AS [OrderItemPrice]
FROM [ Order ] o
Left JOIN [OrderItem] i ON ( [o].[Id] = [i].[OrderId] )
Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )
|
注意:
1.ViewOrder必须每个列都能匹配到字段,否则就无法按规则匹配,保证每个列都正确
2.高并发功能不建议使用,手写的性能肯定高于自动映射
匿名对象自动映射
说明:自动主表赋值 表.*
.Select<dynamic>((st,sc)=> new
{
//id是st任意一个属性
id=st.Id.SelectAll(), // st.* (SelectAll建议只用一张表,不然查询列会有重名)
SchoolName=sc.Name // Name as SchoolName
}).ToList()
//Select st.*,[sc].[Name] AS [schoolName]
//.SelectAll等同于SqlFunc.GetSelfAndAutoFill是个语法糖
|
创建DTO类
这样就能快速拿到DTO类的实体字符串比手写要快的多
string classtring=db.Qureyable<Order>().Select(....).ToClassString( "命名空间" );
|
多表查一表
var oneClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, o.CustomId == c.Id
))
.Select((o, i, c) => c).ToList();
|
多表返回2表
var twoClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, o.CustomId == c.Id
))
.Select((o, i, c) => new { o,i}).ToList()
|
双查询结果用法
当我们需要在select后在外面在包一层select,代码如下
var getAll = db.Queryable<Order>()
.Select(it => new Order
{
Id = it.Id * 2,
Name = it.Name
})
.MergeTable() //将上面的操作变成一个表 mergetable
.GroupBy(it => it.Id) //对表mergetable进行分组
.Select(it => new { id=it.Id }).ToList(); //查询mergetable表里面的字段
//SELECT `Id` FROM
// ( SELECT
// ( `Id` * @Id0 ) AS `Id` ,
// `Name` AS `Name`
//
// FROM `Order ) MergeTable
// GROUP BY `Id`
// 参数 @Id0:2
|
Select之后对字段进行C#处理
方式1 (5.1.4.113-preview+)
只能返回string只能是个单参数
public class UnitTool
{
public static string GetName( string name) //定义一个string
{
return "name" + 111;
}
}
//获取methodInfo
var methodInfo = typeof (UnitTool).GetMethod( "GetName" );
var list8 = db.Queryable<Order>()
.Select(it => new
{
n = it.Name,
name = SqlFunc.OnlyInSelectConvertToString(it.Name, methodInfo) //只能是select中用
}).ToList();
|
方式2
相对方式1用法麻烦 支持的功能全些
实体类
var list= db.Queryable<Order>().Select(it=> new Order{
Id=it.Id,
Name=it.Name
})
.Mapper(it => { //只能写在Select后面
it.Name = it.Id + it.Name; //相当于ToList循环赋值
}).ToList();
|
匿名对象
var list = db.Queryable<Order>().Select(it=>
(dynamic) new //转成 dynamic
{
Id=it.Id,
Name=it.Name
})
.Mapper(it => { //只能写在Select后面
it.Name = it.Id + it.Name; //相当于ToList循环赋值
}).ToList();
|
注意:(dynamic)不要漏了
动态Select
//方式1:多库兼容
var selector= new List<SelectModel>() {
new SelectModel(){AsName = "id1" ,FiledName = "id" },
new SelectModel(){ FiledName = "id" }};
var list=db.Queryable<Order>()..Select(selector).ToList();
//更多复杂用法:https://www.donet5.com/Home/Doc?typeId=2421
//SELECT `id` AS `id1` , `id` AS `id` FROM `Order`
//方式2:直接写SQL
var list=db.Queryable<Order>().Select( "ID AS id1,id as id" ).ToList();
//方式3: 动态表达式
StaticConfig.DynamicExpressionParserType = typeof (DynamicExpressionParser); //启动时配置
var list= db.Queryable<Order>().Select( "it" , $ "it=>new(it.Id as Id, it.Name)" , typeof (Order)).ToList();
//需要SqlFunc:https://www.donet5.com/Home/Doc?typeId=2569
|
别名AS用法
Select(it=> new { id1=it.id ,name2 =it.name })
//select id as id1,name as name2
|
如果是动态看 上面一个标题
返回元组
版本:5.1.4.84+
List<( int Id, string Name)> list
= db.Queryable<Order>().Select<( int Id, string Name)>( "id,name" ).ToList();
|
过滤某一个字段
/***单表***/
db.Queryable<Order>().IgnoreColumns(it=>it.Files).ToList(); //只支持单表查询
/***联查***/
//是主表
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();
//是Join的表
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();
|
标签:ToList,Name,db,用法,Select,id,Id,SqlSugar From: https://www.cnblogs.com/yswenli/p/17967729