指定层级自动加载导航属性，并支持根据从表条件过滤主表数据

[hd2y]

目前有个项目基于 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:

[2881099]

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();

[hd2y]

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:

[2881099]

可以无限级，把测试实体类和插入的测试数据，在一楼更新一下。

[hd2y]

代码有些乱，我单独把这部分列出来吧：

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; }
}

[hd2y]

指定层级加载，写了个 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;
    }
}

另外就差个使用从表过滤数据了，还要再研究下。以上代码仅供参考，不知道会不会有什么未知问题。

[hd2y]

另外一个功能我想基于 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> 对象，无法组装表达式");
}

[2881099]

    // 可以获取到数据
    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();

[2881099]

        [DynamicFilterCustom]
        public static string MyRawSql(object sender, string value) => value;

        [DynamicFilterCustom]
        public static string MyRawSql2(string value) => value;

下个版本这两种定义都支持，object sender 就是 ISelect 对象。

[hd2y]

我现在的使用方式发现一个问题，例如：

    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" 会导致无法正确获取关联结果。(逃