更好的方式来写这个linq查询
本文关键字：linq 查询 方式 更好 | 更新日期: 2023-09-27 18:01:27
目前我正在使用开关操作的组合来生成linq查询，我认为代码有点臃肿。
是否有任何方法来优化这段代码，也许以某种方式动态构建它?
public string[] GetPeopleAutoComplete(string filter, int maxResults, string searchType, string searchOption)
{
var query = from people in _context.People select people;
switch (searchOption)
{
case "StartsWith":
switch (searchType)
{
case "IdentityCode":
query = query.Where(o => o.IdentityCode.StartsWith(filter));
return query.Select(o => o.IdentityCode).Take(maxResults).ToArray();
case "Firstname":
query = query.Where(o => o.Firstname.StartsWith(filter));
return query.Select(o => o.Firstname).Distinct().Take(maxResults).ToArray();
case "Surname":
query = query.Where(o => o.Surname.StartsWith(filter));
return query.Select(o => o.Surname).Distinct().Take(maxResults).ToArray();
}
break;
case "EndsWith":
switch (searchType)
{
case "IdentityCode":
query = query.Where(o => o.IdentityCode.EndsWith(filter));
return query.Select(o => o.IdentityCode).Take(maxResults).ToArray();
case "Firstname":
query = query.Where(o => o.Firstname.EndsWith(filter));
return query.Select(o => o.Firstname).Distinct().Take(maxResults).ToArray();
case "Surname":
query = query.Where(o => o.Surname.EndsWith(filter));
return query.Select(o => o.Surname).Distinct().Take(maxResults).ToArray();
}
break;
case "Contains":
switch (searchType)
{
case "IdentityCode":
query = query.Where(o => o.IdentityCode.Contains(filter));
return query.Select(o => o.IdentityCode).Take(maxResults).ToArray();
case "Firstname":
query = query.Where(o => o.Firstname.Contains(filter));
return query.Select(o => o.Firstname).Distinct().Take(maxResults).ToArray();
case "Surname":
query = query.Where(o => o.Surname.Contains(filter));
return query.Select(o => o.Surname).Distinct().Take(maxResults).ToArray();
}
break;
}
return query.Select(o => o.IdentityCode).Take(maxResults).ToArray();
}
这正是动态构建表达式有用的地方:
public string[] GetPeopleAutoComplete(
string filter, int maxResults, string searchType, string searchOption)
{
IQueryable<Person> query = _context.People;
var property = typeof(Person).GetProperty(searchType);
var method = typeof(string).GetMethod(searchOption, new[] { typeof(string) });
query = query.Where(WhereExpression(property, method, filter));
var resultQuery = query.Select(SelectExpression(property));
if (searchType == "Firstname" || searchType == "Lastname")
resultQuery = resultQuery.Distinct();
return resultQuery.Take(maxResults).ToArray();
}
Expression<Func<Person, bool>> WhereExpression(
PropertyInfo property, MethodInfo method, string filter)
{
var param = Expression.Parameter(typeof(Person), "o");
var propExpr = Expression.Property(param, property);
var methodExpr = Expression.Call(propExpr, method, Expression.Constant(filter));
return Expression.Lambda<Func<Person, bool>>(methodExpr, param);
}
Expression<Func<Person, string>> SelectExpression(PropertyInfo property)
{
var param = Expression.Parameter(typeof(Person), "o");
var propExpr = Expression.Property(param, property);
return Expression.Lambda<Func<Person, string>>(propExpr, param);
}
这并不能解决您的默认情况，但应该相对容易添加。此外，使用这样的反射可能很慢，因此您可能希望缓存GetProperty()和GetMethod()的结果。
另一件要注意的事情是，选择是否使用Distinct()的部分仍然取决于属性名称，但也许您有更好的条件(或者您可以在属性上使用属性)。
并且这两个辅助方法不需要知道关于Person的任何信息，因此将它们设置为泛型是微不足道的。
使用Dynamic Linq to SQL库可以轻松解决您的问题。
Blog post: Dynamic query with Linq
谓词Builder
谓词构建器的工作原理与动态linq库相同，但主要区别在于它允许更容易地编写类型安全查询。
使用动态LINQ库
动态LINQ库允许构建具有不同where子句或顺序的查询。要使用动态LINQ库，您需要下载并安装文件到您的项目中。
查看Scott GU: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
您可以使用上面描述的动态linq选项，或者如果您想要更简单的东西，您可以将一些切换逻辑重构为更小的部分，然后有一个简单的查询
public string[] GetPeopleAutoComplete(string filter, int maxResults, string searchType, string searchOption)
{
var query = (from person in _context.People
where MatchesSearchCriteria(searchType, searchOption, filter)
select SelectAttribute(person,searchType,searchOption));
if (RequiresDistinct(filter,searchType, searchOption))
query = query.Distinct();
return query.Take(maxResults).ToArray();
}
private bool MatchesSearchCriteria(string searchType, string searchOption, string filter)
{
//Implement some switching here...
}
private string SelectAttribute(Person person, string searchType, string searchOption)
{
//Implement some switching here to select the correct value from the person
}
private bool RequiresDistinct(string searchType, string searchOption)
{
//Return true if you need to select distinct values for this type of search
}
我新建了2个类，一个用于测试，另一个用于比较…
您想通过名称…来区分
public class PeopleCollection
{
public people[] People;
public class people
{
public string IdentityCode;
public string Firstname;
public string Surname;
}
}
public class ForCompare : IEqualityComparer<PeopleCollection.people>
{
string _fieldName = "";
public ForCompare(string fieldName)
{
_fieldName = fieldName;
}
public bool Equals(PeopleCollection.people a, PeopleCollection.people b)
{
return "IdentityCode".Equals(_fieldName) ? true : a.GetType().GetProperty(_fieldName).GetValue(a, null).Equals(b.GetType().GetProperty(_fieldName).GetValue(b, null));
}
public int GetHashCode(PeopleCollection.people a)
{
return a.GetHashCode();
}
}
，然后，方法是↓
public static string[] GetPeopleAutoComplete(string filter, int maxResults, string searchType, string searchOption)
{
var property = typeof(PeopleCollection.people).GetProperty(searchType);
var method = typeof(string).GetMethod(searchOption, new[] { typeof(string) });
var query = from people in _context.People select people;
return query.Distinct(new ForCompare(searchType))
.Select(o => (string)property.GetValue(o, null))
.Where(value => (bool)method.Invoke(value, new object[] { filter }))
.Take(maxResults).ToArray();
}
一般来说，您需要这样做:
query.Where(o => o.PropertyName.MethodName(keyword));
.Select(o => o.PropertyName).Take(maxResults).ToArray();
下面是一个例子:
public class Person
{
public string FirstName { get; set; }
}
static void Main(string[] args)
{
string propertyName = "FirstName";
string methodName = "StartsWith";
string keyword = "123";
Type t = typeof(Person);
ParameterExpression paramExp = Expression.Parameter(t, "p");
// the parameter: p
MemberExpression memberExp = Expression.MakeMemberAccess(paramExp, t.GetMember(propertyName).FirstOrDefault());
// part of the body: p.FirstName
MethodCallExpression callExp = Expression.Call(memberExp, typeof(string).GetMethod(methodName, new Type[] { typeof(string) }), Expression.Constant(keyword));
// the body: p.FirstName.StartsWith("123")
Expression<Func<Person, bool>> whereExp = Expression.Lambda<Func<Person, bool>>(callExp, paramExp);
Expression<Func<Person, string>> selectExp = Expression.Lambda<Func<Person, string>>(memberExp, paramExp);
Console.WriteLine(whereExp); // p => p.FirstName.StartsWith("123")
Console.WriteLine(selectExp); // p => p.FirstName
List<Person> people = new List<Person>();
List<string> firstNames = people.Where(whereExp.Compile()).Select(selectExp.Compile()).ToList();
Console.Read();
}
我想这就是你想要的....
动态LINQ我已经把这一切都连接起来了，现在正在查看生成的sql，这就是我得到的:
SELECT
[Project1].[Id] AS [Id],
[Project1].[Firstname] AS [Firstname],
[Project1].[LevelGroup] AS [LevelGroup],
[Project1].[IdentityCode] AS [IdentityCode],
[Project1].[C1] AS [C1],
[Project1].[Surname] AS [Surname]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[IdentityCode] AS [IdentityCode],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Surname] AS [Surname],
[Extent1].[LevelGroup] AS [LevelGroup],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Loans] AS [Extent2]
WHERE [Extent1].[Id] = [Extent2].[PersonId]) AS [C1]
FROM [dbo].[People] AS [Extent1]
WHERE [Extent1].[IdentityCode] LIKE N'a%'
) AS [Project1]
ORDER BY [Project1].[Surname] ASC
这个查询现在没有参数化!我如何解决这个问题，我不觉得安全使用这个代码:?