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

?