正常情况:应该在最后面, 一般是 .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(); //动态 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();
//返回匿名对象 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
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
想要自动看下面
语法最美,新功能(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])
说明:通过x.*方式实现多表查询
注意: Oracle如果用到Take或者分页 需要改用ToffsetPage()替换
//生成的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表查询 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.高并发功能不建议使用,手写的性能肯定高于自动映射
说明:自动主表赋值 表.*
注意: Oracle如果用到Take或者分页 需要改用ToffsetPage()替换
.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类的实体字符串比手写要快的多
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();
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
只能返回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();
相对方式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)不要漏了
//方式1:多库兼容 var selector= new List<SelectModel>() { new SelectModel(){ FiledName = "id",AsName = "id2",}, new SelectModel(){ FiledName = "id"} //常量用法 new SelectModel(){FieldName= "{string}:a",AsName="Name"}}; var list=db.Queryable<Order>().Select(selector).ToList(); //SELECT `id` AS `id2` , `id` AS `id` ,@Name as Name FROM `Order` //更多复杂用法: https://www.donet5.com/Home/Doc?typeId=2421 //方式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
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();
2016 © donet5.comApache Licence 2.0