Code First MVC 4 EF 5 many-to-many Join

问题:

I've been struggling with this for the past few days and can't seem to figure this out. I'm using Code First approach with the MVC 4 and EF 5. I have a many-to-many relationship setup through the fluent API. Here is the sample code:

    public class Class
    {
        public Class()
        {
            Teachers = new List<User>();
        }
        /// <summary>
        /// Unique ID in the system
        /// </summary>
        [Key]
        public long Id { get; set; }

        /// <summary>
        /// Array of users (Teachers and Interventionists) associated with that class
        /// </summary>
        public List<User> Teachers { get; set; }
    }

User class:

public class User
{
    public User()
    {

    }
    /// <summary>
    ///     Unique Id in the system 
    ///</summary> 
    [Key]
    public long Id { get; set; }

    public List<Class> Classes { get; set; }
}

The fluent API is here:

    modelBuilder.Entity<Class>().HasMany(m => m.Teachers).WithMany(t => t.Classes).Map(m =>
            {
                m.ToTable("ClassTeachers");
                m.MapLeftKey("ClassId");
                m.MapRightKey("UserId");
            });

What's going on:

The EF is creating the ClassTeachers table and when I select all the classes I receive the data as expected. The Classes have the correct Teachers within the returned data.

What I need help with:

I'm trying to return all the Classes that have a specific teacher. I was trying something like this:

    var classesTeachers =
        from classes in data.Classes from u in data.Users
        where u.Id == mockUser.Id
        select new { classes.Id, classes.Label, u.FirstName };

However, I'm not getting back the correct data. Normally, there would be a third table that I could join in between to get back the required results, however, this isn't available with the EF Code First approach. I'm really at a loss...

Thank you.

回答1:

Try this:

var classesTeachers = from c in data.Classes
                      where c.Teachers.Any(t => t.Id == mockUser.Id)
                      select c;