Separation of long linq queries to improve maintainability

Question

I have a bunch of these Jobs based on LINQ queries. I am looking for a good way to reorganize them and make them easier to read and allow changes to requests depending on the language / region, etc.

var mailTaskOne = CreateTask(() => myService.Mail.Where(p => p.ProjectName == "Delta" && (p.MailLang== (int)MailLanguage.EU || p.MailLang == (int)MailLanguage.RU) && (p.DateEntered >= startDate && p.DateEntered <= endDate) && p.MailPriority == (int)MailPriority.High).Count()); 

One way, in my opinion, would be convenient to split the request into something like this.

 var results = myService.Mail.Where(x => x.ProjectName == "Delta"); results = results.Where(p => p.MailLang== (int)MailLanguage.EU); results = results.Where(p => p.DateModified >= startDate && p.DateModified <= endDate); 

This would allow me to do this without repeating the entire query for each region.

 if (MailLanguage == "English") results = results.Where(p => p.MailLang== (int)MailLanguage.EU); else results = results.Where(p => p.MailLang== (int)MailLanguage.RU); 

Is there anyone who knows a better solution for this? I get huge features since I need to make 20 of these queries depending on requirements; such as Region, Project Name, etc.

---

Edit:

Due to some limitations that I did not know about using the back-end (web services / api), I, unfortunately, could not use some of the wonderful answers mentioned in this question.

For example, this does not work out correctly, but in no case, because the answer is incorrect, it just does not work with the API I'm working with, possibly because it is poorly implemented.

 public bool IsValid(Type x) { return (xa == b) && (xc ==d) && (xd == e); } 

In any case, anyone looking for similar solutions is all the correct answers, but in the end I ended up with something similar to a snurre solution.

Answer 1

You can create a parameter class, for example:

 public class MailParameters { public DateTime EndTime { get; private set; } public IEnumerable<int> Languages { get; private set; } public int Priority { get; private set; } public string ProjectName { get; private set; } public DateTime StartTime { get; private set; } public MailParameters(string projectName, DateTime startTime, DateTime endTime, MailLang language, Priority priority) : this(projectName, startTime, endTime, new[] { language }, priority) public MailParameters(string projectName, DateTime startTime, DateTime endTime, IEnumerable<MailLang> languages, Priority priority) { ProjectName = projectName; StartTime = startTime; EndTime = endTime; Languages = languages.Cast<int>(); Priority = (int)priority; } } 

Then add these extension methods:

 public static int Count(this IQueryable<Mail> mails, MailCountParameter p) { return mails.Count(m => m.ProjectName == p.ProjectName && p.Languages.Contains(m.MailLang) && m.EnteredBetween(p.StartTime, p.EndTime) && m.Priority == p.Priority); } public static bool EnteredBetween(this Mail mail, DateTime startTime, DateTime endTime) { return mail.DateEntered >= startTime && mail.DateEntered <= endTime; } 

Then use will be:

 var mailParametersOne = new MailParameters("Delta", startDate, endDate, new[] { MailLang.EU, MailLang.RU }, MailPriority.High); var mailTaskOne = CreateTask(() => myService.Mail.Count(mailParametersOne)); 

Answer 2

I would just decompose the request into different lines, as you suggested, this means that you can put comments in a line to describe what it does. You still make only 1 trip to the database so as not to lose anything in terms of performance, but getting better readability.

Answer 3

Why not just use the method for this purpose?

 public static IQueryable<Mail> Count(this IQueryable<Mail> mails, string projectName, MailLanguage mailLanguage, DateTime startDate, DateTime endDate) { return mails.Count(p=> p.ProjectName == projectName && p.MailLang == mailLanguage && p.DateEntered >= startDate && p.DateEntered <= endDate && p.MailPriority == (int)MailPriority.High); } 

then you can just use it like that

 CreateTask(() => myService.Mail.Count("Delta",MailLanguage.EU,startDate,endDate)); 

Answer 4

You can turn the project name, changed data, mail language and any other criteria into variables and provide them with the necessary value based on any condition. Then your query will use variables, not literal values.

 var projectName="Delta"; var mailLanguage=(int)MailLanguage.RU; var results=myService.Mail.Where(x => x.ProjectName == projectName) && (p.MailLang== mailLanguage); 

That way, you can put most of the complexity into assigning values ​​to variables, and the linq query will be easier to read and use.

Answer 5

Consider transferring complex comparisons to a function. For exanple instead

 Results.Where(x => (xa == b) && (xc == d) && (xd == e)) 

consider

 Results.Where(x => IsValid(x)) ... public bool IsValid(Type x) { return (xa == b) && (xc ==d) && (xd == e); } 

Code becomes more readable, and IsValid is easily tested using an automated testing system.

Answer 6

My final decision is based on an article by ScottGu. http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

I create a LINQ query as follows.

  var linqStatements = new List<String>(); linqStatements.Add(parser.StringToLinqQuery<Project>("ProjectId", report.Project)); linqStatements.Add(parser.StringToLinqQuery<Region>("RegionId", report.Region)); linqStatements.Add(parser.StringToLinqQuery<Status>("Status", report.Status)); linqStatements.Add(parser.StringToLinqQuery<Priority>("Priority", report.Priority)); linqStatements.Add(parser.StringToLinqQuery<Category>("CategoryId", report.Category)); linqStatements.Add(AccountIdsToLinqQuery(report.PrimaryAssignment)); string baseQuery = String.Join(" AND ", linqStatements.Where(s => !String.IsNullOrWhiteSpace(s))); var linqQuery = service.Mail.Where(baseQuery).Cast<Mail>(); 

StringToLinqQuery looks something like this (simplified version).

 public string StringToLinqQuery<TEnum>(string field, string value) where TEnum : struct { if (String.IsNullOrWhiteSpace(value)) return String.Empty; var valueArray = value.Split('|'); var query = new StringBuilder(); for (int i = 0; i < valueArray.Count(); i++) { TEnum result; if (Enum.TryParse<TEnum>(valueArray[i].ToLower(), true, out result)) { if (i > 0) query.Append(" OR "); query.AppendFormat("{0} == {1}", field, Convert.ToInt32(result)); } else { throw new DynoException("Item '" + valueArray[i] + "' not found. (" + type of (TEnum) + ")", query.ToString()); } } // Wrap field == value with parentheses () query.Insert(0, "("); query.Insert(query.Length, ")"); return query.ToString(); } 

And the end result will look something like this.

 service.Mail.Where("(ProjectId == 5) AND (RegionId == 6 OR RegionId == 7) AND (Status == 5) and (Priority == 5)") 

My project stores the values ​​in an XML file and then passes to the LINQ query above. If the field is empty, it will be ignored. It also supports multiple values ​​using the | , eg. EU|US will be translated to (Region == 5 OR Region == 6) .