Refactor VB.NET to C#.Net using Linq

Question

I am rewriting a VB.NET application in C#. I will not subject you to the original code because it's pretty messy. Below is the converted C# code:

 public IEnumerable<WebQuery> GetQueries(Request request)
 {
 var queries = new List<WebQuery>();
 foreach (var report in request.Reports)
 {
 queries.AddRange((from query in _context.WebQueries
 join qg in _context.WebQueryGroups on query.QueryKey equals qg.QueryKey
 join wp in _context.WebPermissions on qg.QueryGroupNameKey equals wp.TaskGroupNameKey
 join wugn in _context.WebUserGroupNames on wp.UserGroupNameKey equals wugn.UserGroupNameKey
 join wug in _context.WebUserGroups on wugn.UserGroupNameKey equals wug.UserGroupNameKey
 join wt in _context.WebTasks on query.QueryCategory equals wt.TaskReportCategory
 where wt.TaskKey == request.Key && wp.ResourceKey == 4 && wt.TaskKey == request.Key && wug.UserKey == report.UserKey
 select query).OrderBy(x => x.QueryTitle));
 }
 return queries;
 }

As you can see, I am committing a grievous error in making a call to the database many times.

I am not sure how to append the loop to my query so that I only call the context one time.

Answer 1

Have you tried:

 public IEnumerable<WebQuery> GetQueries(Request request)
 {
 List<string> keys = request.Reports.Select(i => i.UserKey).ToList();
 var queries = new List<WebQuery>();
 queries.AddRange((from query in _context.WebQueries
 join qg in _context.WebQueryGroups on query.QueryKey equals qg.QueryKey
 join wp in _context.WebPermissions on qg.QueryGroupNameKey equals wp.TaskGroupNameKey
 join wugn in _context.WebUserGroupNames on wp.UserGroupNameKey equals wugn.UserGroupNameKey
 join wug in _context.WebUserGroups on wugn.UserGroupNameKey equals wug.UserGroupNameKey
 join wt in _context.WebTasks on query.QueryCategory equals wt.TaskReportCategory
 where wt.TaskKey == request.Key 
 && wp.ResourceKey == 4 
 && wt.TaskKey == request.Key 
 && keys.Contains(wug.UserKey)
 select query).OrderBy(x => x.QueryTitle));
 return queries;
 }

?

• Robert: Does using .Contains(T) will check each item in the list in a single call?