Many to many relationships not generated automatically in Entity Framework 7!

In Entity Framework 7, many to many relationships will not be generated automatically and transparently 'behind the scenes'. So what is a developer to do?

THIS POST HAS BEEN RETIRED

The .NET platform underwent considerable churn in late 2015, including the renaming of most of the associated technlologies under the "Core" banner, and a lot of the frameworks were heavily refactored resulting in a lot of code & behavior changes.

The content of this post should be considered inaccurate at best.

As I mentioned in my previous post: Entity Framework 7 (EF7), is a whole different beast from EF6 and before.

One such change in EF7 is in the way it handles many to many relationships between entities … or, to be more accurate, the fact that it doesn’t; at least not in the way you may have become accustomed to if you’ve used EF6 before!

In EF6 Code First, for example, one could declare a couple of entities and the relationships between those entities. If configured to represent a many to many relationship, EF would construct a ‘join table’ with fields combining the primary keys of both entities; this join table would define the many to many relationship between these entities.

For example, let’s define the entities for a simple blog:

public class Blog
{
	public Guid Id { get; set; }
	public string Name { get; set; }
	public List<Post> Posts { get; set; }
	// ...
}

public class Post
{
	public Guid Id { get; set; }
	public Guid BlogId { get; set; }
	public List<Author> Authors { get; set; }
	// ...
}

public class Author
{
	public Guid Id { get; set; }
	public List<Post> Posts { get; set; }
	// ...
}

If we generate the initial migration for this model, we’ll find an additional entity not listed above:

CreateTable(
	"dbo.PostAuthors",
	c => new
		{
			Post_Id = c.Guid(nullable: false),
			Author_Id = c.Guid(nullable: false),
		})
	.PrimaryKey(t => new { t.Post_Id, t.Author_Id })
	.ForeignKey("dbo.Posts", t => t.Post_Id, cascadeDelete: true)
	.ForeignKey("dbo.Authors", t => t.Author_Id, cascadeDelete: true)
	.Index(t => t.Post_Id)
	.Index(t => t.Author_Id);

EF6 Magic!

Where did PostAuthors come from? EF6 recognizes that the Post entity defines a collection of Author, and Author defines a collection of Post. From this, EF6 determines that there is a many to many relationship between Post and Author and constructs a PostAuthor entity with a composite key referencing the Primary Keys (Id fields) of the Post and Author entities.

This ‘magic’ lets the developer largely ignore the needs of the relational database under the hood, and focus on creating an abstract data model.

However, relying on ‘magic’ like this can come at a cost.

What’s wrong with magic?

Some developers frown upon frameworks like EF6 doing ‘magic’, generating types, data structures, and/or entities behind the scenes without direct developer involvement.

There is merit to these concerns: Many developers who employ abstractions / ORM’s like EF to avoid having to deal with the underlying database technology being used can find their apps and systems’ performance, scalability, reliability and safety can suffer greatly as a consequence.

Developers should understand key aspects of building systems that access databases - regardless of which database is being used: It’s important that database systems are carefully engineered to use the database engine in a manner that maximizes performance, throughput, concurrency, etc.

At the same time, developers want to be as productive as possible. Frameworks like EF can add enormous value, reducing the amount of boilerplate / duplicate / unnecessary code as much as possible, but should not be relied upon to ‘hide the database’ - this is a path to guaranteed pain later.

Developers should be willing, then, to sacrifice a little developer productivity during a project’s development in order to save a considerable amount of time and effort during the latter stages of a project.

This certainly appears to be the mindset of the EF team too, since they’ve opted to not support automatic many-to-many mapping in EF7 (at least for the first version).

So how does EF7 do things differently?

EF7 requires a simpler, more explicit approach to representing many to many relationships between entities.

Specifically, it does not transparently build the PostAuthors many to many mapping entity. Instead, you must built it yourself:

public class PostAuthor
{
	public Guid PostId { get; set; }
	public Guid AuthorId { get; set; }
	public Post Post { get; set; }
	public Author Author { get; set; }
}

You must then also modify the Post and Author model entities to reference collections of the PostAuthor entity type:

public class Post
{
	public Guid Id { get; set; }
	public Guid BlogId { get; set; }
	public List<PostAuthor> PostAuthors { get; set; }
	// ...
}

public class Author
{
	public Guid Id { get; set; }
	public List<PostAuthor> PostAuthors { get; set; }
	// ...
}

When you generate a migration from this model, the relationships between Blog, Post, Author, and PostAuthor are all correctly declared, resulting in the expected SQL DDL statements are correctly generated:

-- ...
CREATE TABLE [Author] (
    [Id] uniqueidentifier NOT NULL,
    [FirstName] nvarchar(max),
    [LastName] nvarchar(max),
    CONSTRAINT [PK_Author] PRIMARY KEY ([Id])
);
CREATE TABLE [Blog] (
    [Id] uniqueidentifier NOT NULL,
    [Title] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
);
CREATE TABLE [Post] (
    [Id] uniqueidentifier NOT NULL,
    [BlogId] uniqueidentifier NOT NULL,
    [Title] nvarchar(max) NOT NULL,
	-- ...
    CONSTRAINT [PK_Post] PRIMARY KEY NONCLUSTERED ([Id]),
    CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([Id])
);
CREATE TABLE [PostAuthor] (
    [PostId] uniqueidentifier NOT NULL,
    [AuthorId] uniqueidentifier NOT NULL,
    CONSTRAINT [PK_PostAuthor] PRIMARY KEY ([PostId], [AuthorId]),
    CONSTRAINT [FK_PostAuthor_Author_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [Author] ([Id]),
    CONSTRAINT [FK_PostAuthor_Post_PostId] FOREIGN KEY ([PostId]) REFERENCES [Post] ([Id])
);
-- ...

So, Where Are We?

The lack of ‘magical’ support for many to many relationships in EF7 is a significant change to how EF has worked in the past. While this may feel like a step backwards for some, I think it actually makes one think more carefully about one’s database design and declare the structure of one’s database more concretely. This is a good thing

Now, of course, the EF team may receive sufficient feedback from customers and/or the community that results in them restoring this feature, but I hope you’ll agree that this change may, in fact, end up being better in the long run. On the other hand, if you feel it’s important that they do restore this feature in the future, open an issue in the EF7 GitHub repo, and be sure to tell me why I am wrong in the comments below ;)

If you liked this post, or . If you have feedback / ideas / suggestions, comment below: