Entity Framework v5 loading relational data

Question

I have two very simple POCO that i want to connect through a one to many relation

public class Menu
{
    public int MenuId { get; set; }
    public bool IsActive { get; set; }
    public ICollection<MenuMember> MenuMembers { get; set; }
}

public  class MenuMember
{
    public int MenuMemberId { get; set; }
    public int MenuId { get; set; }
    public string  ViewRoute { get; set; }
    public bool IsActive{ get; set; }
}

public  class EFDbContext : DbContext
{
    public DbSet<Page> Pages { get; set; }
    public DbSet<Menu > Menus { get; set; }
    public DbSet<MenuMember> MenuMembers{ get; set; }
}

Now what I have to do is very simple , but I all the resources on the internet are suprisingly so vague (or i am too dumb)

I want to write an lambda expression for

SELECT * 
FROM Menu INNER JOIN MenuMembers 
    ON Menu.MenuId = MenuMembers.MenuId 
WHERE Menu.MenuId = 1

I have used this

IEnumerable<Menu> menu 
    = repository.Menus.Where(x => x.MenuId == menuId);

but when I iterate over it, menu.MenuNumbers stays null. I believe it is some sort of lazyloading issue.

What result do you get with this statement? IEnumerable<Menu> menu = repository.Menus.ToList(): — CountZero
– CountZero, Commented May 2, 2013 at 11:08

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Either Include() the relation manually for eager loading:

Entity Framework Loading Related Entities:

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method.

IEnumerable<Menu> menu = repository.Menus
                                   .Include(m => m.MenuMembers)
                                   .Where(x => x.MenuId == menuId);

Or mark the property as virtual so Entity Framework will lazy-load it:

Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed. When using POCO entity types, lazy loading is achieved by creating instances of derived proxy types and then overriding virtual properties to add the loading hook.

public class Menu
{
    public int MenuId { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<MenuMember> MenuMembers { get; set; }
}

And there's a few other options, be sure to check out the documentation.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 2, 2013 at 11:13

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

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

4 Comments

Muhammad Ahmed AbuTalib Over a year ago

Thanks for the response . I used the include way and I got data from both the tables . I used nested foreach to print the data out . However now this leads to another problem . . How would i use an order by clause with a field of the second table ? .

Muhammad Ahmed AbuTalib Over a year ago

because in the lambda expression , we can use a field of the Menu class , but we can't use a field of the MenuMember class in an orderby extension right ? . Is there any work around here

CodeCaster Over a year ago

@MuhammadAhmedAbuTalib you can just OrderBy(m => m.MenuMembers.SomeAggregateFunction()), but you'll have to use an aggregate function because you cannot order "by collection" as that is not a thing. You'll have to calculate an aggregate over each collection to sort by. You can for example order by the amount of members: OrderBy(m => m.MenuMembers.Count()), but not by a single field of MenuMembers. Anyway that is a totally unrelated question, so if you can't figure it out please ask a new one, showing what you have tried.

Muhammad Ahmed AbuTalib Over a year ago

You are right CodeCaster, sorting is unrelated. Thanks for your help regarding the join .

cuongle · Accepted Answer · 2013-05-02 11:07:47Z

1

You need to declare MenuMembers as virtual

public virtual ICollection<MenuMember> MenuMembers { get; set; }

answered May 2, 2013 at 11:07

cuongle

75.5k30 gold badges155 silver badges212 bronze badges

2 Comments

Muhammad Ahmed AbuTalib Over a year ago

Thanks Le , I did as you mention above and ran across another error . I used a nested foreach to obtain the data from the second table . foreach (var item in menuItems) { foreach (MenuMember mm in item.MenuMembers ) { Console.Write(mm.ViewRoute); } }

Muhammad Ahmed AbuTalib Over a year ago

when the control would reach to the second foreach , it would throw an exception stating that "A previous datareader was already open and needs to be closed" . I think i am doing some mistake here ?

Collectives™ on Stack Overflow

Entity Framework v5 loading relational data

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related