什么都2020了，LINQ查询你还在用表达式树
1、简介
今天给大家推荐个好的轮子，System.Linq.Dynamic.Core。我们都知道
数据库应用程序经常依赖于“动态SQL”，即在运行时通过程序逻辑构造的查询。拼接SQL容易造成SQL注入，普通的LINQ可以用表达式树来完成，但也比较麻烦。推荐System.Linq.Dynamic.Core用起来比较方便。
这是Microsoft程序集的.NET 4.0动态语言功能的.NET Core /标准端口。
使用此库，可以在iQueryTable上编写动态LINQ查询（基于字符串）：
var query = db.Customers .Where("City == @0 and Orders.Count >= @1", "London", 10) .OrderBy("CompanyName") .Select("new(CompanyName as Name, Phone)");
2、使用
2.1、nuget
Install-Package System.Linq.Dynamic.Core -Version 1.2.5
2.2、常见方式
using System.Collections;using System.Collections.Generic;using System.Linq.Dynamic.Core.Tests.Helpers;using System.Linq.Dynamic.Core.Tests.Helpers.Models;using System.Linq.Expressions;using System.Reflection;namespace System.Linq.Dynamic.Core.ConsoleTestApp{ public static class E { public static IQueryable GroupBy2<TSource, TKey>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keyLambda2) { //LambdaExpression keyLambda = DynamicExpression.ParseLambda(source.ElementType, null, "new (Profile.Age)", null); LambdaExpression x = (LambdaExpression)keyLambda2; //return source.Provider.CreateQuery<IGrouping<TKey, TSource>>( // Expression.Call( // typeof(Queryable), "GroupBy", // new Type[] { source.ElementType, keySelector.Body.Type }, // new Expression[] { source.Expression, Expression.Quote(keySelector) } // )); return source.Provider.CreateQuery( Expression.Call( typeof(Queryable), "GroupBy", new Type[] { source.ElementType, x.Body.Type }, new Expression[] { source.Expression, Expression.Quote(x) })); } } public class Program{ public static void Main(string[] args) { Console.WriteLine("--start"); DynamicProperty[] props = { new DynamicProperty("Name", typeof(string)), new DynamicProperty("Birthday", typeof(DateTime)) }; Type type = DynamicClassFactory.CreateType(props); DynamicProperty[] props2 = { new DynamicProperty("Name", typeof(string)), new DynamicProperty("Birthday", typeof(DateTime)) }; Type type2 = DynamicClassFactory.CreateType(props2); DynamicProperty[] props3 = { new DynamicProperty("Name", typeof(int)), new DynamicProperty("Birthday", typeof(DateTime)) }; Type type3 = DynamicClassFactory.CreateType(props3); DynamicClass dynamicClass = Activator.CreateInstance(type) as DynamicClass; dynamicClass.SetDynamicPropertyValue("Name", "Albert"); dynamicClass.SetDynamicPropertyValue("Birthday", new DateTime(1879, 3, 14)); Console.WriteLine(dynamicClass); string n1 = dynamicClass["Name"] as string; Console.WriteLine("dynamicClass[\"Name\"] = '" + n1 + "'"); dynamicClass["NameX"] = "x"; string n2 = dynamicClass["NameX"] as string; Console.WriteLine("dynamicClass[\"NameX\"] = '" + n2 + "'"); //GroupByAndSelect_TestDynamicSelectMember(); //Select(); //TestDyn(); //ExpressionTests_Enum(); //Where(); //ExpressionTests_Sum(); Console.WriteLine("--end"); } private static void GroupByAndSelect_TestDynamicSelectMember() { var testList = User.GenerateSampleModels(51).Where(u => u.Profile.Age < 23); var qry = testList.AsQueryable(); var rrrr = qry.GroupBy2(x => new { x.Profile.Age }); var ll = rrrr.ToDynamicList(); var byAgeReturnAllReal = qry.GroupBy(x => new { x.Profile.Age }).ToList(); var r1 = byAgeReturnAllReal[0]; //var byAgeReturnOK = qry.GroupBy("Profile.Age").ToDynamicList(); // - [0] {System.Linq.Grouping<<>f__AnonymousType0<int?>, System.Linq.Dynamic.Core.Tests.Helpers.Models.User>} object {System.Linq.Grouping<<>f__AnonymousType0<int?>, System.Linq.Dynamic.Core.Tests.Helpers.Models.User>} var byAgeReturnAll = qry.GroupBy("new (Profile.Age)").OrderBy("Key.Age").ToDynamicList(); var q1 = byAgeReturnAll[0]; var k = q1.Key; int? age = k.Age; foreach (var x in byAgeReturnAllReal.OrderBy(a => a.Key.Age)) { Console.WriteLine($"age={x.Key.Age} : users={x.ToList().Count}"); } foreach (var x in byAgeReturnAll) { Console.WriteLine($"age={x.Key.Age} : users={x}"); } } private static void TestDyn() { var user = new User { UserName = "x" }; dynamic userD = user; string username = userD.UserName; Console.WriteLine("..." + username); } public static void ExpressionTests_Enum() { //Arrange var lst = new List<TestEnum> { TestEnum.Var1, TestEnum.Var2, TestEnum.Var3, TestEnum.Var4, TestEnum.Var5, TestEnum.Var6 }; var qry = lst.AsQueryable(); //Act var result1 = qry.Where("it < TestEnum.Var4"); var result2 = qry.Where("TestEnum.Var4 > it"); var result3 = qry.Where("it = Var5"); var result4 = qry.Where("it = @0", TestEnum.Var5); var result5 = qry.Where("it = @0", 8); //Assert int idx = 0; var ar1 = result1.ToArray(); foreach (var c in new[] { TestEnum.Var1, TestEnum.Var2, TestEnum.Var3 }) { Console.Write("*"); Write((int)c, (int)ar1[idx]); idx++; } idx = 0; ar1 = result2.ToArray(); foreach (var c in new[] { TestEnum.Var1, TestEnum.Var2, TestEnum.Var3 }) { Console.Write("*"); Write((int)c, (int)ar1[idx]); idx++; } Write((int)TestEnum.Var5, (int)result3.Single()); Write((int)TestEnum.Var5, (int)result4.Single()); Write((int)TestEnum.Var5, (int)result5.Single()); } public static void Where() { //Arrange var testList = User.GenerateSampleModels(100, allowNullableProfiles: true); var qry = testList.AsQueryable(); //Act //var xxx = DynamicQueryable var userById = qry.Where("Id=@0", testList[10].Id); var userByUserName = qry.Where("UserName=\"User5\""); var nullProfileCount = qry.Where("Profile=null"); var userByFirstName = qry.Where("Profile!=null && Profile.FirstName=@0", testList[1].Profile.FirstName); //Assert Write(testList[10], userById.Single()); Write(testList[5], userByUserName.Single()); Write(testList.Count(x => x.Profile == null), nullProfileCount.Count()); Write(testList[1], userByFirstName.Single()); } public static void ExpressionTests_Sum() { //Arrange int[] initValues = { 1, 2, 3, 4, 5 }; var qry = initValues.AsQueryable().Select(x => new { strValue = "str", intValue = x }).GroupBy(x => x.strValue); //Act var result = qry.Select("Sum(intValue)").AsEnumerable().ToArray()[0]; //Assert Write(15, result); } public static void Select() { //Arrange List<int> range = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; var testList = User.GenerateSampleModels(100); var qry = testList.AsQueryable(); //Act IEnumerable rangeResult = range.AsQueryable().Select("it * it"); var userNames = qry.Select("UserName"); var userFirstName = qry.Select("new (UserName, Profile.FirstName as MyFirstName)"); var userRoles = qry.Select("new (UserName, Roles.Select(Id) AS RoleIds)"); //Assert WriteArray(range.Select(x => x * x).ToArray(), rangeResult.Cast<int>().ToArray()); WriteArray(testList.Select(x => x.UserName).ToArray(), userNames.ToDynamicArray()); WriteArray(testList.Select(x => "{UserName=" + x.UserName + ", MyFirstName=" + x.Profile.FirstName + "}").ToArray(), userFirstName.AsEnumerable().Select(x => x.ToString()).ToArray()); Guid[] check = testList[0].Roles.Select(x => x.Id).ToArray(); //dynamic f = userRoles.First(); //Guid[] ids = f.RoleIds.ToArray(); var userRole = userRoles.First(); Console.WriteLine(">>>>>>>>>>>>>>>>>>" + userRole.ToString()); PropertyInfo[] props = userRole.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); Console.WriteLine(">>>>>>>>>>>>>>>>>> GetProperties = {0}", string.Join(", ", props.Select(p => p.Name))); Console.WriteLine(">>>>>>>>>>>>>>>>>> GetPropertyValues = {0}", string.Join(", ", props.Select(p => p.GetValue(userRole, null)))); string name = userRole.UserName; Guid[] result = Enumerable.ToArray(userRole.RoleIds ?? new object[0]); WriteArray(check, result); } private static void Write<T>(T check, T result) where T : class{ Console.WriteLine("> '{0}'", check == result); } private static void Write(int check, int result) { Console.WriteLine("> {0} == {1} = '{2}'", check, result, check == result); } private static void WriteArray<T>(T[] check, T[] result) where T : class{ for (int i = 0; i < check.Length; i++) { Console.WriteLine("> {0} : c={1}, r={2} '{3}'", i, check[i], result[i], check[i] == result[i]); } } private static void WriteArray(Guid[] check, Guid[] result) { for (int i = 0; i < check.Length; i++) { Console.WriteLine("> {0} : c={1}, r={2} '{3}'", i, check[i], result[i], check[i] == result[i]); } } private static void WriteArray(int[] check, int[] result) { for (int i = 0; i < check.Length; i++) { Console.WriteLine("> {0} : c={1}, r={2} '{3}'", i, check[i], result[i], check[i] == result[i]); } } }}
开源地址：
https://github.com/zzzprojects/System.Linq.Dynamic.Core
https://www.nuget.org/packages/System.Linq.Dynamic.Core
赞 (0)