更好的编写此linq查询的方法？

Question

目前我正在使用一些switch操作来生成一个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();
}

- xqwzid

Answer 1

这正是动态构建表达式有用的地方:

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的信息，因此将它们变成通用的将非常简单。

- svick

Answer 2

使用动态 Linq to SQL 库可以轻松解决您的问题。

博客文章：使用 Linq 进行动态查询 谓词生成器 谓词生成器与动态 Linq 库的工作方式相同，但主要区别在于它允许更轻松地编写类型安全的查询。 使用动态 LINQ 库 动态 LINQ 库允许构建具有不同 where 子句或 orderby 的查询。要使用动态 LINQ 库，您需要在项目中下载和安装文件。

查看 Scott GU 的这篇文章：http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

- Pranay Rana

---

当一个查询包含多个谓词（使用 and 和 or 连接）时，Predicate Builder 非常有用。但这里不是这种情况。 - svick

Answer 3

你可以选择上面描述的动态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
    }

- cordialgerm

Answer 4

我创建了两个新类，一个用于测试，另一个用于比较...

你想通过名称获取不同之处..

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();
    }

希望这对你有用...

- shenhengbin

Answer 5

一般来说，您需要这个：

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();
} 

- Cheng Chen

---

大家好， 非常感谢你们的回复，它们都非常有帮助。我只有一个问题。当使用Danny Chen的解决方案时，“筛选”条件必须区分大小写。例如，在数据库中，IdentityCode可以以大写字母“A”开头，但是“a”没有结果？？在普通查询中不会发生这种情况... 我该如何解决这个问题？ - xqwzid

Answer 6

我现在已经把所有东西都连好了，正在研究生成的 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

这个查询现在没有参数化！我该如何解决这个问题，我不觉得使用这段代码很安全 :?

- xqwzid

Answer 7

我觉得这可能是你想要的...

动态 LINQ

- shenhengbin

---

我能否使用“动态Linq”或“谓词生成器”通过将它们作为参数传递给方法来动态生成“包含，以...结尾或以...开头”的关键字？对于“IdentityCode，Firstname和Surname”字段也是如此。这就是我的整个目标，将代码缩减到仅几行？ - xqwzid