AppBoxFuture: Sql存储的ORM查询示例 _查询

生也有涯，知也无涯。这篇文章主要讲述AppBoxFuture: Sql存储的ORM查询示例相关的知识，希望能为你提供帮助。
??上篇介绍集成第三方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" > @p1Select 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 1Select 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