DotNetNext / SqlSugar

.Net aot ORM Fastest ORM Simple Easy VB.NET Sqlite orm Oracle ORM Mysql Orm 虚谷数据库 postgresql ORm SqlServer oRm 达梦 ORM 人大金仓 ORM 神通ORM C# ORM , C# ORM .NET ORM NET5 ORM .NET6 ORM ClickHouse orm QuestDb ,TDengine ORM,OceanBase orm,GaussDB orm ,Tidb orm Object/Relational Mapping
https://www.donet5.com/Home/Doc
MIT License
5.35k stars 1.34k forks source link

一对多原生sql查询 #999

Closed hyke123 closed 2 years ago

hyke123 commented 2 years ago

SqlQueryable 或者Ado.SqlQuery 连表一对多的查询结果怎么映射到类的集合属性上

DotNetNext commented 2 years ago

SQL本来就不支持一对查询吧 ,你的SQL是什么样的

DotNetNext commented 2 years ago

db.Ado.SqlQuery<T1,T2>("select 1 from xx; select xx from yy") 目前ADO查询只有这种多条SQL一起映射

hyke123 commented 2 years ago

image 可能是我表述的不够清楚 想要的效果就是类似dapper这种

DotNetNext commented 2 years ago
    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();

生成的SQL SELECT [o].[Id] AS [Order.Id] , [o].[Name] AS [Order.Name] , [o].[Price] AS [Order.Price] , [o].[CreateTime] AS [Order.CreateTime] , [o].[CustomId] AS [Order.CustomId] , [i].[ItemId] AS [OrderItem.ItemId] , [i].[OrderId] AS [OrderItem.OrderId] , [i].[Price] AS [OrderItem.Price] , [i].[CreateTime] AS [OrderItem.CreateTime] FROM [Order] o Left JOIN [OrderDetail] i ON ( [o].[Id] = [i].[OrderId] ) Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )

DotNetNext commented 2 years ago
    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();

生成的SQL SELECT [o].[Id] AS [Order.Id] , [o].[Name] AS [Order.Name] , [o].[Price] AS [Order.Price] , [o].[CreateTime] AS [Order.CreateTime] , [o].[CustomId] AS [Order.CustomId] , [i].[ItemId] AS [OrderItem.ItemId] , [i].[OrderId] AS [OrderItem.OrderId] , [i].[Price] AS [OrderItem.Price] , [i].[CreateTime] AS [OrderItem.CreateTime] FROM [Order] o Left JOIN [OrderDetail] i ON ( [o].[Id] = [i].[OrderId] ) Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )

DotNetNext commented 2 years ago

不用SQL用联表查询是可以的

DotNetNext commented 2 years ago
   var x= db.SqlQueryable<dynamic>("SELECT  [o].[Id] AS [Order.Id] , [o].[Name] AS [Order.Name] , [o].[Price] AS [Order.Price] , [o].[CreateTime] AS [Order.CreateTime] , [o].[CustomId] AS [Order.CustomId] , [i].[ItemId] AS [OrderItem.ItemId] , [i].[OrderId] AS [OrderItem.OrderId] , [i].[Price] AS [OrderItem.Price] , [i].[CreateTime] AS [OrderItem.CreateTime]  FROM [Order] o Inner JOIN [OrderDetail] i ON ( [o].[Id] = [i].[OrderId] )")
               .Select(it=>new {  o=new Order(),i=new OrderItem()})
               .Select("*").ToList();

有方案了 这样命名可以自动映射

DotNetNext commented 2 years ago

https://www.donet5.com/Home/Doc?typeId=1197 文档也更新了