SqlSugar4.7-4.8 近期更新功能 返回

1. 异步事务的使用 

 主要用于解决异步方法不支持事务，并且又要返回事务类型

//async tran
var asyncResult = db.Ado.UseTranAsync(() =>
{

    var beginCount = db.Queryable<Student>().ToList();
    db.Ado.ExecuteCommand("delete student");
    var endCount = db.Queryable<Student>().Count();
    throw new Exception("error haha");
});
asyncResult.Wait();
var asyncCount = db.Queryable<Student>().Count();

//async
var asyncResult2 = db.Ado.UseTranAsync<List<Student>>(() =>
{
    return db.Queryable<Student>().ToList();
});
asyncResult2.Wait();

2.多表查询的智能自动填充，让代码更简单

以前需要手动去new{  SchoolName=sc.Name } //现在只要在ViewModel取名叫 SchoolName这种有规则命名就能自动赋值

多表有相同字段优先取第一个实体中的字段

//auto fill ViewModelStudent3
var s11 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id)
                        .Select<ViewModelStudent3>().ToList();

3.版本控制

用于判段当前数据是否是数据库最新版本

public class VersionValidation : DemoBase
    {
        public static void Init()
        {
            TimestampDemo();
            DateTimeDemo();
        }

        private static void TimestampDemo()
        {
            var db = GetInstance();
            try
            {

                var data = new StudentVersion()
                {
                    Id = db.Queryable<Student>().Select(it => it.Id).First(),
                    CreateTime = DateTime.Now,
                    Name = "",
                };
                db.Updateable(data).IgnoreColumns(it => new { it.Timestamp }).ExecuteCommand();

                var time = db.Queryable<StudentVersion>().Where(it => it.Id == data.Id).Select(it => it.Timestamp).Single();

                data.Timestamp = time;

                //is ok
                db.Updateable(data).IsEnableUpdateVersionValidation().IgnoreColumns(it => new { it.Timestamp }).ExecuteCommand();
                //updated Timestamp change

                //is error
                db.Updateable(data).IsEnableUpdateVersionValidation().IgnoreColumns(it => new { it.Timestamp }).ExecuteCommand();

                //IsEnableUpdateVersionValidation Types of support  int or long or byte[](Timestamp) or Datetime 

            }
            catch (Exception ex)
            {
                if (ex is SqlSugar.VersionExceptions)
                {
                    Console.Write(ex.Message);
                }
                else
                {

                }
            }
        }
        private static void DateTimeDemo()
        {
            var db = GetInstance();
            try
            {

                var data = new StudentVersion2()
                {
                    Id = db.Queryable<Student>().Select(it => it.Id).First(),
                    CreateTime = DateTime.Now,
                    Name = "",
                };
                db.Updateable(data).ExecuteCommand();

                var time = db.Queryable<StudentVersion2>().Where(it => it.Id == data.Id).Select(it => it.CreateTime).Single();

                data.CreateTime = time;

                //is ok
                db.Updateable(data).IsEnableUpdateVersionValidation().ExecuteCommand();


                data.CreateTime = time.AddMilliseconds(-1);
                //is error
                db.Updateable(data).IsEnableUpdateVersionValidation().ExecuteCommand();

                //IsEnableUpdateVersionValidation Types of support  int or long or byte[](Timestamp) or Datetime 

            }
            catch (Exception ex)
            {
                if (ex is SqlSugar.VersionExceptions)
                {
                    Console.Write(ex.Message);
                }
                else
                {

                }
            }
        }

        [SqlSugar.SugarTable("Student")]
        public class StudentVersion
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public DateTime CreateTime { get; set; }
            [SqlSugar.SugarColumn(IsEnableUpdateVersionValidation = true,IsOnlyIgnoreInsert=true)]
            public byte[] Timestamp { get; set; }
        }

        [SqlSugar.SugarTable("Student")]
        public class StudentVersion2
        {
            public int Id { get; set; }
            public string Name { get; set; }
            [SqlSugar.SugarColumn(IsEnableUpdateVersionValidation = true, IsOnlyIgnoreInsert = true)]
            public DateTime CreateTime { get; set; }
        }
    }

4.Select升级版 Mapper

如果说Select是用来生成SQL，那么Mapper更准确的来主是对查询出来的结果自动加工，

Select是有局限性的虽然支持了子查询，像一对多查询等不支持，很多C#方法的不支持

那么Mapper可以让你不用担心

  var s12 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Select<ViewModelStudent3>()

                .Mapper((it, cache) =>
                {
                    //一次性查询出需要的school集合并且临时存储起来
                    var allSchools = cache.GetListByPrimaryKeys<School>(vmodel => vmodel.SchoolId);
                    //sql select * from  shool where id (in（ViewModelStudent3[0].SchoolId , ViewModelStudent3[1].SchoolId...）

                    //等同于上面写法，复杂的对应关系可以用该方法
                    //var allSchools2= cache.Get(list =>
                    // {
                    //     var ids=list.Select(i => it.SchoolId).ToList(); 
                    //     return db.Queryable<School>().In(ids).ToList();
                    //});Complex writing metho


                    /*处理一对一*/
                    //高性能
                    it.School = allSchools.FirstOrDefault(i => i.Id == it.SchoolId);

                    //性能差，这种写法相当于循环在查询，所以用Cache.XXX可以避免循环
                    //it.School = db.Queryable<School>().InSingle(it.SchoolId); 


                    /*处理一对多*/
                    it.Schools = allSchools.Where(i => i.Id == it.SchoolId).ToList();


                    /*可以用C#任何语法处理想要结果*/
                    it.Name = it.Name == null ? "null" : it.Name;

                }).ToList();