There is already an open DataReader associated

Question

I am making this autocomplete search bar which contains title, description and category_id but I need the category name which is in another table so I take out the category id from my ads table and check the id with the table in category I know I need to close my connection to the database before I can make a new one so I need another way around it.

public class SetGetAds
    {
        public string Title { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }
    }

using (var db = new myProjectEnt())
        {
            var getAds = (from a in db.ads where a.title.Contains(searchQuery) select new { a.title, a.description, a.categories_id }).Take(15);

            var ads = new List<SetGetAds>();

            foreach (var setAds in getAds)
            {
                var getCategory = (from c in db.ads where c.title.Equals(setAds.categories_id) select new { c.title }).SingleOrDefault();

                ads.Add(new SetGetAds { Title = setAds.title, Description = setAds.description, Category = getCategory.title });

                var jsonString = new JavaScriptSerializer();
                return jsonString.Serialize(ads);
            }
        }

Answer 1

getAds is an enumerable sequence that is lazily taking data from the reader - you then loop over that. Then, for each one you are performing a second query - getCategory. The important thing here is that getAds is still reading data - so yes, you have nested commands.

Options (in preference order, highest = preferred):

• restructure the query to get the category at the same time, in one go - to avoid both the nested query and the obvious N+1 issue
• add a .ToList() on the end of getAds, to complete the first query eagerly
• enable MARS so that you are allowed to have nested queries

An N+1 issue is very commonly a source of performance problems; personally I would be looking to write this query in a way that avoids that, for example:

var ads = (from a in db.ads
           where a.title.StartsWith(searchQuery)
           join c in db.ads on a.categories_id equals c.title
           select new { a.title, a.description, a.categories_id,
                       category = c.title }).Take(15);