Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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

?

share|improve this answer
    
Does using .Contains(T) will check each item in the list in a single call? –  Robert Jul 28 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.