EF Code First working with many-to-many relationsh

Question

I am using Visual Studio 2010, C# 4.0 and Entity Framework 5.0. I have been using database first development for many years but am trying to move to code first and am running into problems. Reading and searching does not seem to address the problems

I have simplified my problem as follows - I have two classes - Assessors and Documents.

public class Assessor
{
    public int AssessorID { get; set; }
    public virtual List<Document> Documents { get; set; }       
}

public class Document
{
    public int DocumentID { get; set; }
    public string DocumentLocation { get; set; }
    public string DocumentName { get; set; }
    public virtual List<Assessor> Assessors { get; set; }
}

with the context

public class DocumentAssignment : DbContext
{
    public DbSet<Assessor> Assessors { get; set; }
    public DbSet<Document> Documents { get; set; }
}

An assessor can have many documents and a document can have many assessors (a classic many-to-many relationship). I am using convention to create the relationship but have also used the fluent API. I have seeded the document table.

My two questions: ONE - I want to assign documents to assessors - what is the best way to save this to the database?

TWO I have the following method to retrieve documents assigned to an assessor:

public static IEnumerable<MaternalDocument> GetAssignedDocumentList(int UserID, string ConnectionString)
    {
        using (DocumentAssignment dbContext = new DocumentAssignment(ConnectionString))
        {
            return returnValue = dbContext.MaternalAssessments
                .Where(m => m.AssessorID == UserID)
                .Include(m => m.MaternalDocuments)
                .Select(m => m.MaternalDocuments)
                .ToList();
        }
    }

but I cannot get this to compile because of mapping issues. What am I doing wrong?

Answer 1

You have to tell the DbContext about how the many-to-many relationship is set up, by overriding OnModelCreating in DocumentAssignment. Replace AssessorDocuments in this code with your relation table name.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Assessor>()
            .HasMany(a => a.Documents)
            .WithMany(d => d.Assessors)
            .Map(m =>
                {
                    m.MapLeftKey("AssessorID");
                    m.MapRightKey("DocumentID");
                    m.ToTable("AssessorDocuments");
                });
        base.OnModelCreating(modelBuilder);
    }

To assign a Document to an Assessor (assuming a Document exists with DocumentID of 1 and an Assessor exists with an AssessorID of 1):

        using (var context = new DocumentAssignment())
        {
            var assessor = context.Assessors.Find(1);
            var document = context.Documents.Find(1);
            assessor.Documents.Add(document);
            context.SaveChanges();
        }

Your GetAssignedDocumentList method would look something like this:

public static IEnumerable<Document> GetAssignedDocumentList(int UserID)
    {
        using (var context = new DocumentAssignment())
        {
            return context.Documents.Where(d => d.Assessors.Any(a => a.AssessorID == UserID));
        }
    }