sihuashanxq / zarf Goto Github PK

using(var db=new DbContext(serviceBuilder=>serviceBuilder.UseSqlServer(ConnectionString)))
{
    foreach(var user in db.Query<User>())
    {

    }
}

public class MyDbContext : DbContext
{
    public MyDbContext(Func<IDbServiceBuilder, IDbService> serviceBuilder) 
        : base(serviceBuilder)
    {
    }
}

//StartUp.cs
public void ConfigureServices(IServiceCollection services)
{
    services.AddTransient(p =>new MyDbContext(b => b.UseSqlServer(ConnectionString)));
}

public class User
{
    public int Id { get; set; }

    public int Age { get; set; }

    public string Name { get; set; }

    public DateTime CreateDay { get; set; }

    public string Tel { get; set; }
}

public class Order
{
    public int Id { get; set; }

    public int UserId { get; set; }

    public string Goods { get; set; }
}

public interface IQuery
{
}

public interface IQuery<TEntity> : IQuery
{
    IQuery<TEntity> Where(Expression<Func<TEntity, bool>> predicate);

    TEntity First();

    TEntity First(Expression<Func<TEntity, bool>> predicate);

    TEntity FirstOrDefault();
    .......
}

db.Query<User>().Where(i => i.Id > 10)

db.Query<User>()
    .Join(db.Query<Order>(), (u, o) => u.Id == o.UserId, JoinType.Inner)
    .Select((u, o) => new { u.Id, o.Goods });

//Age Asc,Id Desc
db.Query<User>().OrderBy(i => i.Age).ThenByDescending(i => i.Id);

//GroupBy,OrderBy中只能范围一个属性,因此先Select,后续提供i=>i,i=>new{i.Id,i.Age}等支持
db.Query<User>().Select(i => new { i.Id }).GroupBy(i => i.Id);

db.Query<User>().Take(10);    

//使用ROW_NUMBER()函数,sqlite生成Limit,Offset
db.Query<User>().Skip(2);

db.Query<User>().All(u => u.Id > 0);

db.Query<User>().Any(u => u.Id > 10);

db.Query<User>().First(i => i.Id == 3);
db.Query<User>().FirstOrDefault(i => i.Id == 3);

db.Query<User>().Single(i => i.Id == 3);
db.Query<User>().SingleOrDefault(i => i.Id == 3);

db.Query<User>().Union(db.Query<User>().Where(i=>i.Id<10));

db.Query<User>().Sum(i => i.Age);    

db.Query<User>().Count();    

db.Query<User>().Max(i=>i.Id);    

db.Query<User>().Min(i=>i.Id);    

db.Query<User>().Average(i=>i.Age);    

 //在子查询中如果是聚合类/All/Any的查询,则合并到外层查询中
 db.Query<User>()
    .Where(i => i.Id < 10)
    .Select(i => new
    {
        UserId = i.Id,
        MaxUserId = db.Query<User>().Where(m => m.Id == i.Id).Max(m => m.Id)
    });

db.Query<User>()
    .Where(i => i.Id < 10)
    .Select(i => new
    {
        UserId = i.Id,
        Orders = db.Query<Order>().Where(o => o.UserId == i.Id).ToList(),
        Orders2=db.Query<Order>().Where(o => o.UserId == i.Id).AsEnumerable()
    });   

db.Query<User>()
        .Where(i => i.Id < 100)
        .Where(m => db.Query<User>().FirstOrDefault(n => n.Id == 1) != null)
        .Where(m => db.Query<User>().All(i => i.Id > 0))
        .Select(n => new
        {
            AllIdMoreThanOne = db.Query<User>().Where(mm => mm.Id == n.Id).All(nnn => nnn.Id > 0),
            Orders = db.Query<Order>().Where(m => m.Id == n.Id).ToList()
        }).ToList();

/// <summary>
/// int 扩展
/// </summary>
public static class IntExtension
{
    [SQLFunctionHandler(typeof(IntSQLFunctionHandler))]
    public static int Add(this int i, int n)
    {
        //如果没有常数调用(Add(1,2),1.Add(2))的情况,可以抛出异常
        return i + n;
    }
}

/// <summary>
/// 处理Function的SQL生成
/// </summary>
public class IntSQLFunctionHandler : ISQLFunctionHandler
{
    public Type SoupportedType => typeof(int);

    public bool HandleFunction(ISQLGenerator generator, MethodCallExpression methodCall)
    {
        //SQL 生成,调用generator可以对常量进行参数化处理
        if (methodCall.Method.Name == "Add")
        {
            generator.Attach(methodCall.Arguments[0]);
            generator.Attach(" + ");
            generator.Attach(methodCall.Arguments[1]);
            return true;
        }

        return false;
    }
}

//使用
db.Query<User>().Where(i => i.Id.Add(2) < 10).Select(i => new { Id = i.Id.Add(3), Name = i.Name});

int,int?char,char?,double,double?,short,short?,byte,byte?,bool,bool?
decimal,decimal?,DateTime,DateTime?uint,uint?ulong,ulong?,float,float?
ushort,ushort?,string

db.Query<User>().Select(i => i.Id.ToString());

    IsNullOrEmpty,IsNullOrWhiteSpace
    StartsWith,EndsWith
    Contains,Trim
    TrimStart,TrimEnd
    IndexOf,Substring
    ToLower,ToUpper
    Replace,Concat

//Id包含1
db.Query<User>().Select(i => i.Id.ToString().Contains("1"));

    Abs,Acos,Asin
    Atan,Cos,Ceiling
    Floor,Exp,Log10
    Sign,Sin,Sqrt
    Tan,Atan2,Log
    Max,Min,PowRound,
    SinhCosh,TanhTruncate

db.Query<User>().Select(i => Math.Max(i.Id, i.Age));

//db.AddRange()
db.Add(new User(){Id=1,Age=18,CreateDate=DateTime.Now,Name="张三",Tel="1234567879"});
var rowsCount=db.Save();

db.Update(new User(){Id=1,Age=18,CreateDate=DateTime.Now,Name="张三",Tel="1234567879"});
var rowsCount=db.Save();

db.Delete(new User(){Id=1,Age=18,CreateDate=DateTime.Now,Name="张三",Tel="1234567879"});
var rowsCount=db.Save();

var u = db.Query<User>().FirstOrDefault(i => i.Id == 1);
var du = db.Query<User>().FirstOrDefault(i => i.Id == 10);

u.Name = "拜拜拜";

db.Add(new User() { Id = 999, Name = "张三", Age = 18, CreateDay = DateTime.Now, Tel = "12345978" });
db.Add(new User() { Id = 10001, Name = "李四", Age = 19, CreateDay = DateTime.Now, Tel = "12345978" });
db.Update(u);
db.Delete(du);

var rowsCount = db.Save();

//未跟踪
var u = db.Query<User>().FirstOrDefault(i => i.Id == 1);
u.Name = "拜拜拜";

db.Update(u);
db.Save();

//开启跟踪
var u = db.Query<User>().FirstOrDefault(i => i.Id == 1);
db.TrackEntity(u);

u.Name = "拜拜拜";

db.Update(u);
db.Save();

var trans = db.BeginTransaction();

try
{
    db.Add(new User() { Id = 9999999, Name = "张三", Age = 18, CreateDay = DateTime.Now, Tel = "12345978" });
    db.Save();
    trans.Commit();
}
catch
{
    trans.Rollback();
}

//预制数据
for (var i = 0; i < 500000; i++)
{
    db.Add(new User()
    {
        Id = i,
        Age = i,
        Name = "Name" + i,
        CreateDay = DateTime.Now,
        Tel = "123456789"
    });
}

db.Save();

var st = new Stopwatch();

//预热
db.Query<User>().Where(i => i.Id > 0).ToList();

st.Start();
for (var j = 0; j < 5; j++)
{
    db.Query<User>().Where(m => m.Id > j).ToList();
}

st.Stop();
Console.WriteLine("50万条ToList:" + st.ElapsedMilliseconds / 5.0);
//50万条ToList:1399.2毫秒

var st = new Stopwatch();
//预热
db.Query<User>().Where(m => m.Id > 0).Take(1).ToList();

st.Start();
for (var j = 0; j < 5; j++)
{
    db.Query<User>().Where(m => m.Id > j).Take(400000).ToList();
}

st.Stop();
Console.WriteLine("前40万条ToList:" + st.ElapsedMilliseconds / 5.0);
//前40万条ToList:1166.6毫秒

var st = new Stopwatch();
//预热
db.Query<User>().Where(i => i.Id == 0).ToList();

st.Start();
for (var j = 0; j < 5; j++)
{
    for (var i = 0; i < 20000; i++)
    {
        db.Query<User>().Where(m => m.Id == i).ToList();
    }
}

st.Stop();
Console.WriteLine("循环查询2万次,每次查询一条数据:" + st.ElapsedMilliseconds / 5.0);

//循环查询2万次,每次查询一条数据:10519毫秒