Flattening a loop with lookups into a single linq expression

In Type member support in LINQ-to-Entities? I was attempting to declare a class property to be queried in LINQ which ran into some issues. Here I will lay out the code inside the implementation in hopes of some help for converting it to a query.

I have a class Quiz which contains a collection of Questions, each of which is classified according to a QuestionLevel... I need to determine whether a quiz is "open" or "closed", which is accomplished via an outer join on the question levels and a count of the questions in each level, as compared with a table of maximum values. Here's the code, verbatim:

public partial class Quiz
{
    public bool IsClosed
    {
        get
        {
            // if quiz has no questions, it's open
            if (this.Questions.Count() == 0) return false;

            // get a new handle to the EF container to do a query for max values
            using (EFContainer db = new EFContainer())
            {
                // we get a dictionary of LevelName/number
                Dictionary<string, int> max = db.Registry
                    .Where(x => x.Domain == "Quiz")
                    .ToDictionary(x => x.Key, x => Convert.ToInt32(x.Value));
                // count the number of questions in each level, comparing to the maxima
                // if any of them are less, the quiz is "open"
                foreach (QuestionLevel ql in db.QuestionLevels)
                {
                    if (this.Questions.Where(x => x.Level == ql).Count() < max["Q:Max:" + ql.Name])
                        return false;
                }
            }
            // the quiz is closed
            return true;
        }
    }
 }

so here's my not-yet-working attempt at it:

    public static IQueryable<Quiz> WhereIsOpen(this IQueryable<Quiz> query)
    {
        EFContainer db = new EFContainer();
        return from ql in db.QuestionLevels
               join q in query on ql equals q.Questions.Select(x => x.Level)
               into qs
               from q in qs.DefaultIfEmpty()
               where q.Questions.Count() < db.Registry
                    .Where(x => x.Domain == "Quiz")
                    .Where(x => x.Key == "Q:Max" + ql.Name)
                    .Select(x => Convert.ToInt32(x.Value))
               select q;
    }

it fails on account on the join, complaining:

The type of one of the expressions in the join clause is incorrect. The type inference failed in the call to 'GroupJoin'

I'm still trying to figure that out.

* update I *

ah. silly me.

   join q in query on ql equals q.Questions.Select(x => x.Level).Single()

one more roadblock:

The specified LINQ expression contains references to queries that are associated with different contexts.

this is because of the new container I create for the maximum lookups; so I thought to re-factor like this:

    public static IQueryable<Quiz> WhereIsOpen(this IQueryable<Quiz> query)
    {
        EFContainer db = new EFContainer();
        IEnumerable<QuestionLevel> QuestionLevels = db.QuestionLevels.ToList();
        Dictionary<string, int> max = db.Registry
                .Where(x => x.Domain == "Quiz")
                .ToDictionary(x => x.Key, x => Convert.ToInt32(x.Value));
        return from ql in QuestionLevels
               join q in query on ql equals q.Questions.Select(x => x.Level).Single()
               into qs
               from q in qs.DefaultIfEmpty()
               where q.Questions.Count() < max["Q:Max:" + ql.Name]
               select q;
    }

but I can't get the expression to compile... it needs me to cast QuestionLevels to an IQueryable (but casting doesn't work, producing runtime exceptions).

* update II *

I found a solution to the casting problem but now I'm back to the "different contexts" exception. grr...

return from ql in QuestionLevels.AsQueryable()

* update (Kirk's suggestion) *

so I now have this, which compiles but generates a run-time exception:

public static IQueryable<Quiz> WhereIsOpen(this IQueryable<Quiz> query)
{
    EFContainer db = new EFContainer();
    IEnumerable<string> QuestionLevels = db.QuestionLevels.Select(x => x.Name).ToList();
    Dictionary<string, int> max = db.Registry
            .Where(x => x.Domain == "Quiz")
            .ToDictionary(x => x.Key, x => Convert.ToInt32(x.Value));
    return from ql in QuestionLevels.AsQueryable()
           join q in query on ql equals q.Questions.Select(x => x.Level.Name).Single()
           into qs
           from q in qs.DefaultIfEmpty()
           where q.Questions.Count() < max["Q:Max:" + ql]
           select q;
}开发者_运维问答

which I then call like this:

List<Product> p = db.Quizes.WhereIsOpen().Select(x => x.Component.Product).ToList();

with the resulting exception:

This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code.

---

The issues you're coming across are common when you couple your database objects to your domain objects. It's for this exact reason that it's good to have a separate set of classes that represent your domain and a separate set of classes that represent your database and are used for database CRUD. Overlap in properties is to be expected, but this approach offers more control of your application and decouples your database from your business logic.

The idea that a quiz is closed belongs to your domain (the business logic). Your DAL (data access layer) should be responsible for joining all the necessary tables so that when you return a Quiz, all the information needed to determine whether or not it's closed is available. Your domain/service/business layer should then create the domain object with the IsClosed property properly populated so that in your UI layer (MVC) you can easily access it.

I see that you're access the database context directly, I'd warn against that and encourage you to look into using DI/IoC framework (Ninject is great), however, I'm going to access the database context directly also

Use this class in your views/controllers:

public class QuizDomainObject 
{
    public int Id {get; set;}
    public bool IsClosed {get; set;}
    // all other properties
}

Controller:

public class QuizController : Controller 
{
    public ActionResult View(int id)
    {
        // using a DI/IoC container is the 
        // preferred method instead of 
        // manually creating a service
        var quizService = new QuizService(); 
        QuizDomainObject quiz = quizService.GetQuiz(id);

        return View(quiz);
    }
}

Service/business layer:

public class QuizService
{
    public QuizDomainObject GetQuiz(int id)
    {
        // using a DI/IoC container is the 
        // preferred method instead of 
        // access the datacontext directly
        using (EFContainer db = new EFContainer())
        {
            Dictionary<string, int> max = db.Registry
                .Where(x => x.Domain == "Quiz")
                .ToDictionary(x => x.Key, x => Convert.ToInt32(x.Value));

            var quiz = from q in db.Quizes
                       where q.Id equals id
                       select new QuizDomainObject()
                       {
                            Id = q.Id,
                            // all other propeties,

                            // I'm still unclear about the structure of your  
                            // database and how it interlates, you'll need 
                            // to figure out the query correctly here
                            IsClosed =  from q in ....
                       };


            return quiz;
        }
    }
}

---

Re: your comment

The join to QuestionLevels is making it think there are two contexts... but really there shouldn't be because the QuestionLevels should contain in-memory objects

I believe that if you join on simple types rather than objects you'll avoid this problem. The following might work for you:

return from ql in QuestionLevels                
       join q in query 
       on ql.LevelId equals q.Questions.Select(x => x.Level).Single().LevelId
       into qs 

(and if this doesn't work then construct some anonymous types and join on the Id)

The problem is that joining on the Level objects causes EF to do some under-the-covers magic - find the objects in the database and perform a join there. If you tell it to join on a simple type then it should send the values to the database for a SELECT, retrieve the objects and stitch them together back in your application layer.