强大的动态 LINQ 库

  • 强大的动态 LINQ 库已关闭评论
  • 3 次浏览
  • A+
所属分类：.NET技术
摘要

dynamic-linq.net，这个库大家都用过，简单说就是使用字符串编写查询表达式的，非常强大，因为字符串是可以运行时拼接的，所以就可以在运行时动态的构造查询，非常适合框架开发和需要灵活性的场景。
我用它解决了在原生代码非常困难且耗时（需要动态构造表达式）的功能，真正的直达痛点。
它dotnet下的命名空间是System.Linq.Dynamic.Core，现在就介绍下各种复杂的写法示例和注意事项。
便宜好用的国外VPS推荐

dynamic-linq.net，这个库大家都用过，简单说就是使用字符串编写查询表达式的，非常强大，因为字符串是可以运行时拼接的，所以就可以在运行时动态的构造查询，非常适合框架开发和需要灵活性的场景。
我用它解决了在原生代码非常困难且耗时（需要动态构造表达式）的功能，真正的直达痛点。
它dotnet下的命名空间是System.Linq.Dynamic.Core，现在就介绍下各种复杂的写法示例和注意事项。

1. IQueryable<>支持

//context是EFCore的DbContext context.Customers.Where("City == "Paris""); context.Customers.Where("City == @0 and Age > @1", "Paris", 50); context.Customers.Select("new { City, CompanyName }"); context.Customers.OrderBy("City, CompanyName"); context.Customers.OrderBy("City, CompanyName desc");

2. IEnumerable<>支持

//list是IEnumerable<>类型 list.AsQueryable().Where("City == "Paris""); list.AsQueryable().Where("City == @0 and Age > @1", "Paris", 50); list.AsQueryable().Select("new { City, CompanyName }"); list.AsQueryable().OrderBy("City, CompanyName"); list.AsQueryable().OrderBy("City, CompanyName desc");

3. 参数用@符号加索引，如上文示例

4. 支持几乎所有的IQueryable扩展方法

如Where、Select、OrderBy、Any、Count、Join，参考 附1。

5. 类型转换

类型转换非常有用，可以对字符串类型的值转成你想要的类型，以满足更灵活场景的动态特性。
也支持复杂类型的转换，参考 附1中的Cast and OfType

//postgres数据库会转成: "Value"::int > 0 var count = qry.Count("As(Value, "int?") >0");

6. 复杂条件语句

简单的查询语句都很记住，复杂的查询条件也支持，下面的代码示例，User实体中有个叫Roles的集合导航属性，Role实体中有个Name字符串属性（数据库中对应Name字段），继续参考 附1。

var dynamicResult = context.Users.Where("Roles.Any(Name.Contains(@0))", search);

7. 支持dotnet中基本的常用写法

如 关键字、操作符、标识、常量、类型和System.Math、Convert等方法，全部区分大小写，如在C#下用true而不是True，是DateTime而不是datetime。
需要注意的地方：

  1. &&和and等效，||和or等效
  2. 字符需要单引号，字符串需要双引号，而不是按数据库中的语义
  3. Nullable同样也是类型后面加问号，如 int?
  4. it 很特殊，表示表达式的当前实例，如同下面表达式中的e：e=>e.....
  5. new(...)如同创建对象
  6. 数组也用小括号，如(1,3,9,22)
  7. T(...)，这里的T是类型名，表示类型转换
  8. 其他语义如，iif、As和Is（应该不区分大小写）
  9. 支持数据库的常用操作符，如and、or、not、<>、in等

8. DynamicExpressionParser.ParseLambda

ParameterExpression x = Expression.Parameter(typeof(int), "x"); ParameterExpression y = Expression.Parameter(typeof(int), "y"); LambdaExpression e = DynamicExpressionParser.ParseLambda(new ParameterExpression[] { x, y }, null, "(x + y) * 2"); 
LambdaExpression e = DynamicExpressionParser.ParseLambda(new ParameterExpression[] { x, y }, typeof(double), "(x + y) * 2"); 
using (var context = new EntityContext()) {     LambdaExpression e = DynamicExpressionParser.ParseLambda(         typeof(Customer), typeof(bool),         "City = @0 and Orders.Count >= @1",         "London", 10); } 
using (var context = new EntityContext()) {     var e1 = DynamicExpressionParser.ParseLambda<Customer, bool>(new ParsingConfig(), true, "City = @0", "London");     var e2 = DynamicExpressionParser.ParseLambda<Customer, bool>(new ParsingConfig(), true, "c => c.CompanyName != "test"");      var customers = context.Customers.ToList().AsQueryable().Where("@0(it) and @1(it)", e1, e2); } 
Expression<Func<Customer, bool>> e1 = c => c.City == "Paris"; var e2 = DynamicExpressionParser.ParseLambda<Customer, bool>(new ParsingConfig(), true, "c => c.CompanyName != "test"");  var customers = context.Customers.ToList().AsQueryable().Where("@0(it) and @1(it)", e1, e2);

9. Create Dynamic Class

public static Type CreateType([NotNull] IList<DynamicProperty> properties, bool createParameterCtor = true) 
var props = new DynamicProperty[] {     new DynamicProperty("Name", typeof(string)),     new DynamicProperty("Birthday", typeof(DateTime))  };  Type type = DynamicClassFactory.CreateType(props);  var dynamicClass = Activator.CreateInstance(type) as DynamicClass; dynamicClass.SetDynamicPropertyValue("Name", "Albert"); dynamicClass.SetDynamicPropertyValue("Birthday", new DateTime(1879, 3, 14));  // Use the class here ... Console.WriteLine(dynamicClass);

10. C# Eval Expression

以上动态linq的写法是完全免费的，但这个Eval.Execute和Eval.Compile是收费的（50个字符串以内免费）。
这也是一个很强大的功能，可能在一些特殊场景需要用到，但是需要注意的是，这个Eval会消耗大量的cpu并且性能会比较差。

int result = Eval.Execute<int>("X + Y", new { X = 1, Y = 2})

附1

转载自官网：https://dynamic-linq.net/basic-query-operators

Aggregate

var averagePrice = context.Orders.Aggregate("Average", "Price"); var maxAmount = context.Orders.Aggregate("Max", "Amount"); var minAmount = context.Orders.Aggregate("Min", "Amount"); var totalAmount = context.Orders.Aggregate("Sum", "Amount");

All

bool allHavePriceGreaterThan2 = context.Orders.All("Price > 2"); var search = "e"; var stronglyTypedResult = context.Users.Where(u => u.Roles.All(r => r.Name.Contains(search))); var dynamicResult = context.Users.Where("Roles.All(Name.Contains(@0))", search);

Any

bool anyHavePriceGreaterThan7 = context.Orders.Any("Price > 7"); var search = "e"; var stronglyTypedResult = context.Users.Where(u => u.Roles.Any(r => r.Name.Contains(search))); var dynamicResult = context.Users.Where("Roles.Any(Name.Contains(@0))", search);

Average

var averagePriceExample1 = context.Orders.Select("Price").Average(); var averagePriceExample2 = context.Orders.Average("Price");

AsEnumerable

var dynamicEnumerable = context.Orders.Select("Amount").AsEnumerable();

Cast and OfType

var ofTypeWorker = context.Employees.OfType(typeof(Worker));  // or  string boss = typeof(Boss).FullName; var ofTypeBossA = context.Employees.OfType(boss); var ofTypeBossB = context.Employees.OfType("Test.Models.Boss");  var allWorkers = context.Employees.OfType(typeof(Worker)); var castToWorkers = allWorkers.Cast(typeof(Worker));  var count = qry.Count("As(Value, "int?") != null");

Concat

var list1 = new List<string> { "User3", "User4" }; var list2 = new List<string> { "User5", "User6", "User7" }; var result = queryable.Select("@0.Concat(@1).ToList()", list1, list2);

Count

int numberOfOrdersWhichHavePriceGreaterThan2 = context.Orders.Count("Price > 2"); var usersWhoHaveTwoRoles = context.Users.Where("u => u.Roles.Count() == 2");

DefaultIfEmpty

var defaultIfEmpty = context.Customers.Where("Name == "not-found"").DefaultIfEmpty();  var users = context.Users.Select("Roles.Where(r => r.Name == "Admin").DefaultIfEmpty().FirstOrDefault()");

Distinct

IQueryable queryable = new[] { 1, 2, 2, 3 }.AsQueryable(); var distinctIntegerValues = queryable.Distinct();  var items = context.Customers     .Include(c => c.Orders)     .Select("new (Name as CustomerName, Orders.Distinct() as UniqueOrders)");

Except

var list1 = new List<string> { "User3", "User4" }; var list2 = new List<string> { "User3", "User6", "User7" }; var result = queryable.Select("@0.Except(@1).ToList()", list1, list2);

First, FirstOrDefault

var first = context.Customers.First("c => c.City == "Paris""); var firstOrDefault = context.Customers.FirstOrDefault("c => c.City == "Otherworld"");  var items = context.Users     .Include(u => u.Roles)     .Select("new (Name as userName, Roles.FirstOrDefault().Name as roleName)")     .ToDynamicList();

GroupBy

GroupBy by a single Key

var result = context.Posts.GroupBy("BlogId");

GroupBy by a composite Key

var result = context.Posts.GroupBy("new (BlogId, PostDate)").OrderBy("Key.PostDate");

GroupBy by a single Key and with a single result

var result = context.Posts.GroupBy("PostDate", "Title");

GroupBy by a single Key and a complex object result

var result = context.Posts.GroupBy("PostDate", "new (Title, Content)");

GroupBy by a single Key and do a count()

var result = context.Posts.GroupBy("BlogId").Select("new(Key, Count() AS Count)");

GroupBy by a single Key and do a sum()

var result = context.Posts.GroupBy("BlogId").Select("new(Key, Sum(NumberOfReads) AS TotalReads)");

GroupByMany

GroupByMany strongly typed extension

var sel = lst.AsQueryable().GroupByMany(x => x.Item1, x => x.Item2).ToList();

GroupByMany as a Dynamic LINQ string expression

var sel = lst.AsQueryable().GroupByMany("Item1", "Item2").ToList();

Intersect

var list1 = new List<string> { "User3", "User4" }; var list2 = new List<string> { "User5", "User6", "User7" }; var result = queryable.Select("@0.Intersect(@1).ToList()", list1, list2);

Join

var realQuery = persons.Join(     pets,     person => person,     pet => pet.Owner,     (person, pet) => new { OwnerName = person.Name, Pet = pet.Name } ); var dynamicQuery = persons.AsQueryable().Join(     pets,     "it",     "Owner",     "new(outer.Name as OwnerName, inner.Name as Pet)" );

Last, LastOrDefault

var last = context.Customers.First("c => c.City == "Paris""); var firstOrDefault = context.Customers.LastOrDefault("c => c.City == "Otherworld"");  var items = context.Users     .Include(u => u.Roles)     .Select("new (Name as userName, Roles.LastOrDefault().Name as roleName)")     .ToDynamicList();

Page, PageResult

var pagedCustomers = context.Customers.OrderBy("Name").Page(page, pageSize); 
var result = context.Customers.OrderBy("Name").PageResult(page, pageSize);  public class PagedResult {     public IQueryable Queryable { get; set; }     public int CurrentPage { get; set; }     public int PageCount { get; set; }     public int PageSize { get; set; }     public int RowCount { get; set; } } public class PagedResult<TSource> : PagedResult {     public new IQueryable<TSource> Queryable { get; set; } }

Reverse

var reversed = ((IQueryable) persons.AsQueryable()).Reverse();

SelectMany

Use SelectMany as ExtensionMethod

var result = context.Users.SelectMany("u => u.Roles.Select(r => r.Name)").ToDynamicArray();

Use SelectMany inside a Dynamic LINQ string and return a list of strings

var result = context.Users.SelectMany("Roles.SelectMany(Permissions)").Select("Name");

Use SelectMany on Generic Type

var result = context.Users.SelectMany<Permission>("Roles.SelectMany(Permissions)")

Use SelectMany with a Type

var result = context.Users.SelectMany(typeof(Permission), "Roles.SelectMany(Permissions)")

Skip, SkipWhile

var skipFirstCustomer = context.Customers.OrderBy("CustomerID").Skip(1);  var skipped = context.Customers.ToList().AsQueryable().SkipWhile("CompanyName != "ZZZ"");

Sum

var totalPriceExample1 = context.Orders.Select("Price * Amount").Sum(); var var totalPriceExample2 = context.Orders.Sum("Price * Amount");

Take, TakeWhile

var takeTwoCustomers = context.Customers.OrderBy("CustomerID").Take(2);  var takeWhile = context.Customers.ToList().AsQueryable().TakeWhile("CompanyName != "ZZZ"");

Union

var list1 = new List<string> { "User3", "User4" }; var list2 = new List<string> { "User5", "User6", "User7" }; var result = queryable.Select("@0.Union(@1).ToList()", list1, list2);

Async Query Operators

AllAsync、AnyAsync、AverageAsync、CountAsync、FirstAsync、FirstOrDefaultAsync、LastAsync、LastOrDefaultAsync、LongCountAsync、SingleOrDefaultAsync、SumAsync

便宜好用的国外VPS推荐