Lazy loading data in Entity Framework 6 with MySql not working

Question

I'm working on a code-first EF6 project using MySql. I have setup my data classes such that their related data objects should be lazy-loaded, but they seem to run very slowly each time they are called.

This is an example of one of my entity classes:

[Table("Carrier")]
public class DBCarrier
{
    [Key]
    [Column("CarrierId")]
    public int carrierId { get; set; }

    [MaxLength(128), Required]
    public string CarrierName { get; set; }

    public virtual ICollection<DBDepot> Depots { get; set; }
    public virtual ICollection<DBZone> Zones { get; set; }
}

But when I call the Zones on an object of this class like this (this was all in one statement, but I separated it out to try and find out where the problem was):

ICollection<DBZone> zones = carrier.Zones;
IEnumerable<DBZone> zones1 = zones.Where(x => x.Postcode == postcode);
return zones.Select(x => x.ZoneName).FirstOrDefault();

Every time I call carrier.Zones it takes about 8 seconds to run. I thought that by defining zones as an ICollection would delay execution of the database query.

Alexander Derck · Accepted Answer · 2016-02-12 08:34:36Z

2

ICollection means the data is definitely already materialized in memory. The only way to get your desired behavior is to use an interface which supports deferred execution and there's only two:

IQueryable the query is built up using Expressions and is used to fire queries upon remote data sources (for example sql database).
IEnumerable the query is built up using Delegates and is used to fire queries upon datasets already in memory.

Using any of these 2 interface types will cause your query to be executed when you iterate the data using First(), FirstOrDefault(), ToList() or ToArray().

But seeing you only need the names of the zones your query is pretty inefficient. You fetch the whole DBZone object but you only take the name, I would suggest just fetching the names:

return carrier.Zones.FirstOrDefault(z => z.Postcode == postcode).Select(z => z.ZoneName);

For this you need to keep carrier as an IQueryable of course.

Edit: Just noticed "Every time I call carrier.Zones it takes about 8 seconds to run."

This is the expected behavior for lazy loading. The data will get fetched from the database when you access it => Database call for the carrier, and another call to database for the zones when you access them. This can be avoided using eager loading (Include method) to get all the desired data in one trip to the database.

edited Feb 12, 2016 at 8:34

answered Feb 12, 2016 at 8:27

Alexander Derck

14.7k5 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mikeyq6 Over a year ago

Makes sense. I will try that later.

Collectives™ on Stack Overflow

Lazy loading data in Entity Framework 6 with MySql not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related