标签：Customers Name 强大 LINQ context var new 动态 Select

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。
需要注意的地方：

&&和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

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

标签：Customers,Name,强大,LINQ,context,var,new,动态,Select
From： https://www.cnblogs.com/pains/p/18563616

强大的动态 LINQ 库