0

How can I use Eager Loadign in ASP.NET 4.5 / EF6 / MySQL to acheive the following results.

I have the following MySQL tables (sample):

table_1

  • t1_id
  • t1_name
  • t2_id (FK)

table_2

  • t2_id
  • t2_name

table_3

  • t3_id
  • t1_id (FK)
  • t3_name

I am using ASP.NET 4.5 / Entity Framework 6 / MySQL 5. with the latest .NET connector and develop in Visual Studio 2013.

I want to know how can I use eager loading to get the following results:

Goal

A table 3 item can have several t1 IDs and the T3 row that corresponds to the FK relationship and an array of all the rows that match the T3>T1 FK relationship.

T1 {
     t1_id = 1,
     t1_name = "something",
     t2_id = 3,
       T2 {
           t2_id = 3,
           t2_name = "something 2"
          },
     Collection {
       T3 {
           1: { 
             t3_id = 5,
             t3_name = "name"
             t1_id = 1
            },
           2: {
              t3_id = 6,
              t3_name = "name2",
              t1_id = 1
        },

       }
     }

I want to have an object that contains all the data from the related table's relationships based on the foreign key relationship that I created in MySQL WorkBench for each table.

Liron Harel
  • 10,819
  • 26
  • 118
  • 217
  • Have you done any work creating your EDMX? It sounds like you're a little ways from worrying about eager loading vs lazy loading. – Mike Aug 01 '14 at 12:44
  • Yes, I created the EDMX and it automatically generated objects for each table. – Liron Harel Aug 01 '14 at 12:48
  • Perhaps you can post a screenshot of your EDMX. All of this should be easily handled with navigation properties. – Mike Aug 01 '14 at 12:55
  • I wanted to, but the client preferred not to. That's why I gave a simple example, which I can take and implement into a more complex design. – Liron Harel Aug 01 '14 at 13:14

1 Answers1

1

You need to define your properties in the class with the virtual keyword. So if you want a collection to lazy / eager loaded, you class would look like:

public class A
{
... Some properties ...
}

public class B
{
    public int Id {get;set;}
    public virtual IColection<A> As {get;set;}   
}

You could also try to use the include if you can't change the class:

var b = context.B.Include("As");
Jeff
  • 2,728
  • 3
  • 24
  • 41
  • The classes are self generated by Entity Framework, and I thought there is an option to do that, something built in the EF – Liron Harel Aug 01 '14 at 17:52
  • I only do code first so I'm not very proficient at model first. From the research I've done, the only way to do what you want is use code first and the virtual keyword or use the Include() in you're query. Are you trying to get away from using Include() in every query? – Jeff Aug 01 '14 at 18:11
  • yes, I thought there is an easy way to avoid the include for every query or to create a large query manually. – Liron Harel Aug 01 '14 at 18:12
  • This question doesn't really help you, but maybe it confirms that you can't do it. (What you want would be a nice feature though) http://stackoverflow.com/questions/6042023/entity-framework-4-1-default-eager-loading Maybe this will be the problem that allows you to switch over to code first... ;) – Jeff Aug 01 '14 at 18:17
  • 1
    In fact, I am also looking into switching to MongoDB. I don't expect the database to grow a lot, and I think that I can do fine with denormalization and that's prevents lots of headaches. I will do most of the calculations on the client side. So It's just one two queries. I will need to know how to search, index and sort - but I think it won't be complicated :) – Liron Harel Aug 01 '14 at 18:20