none
...already an open DataReader associated with this Command which must be closed first

    Question

  • VS2010, EF4, CTP4, Windows 7, MSSQL10

    This works when I am pointing at a SQL CE 4 db, but when I change the connection string to point at a SQL Express db

    I get this error:

    {"There is already an open DataReader associated with this Command which must be closed first."}

    It blows on the @item.PriceCategory.Description line in my Index.cshtml file.

    Here is the index.cshtml file

     @inherits System.Web.Mvc.WebViewPage<IEnumerable<Data.Product>>
     ...

        @foreach (var item in Model) {
       
            <tr>
                <td>
                    @item.ShortDescription
                </td>
                <td>
                    @item.PriceCategory.Description
            </tr>
       
        }

    Here are the connection strings
     <configuration>
       <connectionStrings>
         <add name="MNG_Model" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Gift;User Instance=true" providerName="System.Data.SqlClient" />
         <add name="_MNG_Model" connectionString="Data Source=|DataDirectory|MNG.sdf" providerName="System.Data.SqlServerCe.4.0"/>
     </connectionStrings>


    Here is the model

        public class Product
        {
            public int ID { get; set; }
            public string ShortDescription { get; set; }

            public virtual PriceCategory PriceCategory { get; set; }
        }

        public class PriceCategory
        {
            public int ID { get; set; }
            public string Description { get; set; }

            public virtual ICollection<Product> Products { get; set; }
        }


    Here is my Initializer.

        public class MNGInitializer: AlwaysRecreateDatabase<MNG_Model>
        {
            protected override void Seed(MNG_Model db)
            {
                db.WebAccts.Add(new WebAcct { UserName = "Terrence" });
                db.WebAccts.Add(new WebAcct { UserName = "David" });

                db.PriceCategories.Add(new PriceCategory { Description = "$1 - $5" });
                db.PriceCategories.Add(new PriceCategory { Description = "$5 - $10" });

                db.SaveChanges();

                Product pr1 = new Product();
                pr1.LastUpdate = DateTime.Now;
                pr1.DateCreated = DateTime.Now;
                pr1.ShortDescription = "Windows Phone7";
                pr1.PriceCategory = db.PriceCategories.Find(1);

                db.Products.Add(pr1);

                pr1 = new Product();
                pr1.LastUpdate = DateTime.Now;
                pr1.DateCreated = DateTime.Now;
                pr1.ShortDescription = "Windows Server 2008R2";
                pr1.PriceCategory = db.PriceCategories.Find(2);

                db.Products.Add(pr1);

                db.SaveChanges();
            }

    Thanks, Terrence

    Wednesday, September 29, 2010 3:07 AM

Answers

  • Hi Terrence,

    You just need to enable Multiple Active Result Sets (MARS), just add 'MultipleActiveResultSets=True' into your connection string.

    You need multiple results sets because in your web page the item.PriceCategory property needs to be lazy loaded while you are still iterating the results of the 'products' query.

    ~Rowan

    Wednesday, September 29, 2010 7:36 PM

All replies

  • Hi Terrence,

    You just need to enable Multiple Active Result Sets (MARS), just add 'MultipleActiveResultSets=True' into your connection string.

    You need multiple results sets because in your web page the item.PriceCategory property needs to be lazy loaded while you are still iterating the results of the 'products' query.

    ~Rowan

    Wednesday, September 29, 2010 7:36 PM
  • Thanks Rowan.  That triggers a memory of reading something about "Include" for lazy loading...?

    Any idea why it works with sql ce 4 without the MARS in the conn string?

    Wednesday, September 29, 2010 7:40 PM
  • Hi Terrence,

    SQL CE supports multiple result sets by default.

    Correct you can avoid the lazy load using include; context.Products.Include(p => p.PriceCategory)
    (You will need a using statement for 'System.Data.Entity' because the lambda based include is an extension method)

    ~Rowan

    Wednesday, September 29, 2010 8:24 PM
  • Thank you sir.
    Wednesday, September 29, 2010 8:26 PM