发帖
翼度科技»论坛 › 编程开发 › .net › 查看内容

强大的动态 LINQ 库

阳现朝雾木棉花开

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
显示全部楼层
dynamic-linq.net，这个库大家都用过，简单说就是使用字符串编写查询表达式的，非常强大，因为字符串是可以运行时拼接的，所以就可以在运行时动态的构造查询，非常适合框架开发和需要灵活性的场景。
我用它解决了在原生代码非常困难且耗时（需要动态构造表达式）的功能，真正的直达痛点。
它dotnet下的命名空间是System.Linq.Dynamic.Core，现在就介绍下各种复杂的写法示例和注意事项。
1. IQueryable支持
  1. //context是EFCore的DbContext
  2. context.Customers.Where("City == "Paris"");
  3. context.Customers.Where("City == @0 and Age > @1", "Paris", 50);
  4. context.Customers.Select("new { City, CompanyName }");
  5. context.Customers.OrderBy("City, CompanyName");
  6. context.Customers.OrderBy("City, CompanyName desc");
复制代码
2. IEnumerable支持
  1. //list是IEnumerable<>类型
  2. list.AsQueryable().Where("City == "Paris"");
  3. list.AsQueryable().Where("City == @0 and Age > @1", "Paris", 50);
  4. list.AsQueryable().Select("new { City, CompanyName }");
  5. list.AsQueryable().OrderBy("City, CompanyName");
  6. list.AsQueryable().OrderBy("City, CompanyName desc");
复制代码
3. 参数用@符号加索引，如上文示例

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

如Where、Select、OrderBy、Any、Count、Join，参考 附1。
5. 类型转换

类型转换非常有用，可以对字符串类型的值转成你想要的类型，以满足更灵活场景的动态特性。
也支持复杂类型的转换，参考 附1中的Cast and OfType
  1. //postgres数据库会转成: "Value"::int > 0
  2. var count = qry.Count("As(Value, "int?") >0");
复制代码
6. 复杂条件语句

简单的查询语句都很记住，复杂的查询条件也支持，下面的代码示例，User实体中有个叫Roles的集合导航属性，Role实体中有个Name字符串属性（数据库中对应Name字段），继续参考 附1。
  1. var dynamicResult = context.Users.Where("Roles.Any(Name.Contains(@0))", search);
复制代码
7. 支持dotnet中基本的常用写法

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

  • &&和and等效，||和or等效
  • 字符需要单引号，字符串需要双引号，而不是按数据库中的语义
  • Nullable同样也是类型后面加问号，如 int?
  • it 很特殊，表示表达式的当前实例，如同下面表达式中的e：e=>e.....
  • new(...)如同创建对象
  • 数组也用小括号，如(1,3,9,22)
  • T(...)，这里的T是类型名，表示类型转换
  • 其他语义如，iif、As和Is（应该不区分大小写）
  • 支持数据库的常用操作符，如and、or、not、、in等
8. DynamicExpressionParser.ParseLambda
  1. ParameterExpression x = Expression.Parameter(typeof(int), "x");
  2. ParameterExpression y = Expression.Parameter(typeof(int), "y");
  3. LambdaExpression e = DynamicExpressionParser.ParseLambda(new ParameterExpression[] { x, y }, null, "(x + y) * 2");
复制代码
  1. LambdaExpression e = DynamicExpressionParser.ParseLambda(new ParameterExpression[] { x, y }, typeof(double), "(x + y) * 2");
复制代码
  1. using (var context = new EntityContext())
  2. {
  3.     LambdaExpression e = DynamicExpressionParser.ParseLambda(
  4.         typeof(Customer), typeof(bool),
  5.         "City = @0 and Orders.Count >= @1",
  6.         "London", 10);
  7. }
复制代码
  1. using (var context = new EntityContext())
  2. {
  3.     var e1 = DynamicExpressionParser.ParseLambda<Customer, bool>(new ParsingConfig(), true, "City = @0", "London");
  4.     var e2 = DynamicExpressionParser.ParseLambda<Customer, bool>(new ParsingConfig(), true, "c => c.CompanyName != "test"");
  6.     var customers = context.Customers.ToList().AsQueryable().Where("@0(it) and @1(it)", e1, e2);
  7. }
复制代码
  1. Expression<Func<Customer, bool>> e1 = c => c.City == "Paris";
  2. var e2 = DynamicExpressionParser.ParseLambda<Customer, bool>(new ParsingConfig(), true, "c => c.CompanyName != "test"");
  4. var customers = context.Customers.ToList().AsQueryable().Where("@0(it) and @1(it)", e1, e2);
复制代码
9. Create Dynamic Class
  1. public static Type CreateType([NotNull] IList<DynamicProperty> properties, bool createParameterCtor = true)
复制代码
  1. var props = new DynamicProperty[]
  2. {
  3.     new DynamicProperty("Name", typeof(string)),
  4.     new DynamicProperty("Birthday", typeof(DateTime))
  5. };
  7. Type type = DynamicClassFactory.CreateType(props);
  9. var dynamicClass = Activator.CreateInstance(type) as DynamicClass;
  10. dynamicClass.SetDynamicPropertyValue("Name", "Albert");
  11. dynamicClass.SetDynamicPropertyValue("Birthday", new DateTime(1879, 3, 14));
  13. // Use the class here ...
  14. Console.WriteLine(dynamicClass);
复制代码
10. C# Eval Expression

以上动态linq的写法是完全免费的，但这个Eval.Execute和Eval.Compile是收费的（50个字符串以内免费）。
这也是一个很强大的功能，可能在一些特殊场景需要用到，但是需要注意的是，这个Eval会消耗大量的cpu并且性能会比较差。
  1. int result = Eval.Execute<int>("X + Y", new { X = 1, Y = 2})
复制代码
附1

转载自官网：https://dynamic-linq.net/basic-query-operators
Aggregate
  1. var averagePrice = context.Orders.Aggregate("Average", "Price");
  2. var maxAmount = context.Orders.Aggregate("Max", "Amount");
  3. var minAmount = context.Orders.Aggregate("Min", "Amount");
  4. var totalAmount = context.Orders.Aggregate("Sum", "Amount");
复制代码
All
  1. bool allHavePriceGreaterThan2 = context.Orders.All("Price > 2");
  2. var search = "e";
  3. var stronglyTypedResult = context.Users.Where(u => u.Roles.All(r => r.Name.Contains(search)));
  4. var dynamicResult = context.Users.Where("Roles.All(Name.Contains(@0))", search);
复制代码
Any
  1. bool anyHavePriceGreaterThan7 = context.Orders.Any("Price > 7");
  2. var search = "e";
  3. var stronglyTypedResult = context.Users.Where(u => u.Roles.Any(r => r.Name.Contains(search)));
  4. var dynamicResult = context.Users.Where("Roles.Any(Name.Contains(@0))", search);
复制代码
Average
  1. var averagePriceExample1 = context.Orders.Select("Price").Average();
  2. var averagePriceExample2 = context.Orders.Average("Price");
复制代码
AsEnumerable
  1. var dynamicEnumerable = context.Orders.Select("Amount").AsEnumerable();
复制代码
Cast and OfType
  1. var ofTypeWorker = context.Employees.OfType(typeof(Worker));
  3. // or
  5. string boss = typeof(Boss).FullName;
  6. var ofTypeBossA = context.Employees.OfType(boss);
  7. var ofTypeBossB = context.Employees.OfType("Test.Models.Boss");
  9. var allWorkers = context.Employees.OfType(typeof(Worker));
  10. var castToWorkers = allWorkers.Cast(typeof(Worker));
  12. var count = qry.Count("As(Value, "int?") != null");
复制代码
Concat
  1. var list1 = new List<string> { "User3", "User4" };
  2. var list2 = new List<string> { "User5", "User6", "User7" };
  3. var result = queryable.Select("@0.Concat(@1).ToList()", list1, list2);
复制代码
Count
  1. int numberOfOrdersWhichHavePriceGreaterThan2 = context.Orders.Count("Price > 2");
  2. var usersWhoHaveTwoRoles = context.Users.Where("u => u.Roles.Count() == 2");
复制代码
DefaultIfEmpty
  1. var defaultIfEmpty = context.Customers.Where("Name == "not-found"").DefaultIfEmpty();
  3. var users = context.Users.Select("Roles.Where(r => r.Name == "Admin").DefaultIfEmpty().FirstOrDefault()");
复制代码
Distinct
  1. IQueryable queryable = new[] { 1, 2, 2, 3 }.AsQueryable();
  2. var distinctIntegerValues = queryable.Distinct();
  4. var items = context.Customers
  5.     .Include(c => c.Orders)
  6.     .Select("new (Name as CustomerName, Orders.Distinct() as UniqueOrders)");
复制代码
Except
  1. var list1 = new List<string> { "User3", "User4" };
  2. var list2 = new List<string> { "User3", "User6", "User7" };
  3. var result = queryable.Select("@0.Except(@1).ToList()", list1, list2);
复制代码
First, FirstOrDefault
  1. var first = context.Customers.First("c => c.City == "Paris"");
  2. var firstOrDefault = context.Customers.FirstOrDefault("c => c.City == "Otherworld"");
  4. var items = context.Users
  5.     .Include(u => u.Roles)
  6.     .Select("new (Name as userName, Roles.FirstOrDefault().Name as roleName)")
  7.     .ToDynamicList();
复制代码
GroupBy

GroupBy by a single Key
  1. var result = context.Posts.GroupBy("BlogId");
复制代码
GroupBy by a composite Key
  1. var result = context.Posts.GroupBy("new (BlogId, PostDate)").OrderBy("Key.PostDate");
复制代码
GroupBy by a single Key and with a single result
  1. var result = context.Posts.GroupBy("PostDate", "Title");
复制代码
GroupBy by a single Key and a complex object result
  1. var result = context.Posts.GroupBy("PostDate", "new (Title, Content)");
复制代码
GroupBy by a single Key and do a count()
  1. var result = context.Posts.GroupBy("BlogId").Select("new(Key, Count() AS Count)");
复制代码
GroupBy by a single Key and do a sum()
  1. var result = context.Posts.GroupBy("BlogId").Select("new(Key, Sum(NumberOfReads) AS TotalReads)");
复制代码
GroupByMany

GroupByMany strongly typed extension
  1. var sel = lst.AsQueryable().GroupByMany(x => x.Item1, x => x.Item2).ToList();
复制代码
GroupByMany as a Dynamic LINQ string expression
  1. var sel = lst.AsQueryable().GroupByMany("Item1", "Item2").ToList();
复制代码
Intersect
  1. var list1 = new List<string> { "User3", "User4" };
  2. var list2 = new List<string> { "User5", "User6", "User7" };
  3. var result = queryable.Select("@0.Intersect(@1).ToList()", list1, list2);
复制代码
Join
  1. var realQuery = persons.Join(
  2.     pets,
  3.     person => person,
  4.     pet => pet.Owner,
  5.     (person, pet) => new { OwnerName = person.Name, Pet = pet.Name }
  6. );
  7. var dynamicQuery = persons.AsQueryable().Join(
  8.     pets,
  9.     "it",
  10.     "Owner",
  11.     "new(outer.Name as OwnerName, inner.Name as Pet)"
  12. );
复制代码
Last, LastOrDefault
  1. var last = context.Customers.First("c => c.City == "Paris"");
  2. var firstOrDefault = context.Customers.LastOrDefault("c => c.City == "Otherworld"");
  4. var items = context.Users
  5.     .Include(u => u.Roles)
  6.     .Select("new (Name as userName, Roles.LastOrDefault().Name as roleName)")
  7.     .ToDynamicList();
复制代码
Page, PageResult
  1. var pagedCustomers = context.Customers.OrderBy("Name").Page(page, pageSize);
复制代码
  1. var result = context.Customers.OrderBy("Name").PageResult(page, pageSize);
  3. public class PagedResult
  4. {
  5.     public IQueryable Queryable { get; set; }
  6.     public int CurrentPage { get; set; }
  7.     public int PageCount { get; set; }
  8.     public int PageSize { get; set; }
  9.     public int RowCount { get; set; }
  10. }
  11. public class PagedResult<TSource> : PagedResult
  12. {
  13.     public new IQueryable<TSource> Queryable { get; set; }
  14. }
复制代码
Reverse
  1. var reversed = ((IQueryable) persons.AsQueryable()).Reverse();
复制代码
SelectMany

Use SelectMany as ExtensionMethod
  1. 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
  1. var result = context.Users.SelectMany<Permission>("Roles.SelectMany(Permissions)").Select("Name");
复制代码
Use SelectMany on Generic Type
  1. var result = context.Users.SelectMany<Permission>("Roles.SelectMany(Permissions)")
复制代码
Use SelectMany with a Type
  1. var result = context.Users.SelectMany(typeof(Permission), "Roles.SelectMany(Permissions)")
复制代码
Skip, SkipWhile
  1. var skipFirstCustomer = context.Customers.OrderBy("CustomerID").Skip(1);
  3. var skipped = context.Customers.ToList().AsQueryable().SkipWhile("CompanyName != "ZZZ"");
复制代码
Sum
  1. var totalPriceExample1 = context.Orders.Select("Price * Amount").Sum();
  2. var var totalPriceExample2 = context.Orders.Sum("Price * Amount");
复制代码
Take, TakeWhile
  1. var takeTwoCustomers = context.Customers.OrderBy("CustomerID").Take(2);
  3. var takeWhile = context.Customers.ToList().AsQueryable().TakeWhile("CompanyName != "ZZZ"");
复制代码
Union
  1. var list1 = new List<string> { "User3", "User4" };
  2. var list2 = new List<string> { "User5", "User6", "User7" };
  3. var result = queryable.Select("@0.Union(@1).ToList()", list1, list2);
复制代码
Async Query Operators

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

来源:https://www.cnblogs.com/pains/p/18563616
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: 功能齐全的 WPF 自定义控件资源库（收藏版）

下一篇: ASP.NET Core 标识（Identity）框架系列（一）：如何使用 ASP.NET Core 标

发表于 2024-11-22 22:06:08

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4