CODEWITHSUNDEEP
asp.netlinqmodel
asuppa
asuppa

Reputation: 571

Running multiple linq queries on single model in one function

so as part of a school project I am trying to build a simple forum. I could not find any that integrated well with my styles there were free so I am making the basics myself.

Using lists I will output forms, then threads, then posts so it will go 3 pages deep.

the problem I am having is on the first page I want to get a current count from the database for posts/threads. since this can change at a moments notice I figured I would calculate on page load since for this project at best there may be a 100 records to count from...

the code below throws errors. 

        public ActionResult Index(int page = 1)
    {
        ViewBag.Title = "Forums";
        var model = db.forums.OrderBy(x=> x.forumID).ToPagedList(page, 15);
        foreach (var m in db.forums)
        {
            m.postCount = db.threads.Where(t => t.forumID == m.forumID && t.threadID == t.parentThreadID ).Count();
            m.threadCount = db.threads.Where(t => t.forumID == m.forumID).Count();
        }
        return View(model);
    }

error thrown 

Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Source Error: 



Line 20:             foreach (var m in db.forums)
Line 21:             {
Line 22:                 m.postCount = db.threads.Where(t =>  t.forumID == m.forumID && t.threadID == t.parentThreadID ).Count();
Line 23:                 m.threadCount = db.threads.Where(t => t.forumID == m.forumID).Count();
Line 24:             }

Upvotes: 0

Views: 42

Answers (2)

Silas Reinagel
Silas Reinagel

Reputation: 4203

I would suggest making only one call against the model, and just filtering it twice with Linq.

Sample code:

foreach (var m in model)
{
    var threads = db.threads.ToList();
    m.postCount = threads.Where(t =>  t.forumID == m.forumID && t.threadID == t.parentThreadID).Count();
    m.threadCount = threads.Where(t => t.forumID == m.forumID).Count();
}

Upvotes: 1

asuppa
asuppa

Reputation: 571

Turns out that I was looping through db.forums and needed to loop through my model since I had already retrieved the details.

 foreach (var m in model)
    {
        m.postCount = db.threads.Where(t => t.forumID == m.forumID && t.threadID == t.parentThreadID ).Count();
        m.threadCount = db.threads.Where(t => t.forumID == m.forumID).Count();
    }

Upvotes: 0

Related Questions