MySQL + Code First + Lazy Load问题！ [英] MySQL + Code First + Lazy Load problem !

问题描述

在一个非常简单的房地产项目我试图列出所有图像的房子使用EF代码首先附加到现有的数据库，我使用MySQL Conector 6.3.6，这是我的代码。 >

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<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:

Porto das Dunas的图片

未处理的异常：
System.Data.EntityCommandExecutionException：
执行
命令定义时发生错误。

---> MySql.Data.MySqlClient.MySqlException：
已经有一个开放的DataReader
与此连接相关联，
必须先关闭。

MySql.Data.MySqlClient.MySqlCommand.CheckState（）

at
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader（CommandBehavior
行为）

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）
---内部结束
异常堆栈跟踪---

有趣的是事情是，如果我使用以下查询

The interesting thing is that if I use the following query

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

然后它工作，但是执行一个渴望的负载，因为当我检查每个房子的Images属性时，它们是已经居住。所以我的问题是，可以将Lazy Load与MySQL和Code First一起使用吗？

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?

我知道Linq在使用它之前不执行查询，所以也许DataReader仍然是开放的时候懒惰加载图像，这就是为什么会出现问题。

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

推荐答案

...所以也许DataReader仍然是开放的时候懒惰加载图像，这就是为什么发生问题。

...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.

这正是发生了什么，但我认为不太理由你的想法。 DataReader仍然是打开的，不是因为Linq中的延迟执行，而是因为当您尝试访问尚未加载的其他属性时，仍然会遍历查询结果。当您调用 .ToList（）时，结果将一次返回，并存储在内存中的列表< TEntity> 在客户端上，而不是一次返回1条记录。

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.

您可以使用设置 MultipleActiveResultSets = true在MS SQL Server中解决此问题在连接字符串中，但MySQL不支持此设置。但是，您应该能够做的是使用 .Include（tablename）

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

这篇关于MySQL + Code First + Lazy Load问题！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！