Frank Frank - 2 months ago 11
MySQL Question

How do I express a "has many through" relationship in Entity Framework 5?

I am attempting to use Entity Framework 5 to query an existing MySQL database. I used code-first to create a code-based model that maps to an existing database following this tutorial on MSDN.

I have two tables:

users
and
buddies
. A
User
has an
id
, a
name
and an
email
. A
Buddy
has a
user_id
and a
buddy_id
. A
User
has many
Buddies
(which are also
Users
). The
buddy_id
column is a foreign key back into the
Users
table. So each
User
has many
Users
through
Buddies
.

public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public IList<User> Buddies { get; set; }
}


Here is my database access code:

using (var db = new Models.fooContext())
{
foreach (Models.user user in db.users)
{
var u = new User
{
Id = user.id,
Name = user.name,
Email = user.email,
Buddies = new List<User>()
};

// this is obviously very inefficient but I am just
// trying to get anything to work
foreach (Models.buddy buddy in db.buddies) // this line throws an exception
{
if (buddy.user_id == user.id)
{
var b = new User();
// take buddy.user_id and find this user in the user table
u.Buddies.Add(b);
}
}

// serialize User and write it to a file here
}
}


This code throws the following exception on the line indicated above:


System.Data.EntityCommandExecutionException: an error occurred while executing the command definition. See the inner exception for details."


The inner exception is a
MySql.Data.MySqlClient.MySqlException
with the message


There is already an open DataReader associated with this Connection which must be closed first.


My questions:


  1. How do I create a relationship that tells EF that each
    User
    has many
    Buddies
    ?

  2. Once EF understands that a
    User
    has many
    Buddies
    , how do I use the
    buddy_id
    to find the
    User
    record for that
    Buddy
    ?


Answer

You have one data type that you are calling separate names. That is a little confusing. However To get this to work with code first you just need the following fluent configuration in your Custom DbContext class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<user>().
      HasMany(c => c.Buddies).
      WithMany().
      Map(
       m =>
       {
          m.MapLeftKey("user_id");
          m.MapRightKey("buddy_id");
          m.ToTable("buddies");
       });
}

This assuming your user class looks like this:

[Table("user")]
public class user
{
    public int id { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public virtual List<user> Buddies { get; set; }
}

If you use the above method every user object you have will have a navigation property on it called Buddies. When querying for users you will want to eager load buddy users, do:

context.users.Include("Buddies")

Further, To address your issue with multiple readers. It is because you have not enumerated the query (db.users) from your first loop. To address that you can enumerate the query like so:

var users = context.users.Include("Buddies").ToList();
foreach(var user in users)....

And if you use the configuration above, you don't need to try and match id's you simply acquire the list (null field if new) of buddies from the user using the buddies navigation property (virtual,lazy loaded), do:

user.Buddies

As you can see you don't really need a 'Model.buddy' (since it only contains an id mapping) Entity framework will take care of the linking. However if you're not always including the Buddies in your user query you may want the table. Which would be queried with LINQ in the following way:

var userBuddies = db.buddies.Where(buddy=>buddy.user_id == user.id).ToList();
//An enumerated list of user buddies 
//do stuff
Comments