EF 4.1, database first and Many-to-Many relationships - How to get all sub-objects?

Question

Let's say I have the following tables in a database forming a Many-to-Many Relationship. And in an ASP.Net MVC project using EF 4.1, I have corresponding POCO entities and a Repository layer to access the database.

People: PersonId PK, Firsname, Lastname
FavoriteRestaurants: ID PK, PersonId, RestaurantId
Restaurants: ResaurantId PK, Name

If I have a PersonId, what is the best way to list all this person's favorite restaurant names?

Or how would I do to initialise this Enumerable based on the person's Id using a repository layer?

IEnumerable MyFavoriteRestaurants = ???

I know I could use foreach to loop on the Person.FavoriteRestaurants collections an retrieve each FavoriteRestaurant.Restaurant one by one, but I was wondering if there were a more elegant way to do this in one line of code.

UPDATE

Here is an example of a Person POCO entity class in my project. This code has been generated by a Microsoft template downloaded from here:

http://visualstudiogallery.msdn.microsoft.com/23df0450-5677-4926-96cc-173d02752313/

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;

namespace UpDir.Domain
{
    public partial class Person : EntityBase
    {
        #region Primitive Properties

        public virtual int PersonId
        {
            get;
            set;
        }

        public virtual string Firstname
        {
            get;
            set;
        }

        public virtual string Middlename
        {
            get;
            set;
        }

        public virtual string Lastname
        {
            get;
            set;
        }

        public virtual string Nickname
        {
            get;
            set;
        }

        public virtual string EncryptedEmail
        {
            get;
            set;
        }

        public virtual string Photo
        {
            get;
            set;
        }

        public virtual short StatusId
        {
            get { return _statusId; }
            set
            {
                if (_statusId != value)
                {
                    if (Status != null && Status.StatusId != value)
                    {
                        Status = null;
                    }
                    _statusId = value;
                }
            }
        }
        private short _statusId;

        #endregion
        #region Navigation Properties

        public virtual ICollection<Member> Members
        {
            get
            {
                if (_members == null)
                {
                    var newCollection = new FixupCollection<Member>();
                    newCollection.CollectionChanged += FixupMembers;
                    _members = newCollection;
                }
                return _members;
            }
            set
            {
                if (!ReferenceEquals(_members, value))
                {
                    var previousValue = _members as FixupCollection<Member>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupMembers;
                    }
                    _members = value;
                    var newValue = value as FixupCollection<Member>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupMembers;
                    }
                }
            }
        }
        private ICollection<Member> _members;

        public virtual ICollection<Message> Messages
        {
            get
            {
                if (_messages == null)
                {
                    var newCollection = new FixupCollection<Message>();
                    newCollection.CollectionChanged += FixupMessages;
                    _messages = newCollection;
                }
                return _messages;
            }
            set
            {
                if (!ReferenceEquals(_messages, value))
                {
                    var previousValue = _messages as FixupCollection<Message>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupMessages;
                    }
                    _messages = value;
                    var newValue = value as FixupCollection<Message>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupMessages;
                    }
                }
            }
        }
        private ICollection<Message> _messages;

        public virtual ICollection<Notification> Notifications
        {
            get
            {
                if (_notifications == null)
                {
                    var newCollection = new FixupCollection<Notification>();
                    newCollection.CollectionChanged += FixupNotifications;
                    _notifications = newCollection;
                }
                return _notifications;
            }
            set
            {
                if (!ReferenceEquals(_notifications, value))
                {
                    var previousValue = _notifications as FixupCollection<Notification>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupNotifications;
                    }
                    _notifications = value;
                    var newValue = value as FixupCollection<Notification>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupNotifications;
                    }
                }
            }
        }
        private ICollection<Notification> _notifications;

        public virtual Status Status
        {
            get { return _status; }
            set
            {
                if (!ReferenceEquals(_status, value))
                {
                    var previousValue = _status;
                    _status = value;
                    FixupStatus(previousValue);
                }
            }
        }
        private Status _status;

        public virtual ICollection<UpDirEmail> FromEmails
        {
            get
            {
                if (_fromEmails == null)
                {
                    var newCollection = new FixupCollection<UpDirEmail>();
                    newCollection.CollectionChanged += FixupFromEmails;
                    _fromEmails = newCollection;
                }
                return _fromEmails;
            }
            set
            {
                if (!ReferenceEquals(_fromEmails, value))
                {
                    var previousValue = _fromEmails as FixupCollection<UpDirEmail>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupFromEmails;
                    }
                    _fromEmails = value;
                    var newValue = value as FixupCollection<UpDirEmail>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupFromEmails;
                    }
                }
            }
        }
        private ICollection<UpDirEmail> _fromEmails;

        public virtual ICollection<UpDirEmail> ToEmails
        {
            get
            {
                if (_toEmails == null)
                {
                    var newCollection = new FixupCollection<UpDirEmail>();
                    newCollection.CollectionChanged += FixupToEmails;
                    _toEmails = newCollection;
                }
                return _toEmails;
            }
            set
            {
                if (!ReferenceEquals(_toEmails, value))
                {
                    var previousValue = _toEmails as FixupCollection<UpDirEmail>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupToEmails;
                    }
                    _toEmails = value;
                    var newValue = value as FixupCollection<UpDirEmail>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupToEmails;
                    }
                }
            }
        }
        private ICollection<UpDirEmail> _toEmails;

        public virtual ICollection<UpDirEvent> UpDirEvents
        {
            get
            {
                if (_upDirEvents == null)
                {
                    var newCollection = new FixupCollection<UpDirEvent>();
                    newCollection.CollectionChanged += FixupUpDirEvents;
                    _upDirEvents = newCollection;
                }
                return _upDirEvents;
            }
            set
            {
                if (!ReferenceEquals(_upDirEvents, value))
                {
                    var previousValue = _upDirEvents as FixupCollection<UpDirEvent>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupUpDirEvents;
                    }
                    _upDirEvents = value;
                    var newValue = value as FixupCollection<UpDirEvent>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupUpDirEvents;
                    }
                }
            }
        }
        private ICollection<UpDirEvent> _upDirEvents;

        #endregion
        #region Association Fixup

        private void FixupStatus(Status previousValue)
        {
            if (previousValue != null && previousValue.People.Contains(this))
            {
                previousValue.People.Remove(this);
            }

            if (Status != null)
            {
                if (!Status.People.Contains(this))
                {
                    Status.People.Add(this);
                }
                if (StatusId != Status.StatusId)
                {
                    StatusId = Status.StatusId;
                }
            }
        }

        private void FixupMembers(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (Member item in e.NewItems)
                {
                    item.Person = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (Member item in e.OldItems)
                {
                    if (ReferenceEquals(item.Person, this))
                    {
                        item.Person = null;
                    }
                }
            }
        }

        private void FixupMessages(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (Message item in e.NewItems)
                {
                    item.Person = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (Message item in e.OldItems)
                {
                    if (ReferenceEquals(item.Person, this))
                    {
                        item.Person = null;
                    }
                }
            }
        }

        private void FixupNotifications(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (Notification item in e.NewItems)
                {
                    item.Person = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (Notification item in e.OldItems)
                {
                    if (ReferenceEquals(item.Person, this))
                    {
                        item.Person = null;
                    }
                }
            }
        }

        private void FixupFromEmails(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (UpDirEmail item in e.NewItems)
                {
                    item.FromPerson = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (UpDirEmail item in e.OldItems)
                {
                    if (ReferenceEquals(item.FromPerson, this))
                    {
                        item.FromPerson = null;
                    }
                }
            }
        }

        private void FixupToEmails(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (UpDirEmail item in e.NewItems)
                {
                    item.ToPerson = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (UpDirEmail item in e.OldItems)
                {
                    if (ReferenceEquals(item.ToPerson, this))
                    {
                        item.ToPerson = null;
                    }
                }
            }
        }

        private void FixupUpDirEvents(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (UpDirEvent item in e.NewItems)
                {
                    item.Person = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (UpDirEvent item in e.OldItems)
                {
                    if (ReferenceEquals(item.Person, this))
                    {
                        item.Person = null;
                    }
                }
            }
        }

        #endregion
    }
}

@Jayantha No, I am using database first. – Jean-François Beauchamp Apr 29 '12 at 17:34 — Jean-François Beauchamp, Apr 29 '12 at 17:34

score 2 · Answer 1 · answered Apr 29 '12 at 05:18

2

You don't need to think too much about that, EF will do it for you if you have defined your entity classes correct way like this,

Public class Person{
   public int  PersonId {get;set;}
   public string Firsname{get;set;}
....
   public virtual IList<Resturent> Resturents {get;set;}
}

Public class Restaurant{
   public int  ResaurantId {get;set;}
   public string Name{get;set;}
....
   public virtual IList<Resturent> People{get;set;}

}

Then you can simply access person.Restaurants to get all restaurants.

answered Apr 29 '12 at 05:18

Jayantha Lal Sirisena

21,216
11
71
92

I don't think he's using code first but this might help someone else. (BTW, the key is the virtual navigation props.) – tzerb Apr 29 '12 at 05:19
hmm.. But it doesn't matter the method he can do this in any method. But the approach will be different. – Jayantha Lal Sirisena Apr 29 '12 at 05:21
As a matter of fact, I am using Database First, and I am using a tt template I got with Nuget to generate my POCO entities. What would be the approach then to come to the same result? – Jean-François Beauchamp Apr 29 '12 at 17:26
Your tt template did not generated the POCO classes like mine ? Then share your code of the POCO classes here, You can change the generated mapping if you want to achieve this. – Jayantha Lal Sirisena Apr 30 '12 at 03:45
@Jayantha See my Update in my initial post. I added a code sample for a Person POCO entity class. – Jean-François Beauchamp Apr 30 '12 at 03:56
@Jayantha How do you generate your POCO entities. I just realized that my TT template is not coming from Nuget. It is a Microsoft template, but I don't remember where I took it from. – Jean-François Beauchamp Apr 30 '12 at 03:57
@Jayantha By the way, you won't find anything about "restaurants" in my code sample. This was just an example I was giving. You will however find the People-Members-Organizations Many-to-Many relationship. – Jean-François Beauchamp Apr 30 '12 at 04:01
@tzerb Can you tell me more about virtual navigation properties in a database first context? I researched this using Google, but I did not find anything interesting. – Jean-François Beauchamp Apr 30 '12 at 04:04
Seems like you already have navigation properties. In the domain where is your mapping. If People to Organizations is many to many you don't have to have the `Members` class as a domain entity. The rest will be done by the EF. – Jayantha Lal Sirisena Apr 30 '12 at 04:21
@Jayantha Well, no, I need the Member class. In my database, I have People (PersonId PK, Firsname, Lastname, Email, ...), Members (MemberId PK, PersonId, OrganisationId, MemberTypeId, ... other stuff...), and Organisations (OrganisationId, Name, Address, ... other stuff...). However, I would like to have such a thing as an Organisations collection property in my Person class. The thing is that the Members table is not just a link table. It contains additional stuff. – Jean-François Beauchamp Apr 30 '12 at 04:27
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10686/discussion-between-jean-francois-beauchamp-and-jayantha) – Jean-François Beauchamp Apr 30 '12 at 05:36

score 0 · Accepted Answer · answered Jul 09 '12 at 17:15

0

I finally figured it out. This is how I am doing it:

IEnumerable<Restaurants> MyFavoriteRestaurants = person.FavoriteRestaurants.Select(m => m.Restaurants);

answered Jul 09 '12 at 17:15

Jean-François Beauchamp

5,485
8
43
77

EF 4.1, database first and Many-to-Many relationships - How to get all sub-objects?

2 Answers2