AppBoxFuture: Sql存储的ORM查询示例

时间：2021-03-25 15:09:55

  上篇介绍集成第三方Sql数据库时未实现如导航属性、子查询等功能，经过大半个月的努力作者初步实现了这些功能，基本上能满足80%-90%查询需求，特别复杂的查询可以用原生sql来处理，下面分别示例介绍。

Like/In/NotIn

public async Task<object> Query()

{

    var codes = new string[] { "001", "003" };

    var q = new SqlQuery<Entities.OrderItem>();

    q.Where(t => t.ProductCode.In(codes));

    q.AndWhere(t => t.Product.Name.Contains("Pro"));

    return await q.ToListAsync();

}
Select t."OrderId",t."ProductCode",t."Quantity" From "OrderItem" t Left Join "Product" j1 On j1."Code"=t."ProductCode" Where t."ProductCode" In (@p1,@p2) And j1."Name" Like @p3

分页查询

public async Task<object> Query()

{

    var q = new SqlQuery<Entities.OrderItem>();

    q.Where(t => t.Quantity > 0);

    q.OrderBy(t => t.ProductCode);

    q.Skip(2).Take(2);

    var totalRows = await q.CountAsync();

    return await q.ToListAsync();

}
Select Count(*) From "OrderItem" t Where t."Quantity" > @p1

Select t."OrderId",t."ProductCode",t."Quantity" From "OrderItem" t Where t."Quantity" > @p1 Order By t."ProductCode" Offset 2 Limit 2

EntityRef属性自动Left Join

  适用于实体建模时指定了EntityRef(一对一关系)。

public async Task<object> Query()

{

    var q = new SqlQuery<Entities.Customer>();

    q.Where(t => t.City.Name == "无锡");

    return await q.ToListAsync(t => new { t.Id, t.Name, CityName = t.City.Name });

}
Select t."Id",t."Name",j1."Name" "CityName" From "Customer" t Left Join "City" j1 On j1."Code"=t."CityCode" Where j1."Name" = @p1

手工Join

  适用于实体建模时未指定EntityRef关系。

public async Task<object> Query()

{

    var q = new SqlQuery<Entities.Customer>();

    var j = new SqlQueryJoin<Entities.City>();

    q.LeftJoin(j, (cus, city) => cus.CityCode == city.Code);

    q.Where(j, (cus, city) => city.Name == "无锡");

    return await q.ToListAsync(j, (cus, city) => new { cus.Id, cus.Name, CityName = city.Name });

}
Select t."Id",t."Name",j1."Name" "CityName" From "Customer" t Left Join "City" j1 On j1."Code"=t."CityCode" Where j1."Name" = @p1

GroupBy分组

public async Task<object> Query()

{

    var q = new SqlQuery<Entities.OrderItem>();

    q.GroupBy(t => t.ProductCode)

        .Having(t => DbFuncs.Sum(t.Quantity) > 0);

    return await q.ToListAsync(t => new { t.ProductCode, Amount = DbFuncs.Sum(t.Quantity) });

}
Select t."ProductCode",Sum(t."Quantity") "Amount" From "OrderItem" t Group By t."ProductCode" Having Sum(t."Quantity") > @p1

SubQuery子查询

public async Task<object> Query()

{

    var q = new SqlQuery<Entities.OrderItem>();

    var s = new SqlQuery<Entities.Product>();

    q.Where(t => t.ProductCode.In(

        s.Where(p => p.Name.Contains("15")).AsSubQuery(p => p.Code)

        ));

    return await q.ToListAsync();

}
Select t."OrderId",t."ProductCode",t."Quantity" From "OrderItem" t Where t."ProductCode" In (Select t1."Code" From "Product" t1 Where t1."Name" Like @p1)

Eager loading预先加载

  适用于SqlQuery.ToSingleAsync()及ToListAsync()。注意以下示例加载EntitySet属性，会单独生成Sql，所以不建议ToListAsync()时预先加载EntitySet属性，更不建议嵌套预先加载EntitySet。

public async Task<object> Query()

{

    var q = new SqlQuery<Entities.Order>();

    q.Include(order => order.Customer)

        .ThenInclude(customer => customer.City)

     .Include(order => order.Items)

        .ThenInclude(item => item.Product);

    return await q.ToSingleAsync();

}
Select t."Id",t."CustomerId",j1."Id" "Customer.Id",j1."Name" "Customer.Name",j1."CityCode" "Customer.CityCode",j1."Phone" "Customer.Phone",j2."Code" "Customer.City.Code",j2."Name" "Customer.City.Name" From "Order" t Left Join "Customer" j1 On j1."Id"=t."CustomerId" Left Join "City" j2 On j2."Code"=j1."CityCode" Limit 1

Select t."OrderId",t."ProductCode",t."Quantity",j1."Code" "Product.Code",j1."Name" "Product.Name",j1."Unit" "Product.Unit" From "OrderItem" t Left Join "Product" j1 On j1."Code"=t."ProductCode" Where t."OrderId" = @p1

暂未实现Explicit loading(显式加载);

暂不支持Lazy loading(延迟加载)

更新同时返回值 [2020-02-19更新]

  适用于扣减库存同时返回扣减后的值，以判断是否超出库存数量。

public async Task Subtract(int itemId, int amount)

{

    using var conn = await DataStore.Default.OpenConnectionAsync();

    using var txn = conn.BeginTransaction();

    var upcmd = new SqlUpdateCommand<Entities.TestStocks>();

    upcmd.Update(t => t.Amount = t.Amount - amount);

    upcmd.Where(t => t.ItemId == itemId);

    //var outs = upcmd.Output(t => new { t.ItemId, t.Amount });

    var outs = upcmd.Output(t => t.Amount); //在下句执行前指定返回值

    await DataStore.Default.ExecCommandAsync(upcmd, txn); //显式指定事务执行

    if (outs.Count != 1 || outs[0] < 0) //在执行后检查返回结果

        throw new Exception("库存不足");

    txn.Commit(); //成功递交事务

}
Update "sys.TestStocks" t Set "Amount" = "Amount" + @p1 Where t."ItemId" = @p1 RETURNING "Amount"

小结

  GitHub上的运行时已更新可安装测试，作者下一步重点是实现独立的不依赖内置存储的版本，并且实现模型包的导入与导出功能。另一边码代码一边码文实属不易，作者需要您的支持请您多多点赞推荐！

相关文章