mysql code first,MySQL + Code First + Lazy Load问题！

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.

namespace 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 Houses { get; set; }

public DbSet 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 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 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 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);