FreeSql
FreeSql copied to clipboard
dotnetcore
指定层级自动加载导航属性，并支持根据从表条件过滤主表数据
目前有个项目基于 Dapper 以及 EF 拓展开发，查询时支持指定层级对导航属性进行贪婪加载，并且前端支持通过 [Key, Condition] 的方式自从表开始过滤数据，不知道现在有没有好的实现方案。
指定贪婪加载层级
Order(订单) 与 Orgnization(组织) 一对一 Order(订单) 与 OrderItem(订单行记录) 一对多 OrderItem(订单行记录) 与 Material(物料) 一对一 Material(物料) 与 Unit(单位) 一对多
以下为模拟测试数据，为实现加载单据下从表数据，需要一直向下追加 Include/IncludeMany，并且当碰到从表是一对一关系无法继续向下加载从表数据。
当前项目实现的效果是比如 InludeLevel(int level)，填写 3 后，上述会查询到 Unit 这一层。2 则会查询到仅 Material 这一层。
void Main()
{
IFreeSql freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build();
freeSql.Aop.CurdBefore += (s, e) =>
{
e.Sql.Dump();
};
var orgnization = new Orgnization { Code = "C001" };
freeSql.Insert(orgnization).ExecuteAffrows();
var materials = new[]
{
new Material{Code="TEST1",Units=new List<Unit>{new Unit{Code = "KG"}}},
new Material{Code="TEST2",Units=new List<Unit>{new Unit{Code = "KG"}}}
};
var repo1 = freeSql.GetGuidRepository<Material>();
repo1.DbContextOptions.EnableCascadeSave = true;
repo1.Insert(materials);
var order = new Order
{
Code = "X001",
OrgnizationId = orgnization.Id,
OrderItems = new List<OrderItem>
{
new OrderItem{ ItemCode = "01", MaterialId = materials[0].Id },
new OrderItem { ItemCode = "02", MaterialId = materials[1].Id },
}
};
var repo2 = freeSql.GetGuidRepository<Order>();
repo2.DbContextOptions.EnableCascadeSave = true;
repo2.Insert(order);
// 这里不知道该怎么继续加载 Unit
var list = repo2.Where(t => t.Code.StartsWith("X"))
.Include(t => t.Orgnization)
.IncludeMany(t => t.OrderItems, then => then.Include(t2 => t2.Material))
.ToList();
list.Dump();
}
public class Order
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid OrgnizationId { get; set; }
[Navigate(nameof(OrgnizationId))]
public Orgnization Orgnization { get; set; }
[Navigate(nameof(OrderItem.OrderId))]
public List<OrderItem> OrderItems { get; set; }
}
public class OrderItem
{
public Guid Id { get; set; }
public string ItemCode { get; set; }
public Guid MaterialId { get; set; }
public Guid OrderId { get; set; }
[Navigate(nameof(MaterialId))]
public Material Material { get; set; }
}
public class Orgnization
{
public Guid Id { get; set; }
public string Code { get; set; }
}
public class Material
{
public Guid Id { get; set; }
public string Code { get; set; }
[Navigate(nameof(Unit.MaterialId))]
public List<Unit> Units { get; set; }
}
public class Unit
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid MaterialId { get; set; }
}
过滤数据
目前，接口还支持通过从表过滤数据，例如查询 Order 时，条件为 ["OrderItems.Material.Code": {"=", "TEST1"}]，则会查询到所有存在该物料的单据信息。
// 这里不知道该怎么继续加载 Unit
var list1 = repo2.Where(t => t.OrderItems.Any(t => t.Material.Code == "TEST1"))
.Include(t => t.Orgnization)
.IncludeMany(t => t.OrderItems, then => then.Include(t2 => t2.Material))
.ToList();
像这种主从关系的表以及查询业务还有很多，不知道如何迁移到 FreeSql 并实现该这些业务。:cry:
var list = repo2.Where(t => t.Code.StartsWith("X"))
.Include(t => t.Orgnization)
.IncludeMany(t => t.OrderItems, then => then.IncludeMany(t2 => t2.Material.Units))
.ToList();
var list = repo2.Where(t => t.Code.StartsWith("X")) .Include(t => t.Orgnization) .IncludeMany(t => t.OrderItems, then => then.IncludeMany(t2 => t2.Material.Units)) .ToList();
在写扩展方法的时候发现好像有限制：
// 可以获取到数据
freeSql.Select<Orgnization>().IncludeMany(t => t.Company.Departments).ToList().Dump();
// 只能获取到 Orgnization
freeSql.Select<Order>().IncludeMany(t => t.Orgnization.Company.Departments).ToList().Dump();
看了源码好像现在不支持无限层级的加载。:cry:
代码有些乱，我单独把这部分列出来吧：
void Main()
{
IFreeSql freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.UseNoneCommandParameter(true)
.Build();
freeSql.Aop.CurdBefore += (s, e) =>
{
e.Sql.Dump();
};
var company = new Company { Id = Guid.NewGuid(), Code = "CO001" };
var department = new Department { Id = Guid.NewGuid(), Code = "D001", CompanyId = company.Id };
var orgnization = new Orgnization { Code = "C001", CompanyId = company.Id };
freeSql.Insert(company).ExecuteAffrows();
freeSql.Insert(orgnization).ExecuteAffrows();
freeSql.Insert(department).ExecuteAffrows();
var materials = new[]
{
new Material{Code="TEST1",Units=new List<Unit>{new Unit{Code = "KG"}}},
new Material{Code="TEST2",Units=new List<Unit>{new Unit{Code = "KG"}}}
};
var repo1 = freeSql.GetGuidRepository<Material>();
repo1.DbContextOptions.EnableCascadeSave = true;
repo1.Insert(materials);
var order = new Order
{
Code = "X001",
OrgnizationId = orgnization.Id,
OrderItems = new List<OrderItem>
{
new OrderItem{ ItemCode = "01", MaterialId = materials[0].Id },
new OrderItem { ItemCode = "02", MaterialId = materials[1].Id },
}
};
var repo2 = freeSql.GetGuidRepository<Order>();
repo2.DbContextOptions.EnableCascadeSave = true;
repo2.Insert(order);
// 可以完整加载数据
freeSql.Select<Orgnization>().IncludeMany(t => t.Company.Departments).ToList().Dump();
// 只能查询到Orgnization
freeSql.Select<Order>().IncludeMany(t => t.Orgnization.Company.Departments).ToList().Dump();
//freeSql.Select<Order>().IncludeMany(t => t.OrderItems, then => then.IncludeMany(t => t.Material.Units)).ToList().Dump();
}
public class Order
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid OrgnizationId { get; set; }
[Navigate(nameof(OrgnizationId))]
public Orgnization Orgnization { get; set; }
[Navigate(nameof(OrderItem.OrderId))]
public List<OrderItem> OrderItems { get; set; }
}
public class OrderItem
{
public Guid Id { get; set; }
public string ItemCode { get; set; }
public Guid MaterialId { get; set; }
public Guid OrderId { get; set; }
[Navigate(nameof(MaterialId))]
public Material Material { get; set; }
}
public class Orgnization
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid CompanyId { get; set; }
[Navigate(nameof(CompanyId))]
public Company Company { get; set; }
}
public class Company
{
public Guid Id { get; set; }
public string Code { get; set; }
[Navigate(nameof(Department.CompanyId))]
public List<Department> Departments { get; set; }
}
public class Department
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid CompanyId { get; set; }
}
public class Material
{
public Guid Id { get; set; }
public string Code { get; set; }
[Navigate(nameof(Unit.MaterialId))]
public List<Unit> Units { get; set; }
}
public class Unit
{
public Guid Id { get; set; }
public string Code { get; set; }
public Guid MaterialId { get; set; }
}
指定层级加载，写了个 IncludeLevel 的扩展方法，这部分好了就应该可以用起来了：
public class TableRefTree
{
public int Level { get; set; }
public TableInfo TableInfo { get; set; }
public TableRef TableRef { get; set; }
public List<TableRefTree> Subs { get; set; }
public static TableRefTree GetTableRefTree<T1>(ISelect<T1> select, int maxLevel)
{
var orm = select.GetType().GetField("_orm").GetValue(select) as IFreeSql;
var tableInfo = orm.CodeFirst.GetTableByEntity(typeof(T1));
var tableRefs = tableInfo.Properties.Select(a => tableInfo.GetTableRef(a.Key, false)).Where(a => a != null).ToList();
var tree = new TableRefTree()
{
Level = 1,
TableInfo = tableInfo,
};
tree.Subs = GetTableRefTree(orm, tree, maxLevel).ToList();
return tree;
}
public static IEnumerable<TableRefTree> GetTableRefTree(IFreeSql orm, TableRefTree tree, int maxLevel)
{
if (tree.Level > maxLevel) yield break;
var tableRefs = tree.TableInfo.Properties.Select(a => tree.TableInfo.GetTableRef(a.Key, false)).Where(a => a != null).ToList();
foreach (var tableRef in tableRefs)
{
var tableInfo = orm.CodeFirst.GetTableByEntity(tableRef.RefEntityType);
var sub = new TableRefTree()
{
Level = tree.Level + 1,
TableInfo = tableInfo,
TableRef = tableRef,
};
sub.Subs = GetTableRefTree(orm, sub, maxLevel).ToList();
yield return sub;
}
}
}
public static class FreeSqlExtensions
{
public static ISelect<T1> IncludeLevel<T1>(this ISelect<T1> select, int level)
{
var tree = TableRefTree.GetTableRefTree(select, level);
return select.IncludeLevel(level, tree);
}
private static ISelect<T1> IncludeLevel<T1>(this ISelect<T1> select, int level, TableRefTree tree, ParameterExpression parameterExpression = null, MemberExpression bodyExpression = null)
{
var includeMethod = select.GetType().GetMethod("Include");
var includeManyMethod = select.GetType().GetMethod("IncludeMany");
parameterExpression ??= Expression.Parameter(tree.TableInfo.Type, "t");
foreach (var sub in tree.Subs)
{
switch (sub.TableRef.RefType)
{
case TableRefType.ManyToOne:
case TableRefType.OneToOne:
{
var body = bodyExpression == null ? Expression.Property(parameterExpression, sub.TableRef.Property) : Expression.Property(bodyExpression, sub.TableRef.Property);
if (sub.Subs.Count == 0)
{
var funcType = typeof(Func<,>).MakeGenericType(parameterExpression.Type, sub.TableRef.RefEntityType);
var navigateSelector = Expression.Lambda(funcType, body, parameterExpression);
includeMethod.MakeGenericMethod(sub.TableRef.RefEntityType).Invoke(select, new object[] { navigateSelector });
}
else
{
select.IncludeLevel(level, sub, parameterExpression, body);
}
}
break;
case TableRefType.ManyToMany:
case TableRefType.OneToMany:
{
var body = bodyExpression == null ? Expression.Property(parameterExpression, sub.TableRef.Property) : Expression.Property(bodyExpression, sub.TableRef.Property);
object then = null;
if (sub.Subs.Count > 0)
{
//var thenSelectType = select.GetType().GetGenericTypeDefinition().MakeGenericType(sub.TableRef.RefEntityType);
var thenSelectType = typeof(ISelect<>).MakeGenericType(sub.TableRef.RefEntityType);
var thenType = typeof(Action<>).MakeGenericType(thenSelectType);
var thenParameter = Expression.Parameter(thenSelectType, "then");
typeof(FreeSqlExtensions).GetMethod("IncludeLevel").Dump();
typeof(FreeSqlExtensions).GetMethods().Dump();
var thenMethod = typeof(FreeSqlExtensions).GetMethod("IncludeLevel").MakeGenericMethod(sub.TableRef.RefEntityType);
var thenLevelConst = Expression.Constant(level - sub.Level + 1);
var thenBody = Expression.Call(null, thenMethod, thenParameter, thenLevelConst);
var thenExpression = Expression.Lambda(thenType, thenBody, thenParameter);
then = thenExpression.Compile();
}
var funcType = typeof(Func<,>).MakeGenericType(parameterExpression.Type, typeof(IEnumerable<>).MakeGenericType(sub.TableRef.RefEntityType));
var navigateSelector = Expression.Lambda(funcType, body, parameterExpression);
includeManyMethod.MakeGenericMethod(sub.TableRef.RefEntityType).Invoke(select, new object[] { navigateSelector, then });
}
break;
}
}
return select;
}
}
另外就差个使用从表过滤数据了，还要再研究下。以上代码仅供参考，不知道会不会有什么未知问题。
另外一个功能我想基于 DynamicFilterCustom 实现，但是现在拿不到 ISelect<T1> 对象，不知道该怎么扩展合适了，叶老板有时间的时候也帮忙看下吧：:cry:
// 使用方式
DynamicFilterInfo dynamicFilterInfo = new DynamicFilterInfo
{
Operator = DynamicFilterOperator.Custom,
Field = $"{nameof(DynamicFilterMyCustom.Include)} {typeof(DynamicFilterMyCustom).FullName}",
Value = JsonConvert.SerializeObject(new DynamicFilterInfo { Operator = DynamicFilterOperator.Eq, Field = "OrderItems.Material.Units.Code", Value = "KG" }),
};
//freeSql.Select<Order>().WhereDynamicFilter(dynamicFilterInfo).ToList();
// 实现以下效果
var list = freeSql.Select<Order>().Where(t=>t.OrderItems.Where(item=>item.Material.Units.Where(unit=>unit.Code == "KG").Any()).Any()).ToList();
list.Dump();
public class DynamicFilterMyCustom
{
[DynamicFilterCustom]
public static string Include(string value) => throw new NotImplementedException("无法获取到 ISelect<T1> 对象，无法组装表达式");
}
// 可以获取到数据
freeSql.Select<Orgnization>().IncludeMany(t => t.Company.Departments).ToList().Dump();
// 只能获取到 Orgnization
freeSql.Select<Order>().IncludeMany(t => t.Orgnization.Company.Departments).ToList().Dump();
问题原因是 ToList(includeNestedMembers: false)，没有查到第三级对象 Company，触发 ToList(true) 就没有这个问题了。
如何触发？
freeSql.Select<Order>()
.Include(t => t.Orgnization.Company) //此行是关键，下个版本优化这行指定
.IncludeMany(t => t.Orgnization.Company.Departments).ToList().Dump();
[DynamicFilterCustom]
public static string MyRawSql(object sender, string value) => value;
[DynamicFilterCustom]
public static string MyRawSql2(string value) => value;
下个版本这两种定义都支持，object sender 就是 ISelect 对象。
我现在的使用方式发现一个问题，例如：
var filter2 = new DynamicFilterInfo
{
Field = "Material.Code",
Operator = DynamicFilterOperator.Eq,
Value = "TEST1",
};
freeSql.Select<Order>().Where(t1 => t1.OrderItems.AsSelect().WhereDynamicFilter(filter2).Any()).ToList().Dump();
打印SQL为：
SELECT a."Id", a."Code", a."OrgnizationId"
FROM "Order" a
WHERE (exists(SELECT 1
FROM "OrderItem" a
LEFT JOIN "Material" a__Material ON a__Material."Id" = a."MaterialId"
WHERE (a__Material."Code" = 'TEST1') AND (a."OrderId" = a."Id")
limit 0,1))
其中 a."OrderId" = a."Id" 会导致无法正确获取关联结果。(逃