AppBoxFuture的ORM查询示例，如何针对高效检索？
摘要：上篇介绍集成第三方Sql数据库时未实现如导航属性、子查询等功能，经过大半个月的努力作者初步实现了这些功能，基本上能满足80% 90%查询需求，特别复杂的查询可以用原生sql来处理，下面分别示例介绍。 LikeInNotIn 分页查询 E
上篇介绍集成第三方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关系。