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
}
}
I finally figured it out. This is how I am doing it:
IEnumerable<Restaurants> MyFavoriteRestaurants = person.FavoriteRestaurants.Select(m => m.Restaurants);