基于System.Linq.Dynamic.Core实现动态Join关联与投影查询

核心需求 #

• 借助System.Linq.Dynamic.Core对IQueryable实体执行动态Join关联操作
• 基于表间关系在主表与关联表之间创建动态关联，最终将查询结果投影为特定结构的动态类型
• 仅从主表中选择与ViewModel匹配的指定列，所有操作需在运行时动态实现

现有示例代码 #

private IQueryable FilterPropertiesBasedOnViewModel(IQueryable data, Type modelType, Type viewModelType, Dictionary<string, (Type, Type)> modelTypeMapping)
{
    var viewModelProperties = viewModelType.GetProperties().Select(p => p.Name).ToList();
    var modelProperties = modelType.GetProperties().Where(p => viewModelProperties.Contains(p.Name)).ToList();

    if (modelProperties.Any())
    {
        var selectExpression = new List<string>();

        foreach (var propertyInfo in modelProperties)
        {
            if (Nullable.GetUnderlyingType(propertyInfo.PropertyType) == typeof(Guid))
            {
                var (relatedType, relatedKeyType) = modelTypeMapping[propertyInfo.Name];
                var relatedTableName = relatedType.Name;

                var outerKeySelector = $"it.{propertyInfo.Name}?";
                var innerKeySelector = $"Oid";
                var resultSelector = $"new(outer.{propertyInfo.Name} as {propertyInfo.Name})";

                // Apply Join using System.Linq.Dynamic.Core
                data = data.Join(
                    GetTypedDbSet(relatedType),
                    outerKeySelector,
                    innerKeySelector,
                    resultSelector
                );
            }

            // Add the property to the select expression
            selectExpression.Add($"{propertyInfo.Name} as {propertyInfo.Name}");
        }

        var finalSelectExpression = string.Join(", ", selectExpression);

        // Project the data to include only common properties
        return data.Select($"new ({finalSelectExpression})");
    }
    else
    {
        return data;
    }
}

期望生成的SQL语句 #

select 
    b.Name,
    a.Name as Account,
    c.Name as Country,
    p.Name as Province,
    sp.Name as SubProvince
from 
    dbo.Branch b
left join 
    dbo.Account a on a.Oid = b.Account
left join 
    dbo.Country c on c.Oid = b.Country 
left join 
    dbo.Province p on p.Oid = b.Province
left join 
    dbo.SubProvince sp on sp.Oid = b.SubProvince

内容的提问来源于stack exchange，提问作者Jabagh Anjouk