Better way to write this linq query?

Currently i am using a combination of switch operations to generate a linq query, and i am thinking the code is a little bloated.

Is there any way to optimize this code, maybe someway to dynamically build it?

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":开发者_Python百科
                    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();
}

This is exactly where dynamically building expressions is useful:

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

This doesn't solve your default case, but that should be relatively easy to add. Also, using reflection like this might be slow, so you might want to cache the results of GetProperty() and GetMethod().

Another thing to note is that the part that chooses whether to use Distinct() still depends on the property names, but maybe you have a better condition for that (or you could use attributes on the properties).

And the two helper methods don't need to know anything about Person, so it would be trivial to make them generic.

Make use of Dynamic Linq to SQL library may resolve your issue easily.

Blog post : Dynamic query with Linq

Predicate Builder

Predicate builder works same as dynamic linq library but the main difference is its allow to write more type safe queries easily.

Use Dynamic LINQ library

Dynamic LINQ library allows build query which are having varying where clause or orderby. To work with the dynamic LINQ library you need to download and install file in your project.

Check this post of Scott GU : http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

You can go the dynamic linq option described above, or if you want something simpler you can refactor some of the switching logic into smaller pieces and then have a simple query

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
    }

I make 2 new Class , The one is for test , and the other is for compare...

you want to get the distinct by the name ..

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

and then , the method is like ↓

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

i hope this is useful to you...

Generally speaking you want this:

query.Where(o => o.PropertyName.MethodName(keyword));
     .Select(o => o.PropertyName).Take(maxResults).ToArray();

Here is an example:

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

I think this is what you want....

Dynamic LINQ

I have got this all wired up now and was looking into the sql that is generated, this is what i get:

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

This query is now not parametized! How do i resolve this, i don't feel safe using this code :?