5.0.1.3 版本发布 返回

SqlSugar 老数据
16 6689
//有自动赋值的外键
var data=new Order()
{
    Name = "订单 1",
        CustomId = 1,
        Price = 100,
        CreateTime = DateTime.Now,
        Id = 0,//自增列
        Items = new List<OrderItem>() {
                new OrderItem(){
                    CreateTime=DateTime.Now,
                    OrderId=0,//需要自动获取订单的自增列
                    Price=1,
                    ItemId=1
                },
                new OrderItem(){....},
                new OrderItem(){....}
               
            }
    };
db.Insertable(data)
    .AddSubList(it =>it.Items.First().OrderId)  //设置item表的OrderId等于订单自增列
    .ExecuteCommand();
//data可以返回所有数据,包括自增   
     
 
//没有自动赋值的外键
db.Insertable(new Order()
{
    Name = "订单 1",
        CustomId = 1,
        Pid = 20,
        CreateTime = DateTime.Now,
        Id = 0,//自增列
        Items = new List<OrderItem>() {
                new OrderItem(){
                    CreateTime=DateTime.Now,
                    Pid=20,
                    ItemId=1
                }
            }
    })
    .AddSubList(it => it.Items)//items  直接插入,items是什么就插入什么
    .ExecuteCommand();
 
 
//当然支持多个AddSubList
AddSubList(xx).AddSubList(xx)


热忱回答16

  • 新功能: 树型查询功能

    var tree = db.Queryable<Tree>().ToTree(it=>it.Child,it=>it.ParentId,0); //第三个参数为0代表 parent为0是第一级
    //实体
    public class Tree
    {
            [SqlSugar.SugarColumn(IsPrimaryKey =true)]
            public int Id { getset; }
            public string Name { getset; }
            public int ParentId { getset; }
            [SqlSugar.SugarColumn(IsIgnore = true)]
            public List<Tree> Child { getset; }
    }


    0 回复
  • 新功能多级插入:

    db.Insertable(new Order()
     {
                    Name = "订单 1",
                    CustomId = 1,
                    Price = 100,
                    CreateTime = DateTime.Now,
                    Id = 0,
                    Items = new List<OrderItem>() {
                          new OrderItem(){
                               CreateTime=DateTime.Now,
                               OrderId=0,
                                Price=1,
                                 ItemId=1
                           },
                          new OrderItem(){
                               CreateTime=DateTime.Now,
                               OrderId=0,
                                Price=2,
                                 ItemId=2
                           }
                     }
                })
    //OrderItem中的OrderId自动取上级的主键            
    .AddSubList(it => it.Items.First().OrderId).ExecuteReturnPrimaryKey();
    //如果有多个子结果这里还能在.AddSubList


    无限级插入

    var list=new List<Country1>()
                {
                   new Country1(){
                         Id=1,
                           Name="中国",
                         Provinces=new List<Province1>(){
                                new Province1{
                                     Id=1001,
                                     Name="江苏",
                                      citys=List1
                                },
                               new Province1{
                                     Id=1002,
                                     Name="上海",
                                      citys=List2
                                },
                               new Province1{
                                     Id=1003,
                                     Name="北京",
                                     citys=List3
                                }
                           }
                     },
                     new Country1(){
                          Name="美国",
                          Id=2,
                          Provinces=new List<Province1>()
                          {
                              new Province1(){
                                   Name="美国小A",
                                   Id=20001
                              },
                             new Province1(){
                                   Name="美国小b",
                                   Id=20002
                              }
                          }
                      },
                     new Country1(){
                          Name="英国",
                          Id=3
                      }
                };
    //开始插入
    db.Insertable(list)
    .AddSubList(it=>new SubInsertTree()
    {
         Expression=it.Provinces.First().CountryId,//CountryId自动填充
         ChildExpression=new List<SubInsertTree>() {
                    new SubInsertTree(){
                      Expression=it.Provinces.First()
                                      .citys.First()
                                      .ProvinceId//ProvinceId自动填充
                    }
          }
    })
    //如果有多个子结果这里还能在.AddSubList
    .ExecuteCommand();
    
    
    //实体
    
        public class Country1
        {
            [SqlSugar.SugarColumn(IsPrimaryKey = true,IsIdentity =true)]
            public int Id { get; set; }
            public string Name { get; set; }
    
            [SqlSugar.SugarColumn(IsIgnore = true)]
            public List<Province1> Provinces { get; set; }
        }
    
        public class Province1
        {
            [SqlSugar.SugarColumn(IsPrimaryKey = true,IsIdentity =true)]
            public int Id { get; set; }
            public string Name { get; set; }
            public int CountryId { get; set; }
            [SqlSugar.SugarColumn(IsIgnore = true)]
            public List<City1> citys { get; set; }
        }
    
        public class City1
        {
            [SqlSugar.SugarColumn(IsPrimaryKey = true,IsIdentity =true)]
            public int Id { get; set; }
            public int ProvinceId { get; set; }
            public string Name { get; set; }
        }




    0 回复
  • Mr、谢 Mr、谢 VIP0
    2020/11/15

    大佬,能修复下Select 查询对象 Json集合为null的问题吗?项目中等着用,谢谢

    已升级到5.0.1.3,postgresql数据类型为json的时候,连表查询json数据没转换出来,下面是demo

    using SqlSugar;

    using System;

    using System.Collections.Generic;


    namespace PgSqlTest

    {

        internal class Program

        {

            [SugarTable("User")]

            public class User

            {

                [SugarColumn(IsNullable = false, ColumnDataType = "uuid", IsPrimaryKey = true)]

                public Guid Id { get; set; }


                /// <summary>

                /// 部门名称

                /// </summary>

                [SugarColumn(IsNullable = false)]

                public string Name { get; set; }


                /// <summary>

                /// 用户部门

                /// </summary>

                [SugarColumn(IsNullable = false, IsJson = true, ColumnDataType = "json")]

                public List<Department> UserDpt { get; set; }

            }


            [SugarTable("User2")]

            public class User2

            {

                [SugarColumn(IsNullable = false, ColumnDataType = "uuid", IsPrimaryKey = true)]

                public Guid Id { get; set; }


                [SugarColumn(IsNullable = false, ColumnDataType = "uuid")]

                public Guid UserId { get; set; }

            }


            public class Department

            {

                public Guid Id { get; set; }

                public string Name { get; set; }

            }


            private static void Main(string[] args)

            {

                SqlSugarClient db = new SqlSugarClient(

                 new ConnectionConfig()

                 {

                     ConnectionString = "*",

                     DbType = DbType.PostgreSQL,//设置数据库类型

                     IsAutoCloseConnection = true,//自动释放数据务,如果存在事务,在事务结束后释放

                     InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息

                 });

                db.Aop.OnError = (exp) =>//SQL报错

                {

                    string sql = exp.Sql;

                    //exp.sql 这样可以拿到错误SQL

                };

                //db.DbMaintenance.CreateDatabase();

                db.CodeFirst.InitTables(typeof(User), typeof(User2));

                List<Department> departments = new List<Department>();

                departments.Add(new Department { Id = Guid.NewGuid(), Name = "研发部" });

                departments.Add(new Department { Id = Guid.NewGuid(), Name = "市场部" });

                Guid userId = Guid.NewGuid();

                User user = new User

                {

                    Id = userId,

                    Name = "张三",

                    UserDpt = departments

                };

                db.Insertable(user).ExecuteCommand();

                User2 user2 = new User2();

                user2.Id = Guid.NewGuid();

                user2.UserId = userId;


                db.Insertable(user2).ExecuteCommand();

                var data = db.Queryable<User, User2>((a, b) => new object[] { JoinType.Inner, a.Id == b.UserId })

                    .Where((a, b) => a.Id == userId)

                    .Select((a, b) => new

                    {

                        User = a,//这里面的json集合没数据

                        Items = a.UserDpt

                    }).ToList();

                Console.ReadKey();

            }

        }

    }



    0 回复
  • @Mr、谢:你先这么用 


    .ToList().Select(it=>new { a=it,dep=it.UserDpt}).ToList();

    0 回复
  • @Mr、谢:我本地已修复后面版本会发布

    0 回复
  • Mr、谢 Mr、谢 VIP0
    2020/11/15

    @fate stay night:太感谢了

    0 回复
  • 韶华 韶华 VIP0
    2020/11/18

    有没有级联修改? 或者同一个方法实现级联新增和修改?


    0 回复
  • 随风 随风 VIP0
    2020/12/3

    ISugarQueryable的Select方法使用Tuple报错。

    错误写法:

    dbContent.Queryable<AcigaParkingOutvehicleModel>()
                    .GroupBy(it => new { it.projectUuid, it.typeValue })
                    .Select(it => new Tuple<string, int, int>(it.projectUuid, it.typeValue, SqlFunc.AggregateCount(1)))
                    .ToList();

    需要这样写才不会报错:

    dbContent.Queryable<AcigaParkingOutvehicleModel>().AS($"outvehicle_{zoneRID}")
                    .GroupBy(it => new { it.projectUuid, it.typeValue })
                    .Select(it=>new { it.projectUuid, it.typeValue ,count= SqlFunc.AggregateCount(1) })
                    .ToList()
                    .Select(it => new Tuple<string, int, int>(it.projectUuid, it.typeValue, it.count))
                    .ToList();

    请问是不是sqlsugar还不支持元组呢?

    0 回复
  • KingVar KingVar VIP0
    2020/12/20

    @fate stay night:请教一下,级联新增这个方法不能异步吗?

    0 回复
  • @KingVar:后期可以做 

    0 回复
  • @随风:不支持

    0 回复
  • KingVar KingVar VIP0
    2020/12/20

    @fate stay night:好的,谢谢!

    0 回复
  • muzili muzili VIP0
    2021/4/2

    无限极插入时,.AddSubList这个没有属性,该如何解决呀?

    0 回复
  • 小杰 小杰 VIP0
    2021/4/2

    @muzili:文档上官网都有例子

    0 回复
  • YQ YQ VIP0
    2022/3/3
    db.Insertable(entity)
                     .AddSubList(x => new SubInsertTree()
                     {
                         Expression = x.UserTasks.First().TaskId,
                         ChildExpression = new List<SubInsertTree>
                         {
                            new SubInsertTree
                            {
                                Expression=x.UserTasks.First().Details.First().UserTaskId,
                                ChildExpression=new List<SubInsertTree>
                                {
                                    new SubInsertTree
                                    {
                                        Expression=x.UserTasks.First().Details.First().Details.First().UserTaskDetailId
                                    }
                                }
                            }
                         }
                     }).ExecuteCommandAsync()

    请问四层联级这么写对吗,第四层没有执行写入

    0 回复
  • 老功能存档

    //有自动赋值的外键
    var data=new Order()
    {
        Name = "订单 1",
            CustomId = 1,
            Price = 100,
            CreateTime = DateTime.Now,
            Id = 0,//自增列
            Items = new List<OrderItem>() {
                    new OrderItem(){
                        CreateTime=DateTime.Now,
                        OrderId=0,//需要自动获取订单的自增列
                        Price=1,
                        ItemId=1
                    },
                    new OrderItem(){....},
                    new OrderItem(){....}
                   
                }
        };
    db.Insertable(data)
        .AddSubList(it =>it.Items.First().OrderId)  //设置item表的OrderId等于订单自增列
        .ExecuteCommand();
    //data可以返回所有数据,包括自增   
         
     
    //没有自动赋值的外键
    db.Insertable(new Order()
    {
        Name = "订单 1",
            CustomId = 1,
            Pid = 20,
            CreateTime = DateTime.Now,
            Id = 0,//自增列
            Items = new List<OrderItem>() {
                    new OrderItem(){
                        CreateTime=DateTime.Now,
                        Pid=20,
                        ItemId=1
                    }
                }
        })
        .AddSubList(it => it.Items)//items  直接插入,items是什么就插入什么
        .ExecuteCommand();
     
     
    //当然支持多个AddSubList
    AddSubList(xx).AddSubList(xx)


    0 回复