Best way to write this linq query?

Question

I am currently using a combination of switch operations to generate a linq request, and I think the code is a little swollen.

Is there a way to optimize this code, perhaps somehow 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": 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(); } 

Answer 1

This is exactly where dynamic expression building 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 does not solve your default case, but it should be relatively easy to add. Also, using this reflection can be slow, so you can 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 this (or you can use attributes for the properties )

And the two helper methods do not need to know anything about Person , so it would be trivial to make them generalized.

Answer 2

Using the Dynamic Linq to SQL library can easily solve your problem.

Blog Post: Dynamic Query with Linq

Predicate constructor

The creator of Predicate works just like the dynamic linq library, but the main difference is that it makes it easy to write more secure type queries.

Use LINQ dynamic library

The LINQ dynamic library allows you to build a query that has a variable where where or orderby. To work with the LINQ dynamic library, you need to download and install the file in your project.

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

Answer 3

You can go to the dynamic linq function described above, or if you need something simpler, you can reorganize part of the logic of switching to smaller parts and then execute 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 } 

Answer 4

I am making 2 new classes, one for test and one for comparison ...

You want a distinct 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 ...

Answer 5

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

Answer 6

I think this is what you want ....

Dynamic LINQ

Answer 7

I have everything connected now and I was looking for the created sql, here 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 no longer parameterized! How can I solve this problem? I don’t feel safe with this code :?