強大的動態 LINQ 庫

Rick Carter發表於2024-11-22

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

相關文章