/// <summary>
///
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
public static DynamicFilterInfo ToDynamicFilter(this QueryPageOptions option)
{
var ret = new DynamicFilterInfo()
{
Filters = new List <DynamicFilterInfo>()
};
foreach (var filter in option.Filters.Concat(option.Searchs))
{
var item = new DynamicFilterInfo()
{
Filters = new List <DynamicFilterInfo>()
};
var actions = filter.GetFilterConditions();
foreach (var f in actions)
{
item.Logic = f.FilterLogic.ToDynamicFilterLogic();
item.Filters.Add(new DynamicFilterInfo()
{
Field = f.FieldKey,
Value = f.FieldValue,
Operator = f.FilterAction.ToDynamicFilterOperator()
});
}
ret.Filters.Add(item);
}
return(ret);
}
public async Task <DynamicFilterInfo> GetPermissionDynamicFilterAsync(string permissionId, string moduleKey)
{
var conditions = await GetPermissionConditionsAsync(permissionId);
var currentConditions = conditions.Where(x => x.Code == moduleKey);
var cons = new List <DynamicFilterInfo>();
foreach (var item in currentConditions)
{
var conditionStr = item.Condition?
.Replace("{UserId}", _authUser.Id)
.Replace("{UserName}", _authUser.UserName)
.Replace("{UserGroupId}", _authUser.GroupId)
.Replace("{UserPermissionId}", _authUser.PermissionId);
var dyCons = Newtonsoft.Json.JsonConvert.DeserializeObject <List <DynamicFilterInfo> >(conditionStr);
foreach (var it in dyCons)
{
cons.Add(it);
}
}
var condition = new DynamicFilterInfo();
condition.Logic = DynamicFilterLogic.Or;
condition.Filters = cons;
return(condition);
}
public async Task <IActionResult> DynamicQueryAsync([FromQuery] DynamicFilterInfo dynamicFilter)
{
var result = await _dataBaseOptionService.DynamicQueryAsync(dynamicFilter);
return(Ok(new ResponseResult {
Result = result
}));
}
public static ISelect <T1> Where <T1>(this ISelect <T1> select, string field, DynamicFilterOperator filterOperator, object value)
{
var filter = new DynamicFilterInfo {
Field = field, Operator = filterOperator, Value = value
};
Expression <Func <T1, bool> > exp = GetWhereExpression(select, filter);
return(select.Where(exp));
}
public void DynamicLinqTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
freeSql.Insert(
Enumerable.Range(1, 100)
.Select(i => new Topic {
Title = $"new topic {i}", Clicks = 100
})
.ToList())
.ExecuteAffrows();
// 常规用法
var list1 = freeSql.Select <Topic>()
.Where(t => t.Title.StartsWith("new topic 1"))
.ToList();
// 借助 DynamicExpressionParser
var list2 = freeSql.Select <Topic>()
.WhereDynamicLinq("Title.StartsWith(\"new topic 1\")")
.ToList();
// 拓展 DynamicFilter
var dynmaicFilterInfo = new DynamicFilterInfo
{
Field = $"{nameof(DynamicLinqCustom.DynamicLinq)} {typeof(DynamicLinqCustom).FullName},{typeof(DynamicLinqCustom).Assembly.FullName}",
Operator = DynamicFilterOperator.Custom,
Value = "Title.StartsWith(\"new topic 1\")",
};
var list3 = freeSql.Select <Topic>()
.WhereDynamicFilter(dynmaicFilterInfo)
.ToList();
}
}
/// <summary>
/// 生成Where子句的DynamicFilterInfo对象
/// </summary>
/// <param name="option"></param>
/// <param name="isSerach"></param>
/// <returns></returns>
private DynamicFilterInfo?MakeDynamicFilterInfo(QueryPageOptions option, out bool isSerach)
{
var filters = new List <DynamicFilterInfo>();
object?searchModel = option.SearchModel;
Type type = searchModel.GetType();
var instance = Activator.CreateInstance(type);
if (string.IsNullOrEmpty(option.SearchText))
{
//生成高级搜索子句
//TODO : 支持更多类型
foreach (var propertyinfo in type.GetProperties().Where(a => a.PropertyType == typeof(string) || a.PropertyType == typeof(int)).ToList())
{
if (propertyinfo.GetValue(searchModel) != null && !propertyinfo.GetValue(searchModel).Equals(propertyinfo.GetValue(instance)))
{
string propertyValue = propertyinfo.GetValue(searchModel).ToString();
if (propertyinfo.PropertyType == typeof(int) && !IsNumeric(propertyValue))
{
continue;
}
filters.Add(new DynamicFilterInfo()
{
Field = propertyinfo.Name,
Operator = propertyinfo.PropertyType == typeof(int) ? DynamicFilterOperator.Equal : DynamicFilterOperator.Contains,
Value = propertyinfo.PropertyType == typeof(int) ? Convert.ToInt32(propertyValue) : propertyValue,
});
}
}
}
else
{
//生成默认搜索子句
//TODO : 支持更多类型
foreach (var propertyinfo in type.GetProperties().Where(a => a.PropertyType == typeof(string) || a.PropertyType == typeof(int)).ToList())
{
if (propertyinfo.PropertyType == typeof(int) && !IsNumeric(option.SearchText))
{
continue;
}
filters.Add(new DynamicFilterInfo()
{
Field = propertyinfo.Name,
Operator = propertyinfo.PropertyType == typeof(int) ? DynamicFilterOperator.Equal : DynamicFilterOperator.Contains,
Value = propertyinfo.PropertyType == typeof(int) ? Convert.ToInt32(option.SearchText) : option.SearchText,
});
}
}
if (option.Filters.Any())
{
foreach (var item in option.Filters)
{
var filter = item.GetFilterConditions().First();
var filterOperator = DynamicFilterOperator.Contains;
switch (filter.FilterAction)
{
case FilterAction.Contains:
filterOperator = DynamicFilterOperator.Contains;
break;
case FilterAction.NotContains:
filterOperator = DynamicFilterOperator.NotContains;
break;
case FilterAction.NotEqual:
filterOperator = DynamicFilterOperator.NotEqual;
break;
case FilterAction.Equal:
filterOperator = DynamicFilterOperator.Equal;
break;
}
filters.Add(new DynamicFilterInfo()
{
Field = filter.FieldKey,
Operator = filterOperator,
Value = filter.FieldValue,
});
}
}
if (filters.Any())
{
DynamicFilterInfo dyfilter = new DynamicFilterInfo()
{
Logic = string.IsNullOrEmpty(option.SearchText) ? DynamicFilterLogic.And : DynamicFilterLogic.Or,
Filters = filters
};
isSerach = true;
return(dyfilter);
}
isSerach = false;
return(null);
}
private static Expression GetWhereExpression <T1>(ISelect <T1> select, TableRefTree deepest, DynamicFilterInfo filterInfo, string[] properties, Expression body, ref int manyLevel)
{
while (deepest.Parent != null && deepest.TableRef.RefType != TableRefType.ManyToMany && deepest.TableRef.RefType != TableRefType.OneToMany)
{
deepest = deepest.Parent;
}
manyLevel++;
if (manyLevel == 1)
{
filterInfo = new DynamicFilterInfo
{
Field = string.Join(".", properties.Skip(deepest.Level - 1)),
Operator = filterInfo.Operator,
Value = filterInfo.Value,
};
deepest = deepest.Parent;
return(GetWhereExpression(select, deepest, filterInfo, properties, body, ref manyLevel));
}
if (body == null)
{
body = Expression.Parameter(deepest.TableInfo.Type, $"t{deepest.Level}");
var sub = deepest;
do
{
sub = sub.Subs.First();
body = Expression.Property(body, sub.TableRef.Property);
} while (sub.TableRef.RefType != TableRefType.ManyToMany && sub.TableRef.RefType != TableRefType.OneToMany);
var selectMethod = typeof(FreeSqlGlobalExtensions).GetMethods().First(a => a.Name == "AsSelect" && a.ContainsGenericParameters && a.GetParameters().Count() == 1).MakeGenericMethod(sub.TableRef.RefEntityType);
body = Expression.Call(null, selectMethod, body);
var asMethod = typeof(ISelect <>).MakeGenericType(sub.TableRef.RefEntityType).GetMethod("As");
var constExpression = Expression.Constant($"t{deepest.Level}");
body = Expression.Call(body, asMethod, constExpression);
var whereDynamicFilterMethod = typeof(ISelect0 <,>).MakeGenericType(typeof(ISelect <>).MakeGenericType(sub.TableRef.RefEntityType), sub.TableRef.RefEntityType).GetMethod("WhereDynamicFilter");
body = Expression.Call(body, whereDynamicFilterMethod, Expression.Constant(filterInfo));
var anyMethod = typeof(ISelect0 <,>).MakeGenericType(typeof(ISelect <>).MakeGenericType(sub.TableRef.RefEntityType), sub.TableRef.RefEntityType).GetMethod("Any");
body = Expression.Call(body, anyMethod);
deepest = deepest.Parent;
}
else
{
Expression subBody = Expression.Parameter(deepest.TableInfo.Type, $"t{deepest.Level}");
var sub = deepest;
do
{
sub = sub.Subs.First();
subBody = Expression.Property(subBody, sub.TableRef.Property);
} while (sub.TableRef.RefType != TableRefType.ManyToMany && sub.TableRef.RefType != TableRefType.OneToMany);
var selectMethod = typeof(FreeSqlGlobalExtensions).GetMethods().First(a => a.Name == "AsSelect" && a.ContainsGenericParameters && a.GetParameters().Count() == 1).MakeGenericMethod(sub.TableRef.RefEntityType);
subBody = Expression.Call(null, selectMethod, subBody);
var anyMethod = typeof(ISelect <>).MakeGenericType(sub.TableRef.RefEntityType).GetMethod("Any");
var funcType = typeof(Func <,>).MakeGenericType(sub.TableRef.RefEntityType, typeof(bool));
var parameterBody = Expression.Parameter(sub.TableInfo.Type, $"t{sub.Level}");
var lambda = Expression.Lambda(funcType, body, parameterBody);
body = Expression.Call(subBody, anyMethod, lambda);
deepest = deepest.Parent;
}
if (deepest == null)
{
var funcType = typeof(Func <,>).MakeGenericType(typeof(T1), typeof(bool));
var parameterBody = Expression.Parameter(typeof(T1), "t1");
return(Expression.Lambda(funcType, body, parameterBody));
}
else
{
return(GetWhereExpression(select, deepest, filterInfo, properties, body, ref manyLevel));
}
}
public static Expression <Func <T1, bool> > GetWhereExpression <T1>(ISelect <T1> select, DynamicFilterInfo filter)
{
var properties = filter.Field.Split('.');
var tree = TableRefTree.GetTableRefTree(select, properties.Length, properties);
// 检索
var treeList = GetTreeList(tree).ToList();
var deepest = treeList.Last();
var collectionNodes = treeList.Where(a => a.TableRef.RefType == TableRefType.OneToMany || a.TableRef.RefType == TableRefType.ManyToMany).ToList();
if (deepest.Level != properties.Length)
{
throw new Exception($"当前类型{typeof(T1)}导航属性{filter.Field}匹配检索失败");
}
if (collectionNodes.Count == 0)
{
throw new Exception($"当前类型{typeof(T1)}导航属性{filter.Field}不包含{TableRefType.OneToMany}或者{nameof(TableRefType.ManyToMany)}关系");
}
var selectMethod = typeof(FreeSqlGlobalExtensions).GetMethods().First(a => a.ContainsGenericParameters && a.GetParameters().Count() == 1);
var parameterExpression = Expression.Parameter(typeof(T1), "t");
var manyLevel = 0;
var exp = GetWhereExpression(select, deepest, filter, properties, null, ref manyLevel);
return(exp as Expression <Func <T1, bool> >);
}
public void WhereByPropertyTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
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);
// 根据导航属性过滤数据
//var list1 = freeSql.Select<Order>().Where(t => t.OrderItems.Any(t1 => t1.Material.Units.Any(t2 => t2.Code == "KG"))).ToList();
var filterInfo1 = new DynamicFilterInfo
{
Field = "Code",
Operator = DynamicFilterOperator.Eq,
Value = "KG",
};
var list1 = freeSql.Select <Order>().Where(t => t.OrderItems.Any(t1 => t1.Material.Units.AsSelect().WhereDynamicFilter(filterInfo1).Any())).ToList();
// 导航属性如果是 OneToOne 或者 ManyToOne 默认支持
var filterInfo2 = new DynamicFilterInfo
{
Field = "Orgnization.Company.Code",
Operator = DynamicFilterOperator.Eq,
Value = "CO001",
};
//var list2 = freeSql.Select<Order>().Where(t => t.Orgnization.Company.Code == "CO001").ToList();
var list2 = freeSql.Select <Order>().WhereDynamicFilter(filterInfo2).ToList();
// 实现效果 OrderItems.Material.Units.Code == "KG"
var list3 = freeSql.Select <Order>().Where("OrderItems.Material.Units.Code", DynamicFilterOperator.Eq, "KG").ToList();
// 实现效果 OrderItems.Material.Code == "TEST1"
// Error 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))
var list4 = freeSql.Select <Order>().Where("OrderItems.Material.Code", DynamicFilterOperator.Eq, "TEST1").ToList();
// 拓展 DynamicFilter
var dynmaicFilterInfo = new DynamicFilterInfo
{
Field = $"{nameof(DynamicLinqCustom.WhereNavigation)} {typeof(DynamicLinqCustom).FullName},{typeof(DynamicLinqCustom).Assembly.FullName}",
Operator = DynamicFilterOperator.Custom,
Value = JsonConvert.SerializeObject(new DynamicFilterInfo {
Field = "OrderItems.Material.Units.Code", Operator = DynamicFilterOperator.Eq, Value = "KG"
}),
};
var list5 = freeSql.Select <Order>()
.WhereDynamicFilter(dynmaicFilterInfo)
.ToList();
}
}
/// <summary>
///
/// </summary>
/// <param name="dynamicFilter"></param>
/// <returns></returns>
public async Task <ResponseResult <IList <XXXAo> > > DynamicQueryAsync(DynamicFilterInfo dynamicFilter)
{
return(await Task.FromResult(new ResponseResult <IList <XXXAo> > {
}));
}
/// <summary>
///
/// </summary>
/// <param name="dynamicFilter"></param>
/// <returns></returns>
public async Task <ResponseResult <IList <XXXAo> > > DynamicQueryAsync(DynamicFilterInfo dynamicFilter)
{
throw new System.NotImplementedException();
}