MySQL + Code First + Lazy Load problem !

in a very simple real estate program I'm trying to list all images for a house using EF Code First attached to an existing DB I have, I'm using MySQL Conector 6.3.6, here's my code.

namesp开发者_StackOverflow社区ace CodeFirstMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            RealEstate db = new RealEstate();

            var houses = (from h in db.Houses
                         select h).Take(10);

            foreach (var house in houses)
            {
                Console.WriteLine(string.Format("Images for {0}", house.Address));
                foreach (Image image in house.Images)
                {
                    Console.WriteLine(string.Format("=> {0}", image.FileName));
                }
            }
        }
    }

    public class RealEstate : DbContext
    {
        public DbSet<House> Houses { get; set; }
        public DbSet<Image> Images { get; set; }
    }

    [Table("CADIMO")]
    public class House
    {
        [Column("CODIGO")]
        public int HouseId { get; set; }

        [Column("ENDERECO")]
        public string Address { get; set; }

        public virtual ICollection<Image> Images { get; set; }
    }

    [Table("CDIMIM")]
    public class Image
    {
        [Key]
        [Column("CODIGO", Order = 0)]
        public int HouseId { get; set; }

        [Key]
        [Column("CODIGO_I", Order = 1)]
        public int ImageOrder { get; set; }

        [Column("FILE_PATH")]
        public string FileName { get; set; }
    }
}

When I try to run this I get the following error:

Images for Porto das Dunas

Unhandled Exception: System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.

---> MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

at MySql.Data.MySqlClient.MySqlCommand.CheckState()

at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

--- End of inner exception stack trace ---

The interesting thing is that if I use the following query

List<House> houses = (from h in db.Houses select h).Take(10).ToList();

Then it works but a eager load is executed because when I check the Images property for each house they are already populated. So, my question is, can Lazy Load be used with MySQL and Code First?

I know that Linq doesn't execute the query until we use it, so maybe the DataReader is still open when it's time to lazy load the images and this is why the problem is taking place.

Any light on this issue is appreciated.

thank you Anderson Fortaleza

...so maybe the DataReader is still open when it's time to lazy load the images and this is why the problem is taking place.

That is exactly what's happening, but I think for not quite the reason that you think. The DataReader is still open, not because of deferred execution in Linq, but because you're still iterating through the query results when you try to access the other property that isn't loaded yet. When you call .ToList() the results get returned all at once and stored in a List<TEntity> in memory on the client, instead of being returned 1 record at a time.

You can get around this in MS SQL Server using the setting MultipleActiveResultSets=true in your connection string, but MySQL doesn't support this setting. What you should be able to do, though, is eager-load the additional data you need using .Include("tablename")

var houses = (from h in db.Houses.Include("Images")
              select h).Take(10);