Imagining a better EntityFramework

There is a lot to like in EntityFramework... and some less desirable parts.

The good

Automatic translation of C# Linq queries to SQL for efficient querying

For example, the following asks the database to filter the books rather than loading them all and discarding the books by other authors.

var books = ctx.Books    .Where(b => b.Author == "John Smith").ToList();

Using anonymous objects to select only the data you need

For example, the following only fetches the Authors.Name and Books.Title columns.

var authorNamesAndBookTitles = ctx.Authors    .Select(a => new     {         Name = a.Name,         Books = a.Books.Select(b => b.Title)     });

Using Automapper's ProjectTo to load only the data that you want

For example, if AuthorDto contains only a subset of the properties of Author, the following will only fetch the required columns.

Mapper.CreateMap<Author, AuthorDto>();var authorsAndBooksDtos = db.Employees    .ProjectTo<AuthorDto>().ToList();

Explicit joins for (mostly efficient) loading of related data

This will load all the authors along with the books each one has written.

var authorsWithBooks = ctx.Authors    .Include(a => a.Books).ToList();

Automatic change tracking

Just load some data, modify it, and then call SaveChanges() and all the changes get saved.

var authorWithBooks = ctx.Authors    .Include(a => a.Books)    .Where(b => b.Id == authorId).First();authorWithBooks.Name = "new name";authorWithBooks.Add(new Book { Title = "book title" });ctx.SaveChanges();

Automated generation of code-first migrations

Just change your entity classes, run Add-Migration NameOfMigration in the console, and a new migration class gets generated.

The not so good

You can't mix returning anonymous objects and entity types

For example, the following won't work.

var authorNamesAndBooks = ctx.Authors    .Select(a => new     {         Name = a.Name,         Books = a.Books    });

Change tracking

Coding a feature usually involves loading some data, verifying the state of the data, modifying the data, and maybe sending an email or generating a document. Change tracking makes this pretty easy.

But if the verification part is done by a function written by a less experienced developer, you just have to hope that it doesn't modify the data. For example, the following is valid code...

if (author.IsActive = true) { ... }

but it mistakenly assigns true to author.IsActive, and this change will be persisted.

Explicit joins are inefficient when loading data from many related tables

Unfortunately, when you include data from many related tables the query that is generated is not particularly efficient. Loading the related entities separately can often be quicker.

EntityFramework.Plus provides IncludeOptimized to help with this scenario, but doesn't work with many-to-many relationships.

Related entities can be included unexpectedly even with AsNoTracking()

For example, this loads an author with his books and nothing else right?

var authorAndBooks = ctx.Authors    .Where(a => a.Name == "John Smith")    .Include(a => a.Books).ToList();

So why is authorAndBooks.First().Author not null?

My guess is that as EntityFramework had loaded the author into memory, it realised it could set the Author property of each book correctly from the data in cache.

This could be problematic when you want to send a minimal set of data over the wire.

Migrations

EntityFramework's Add-Migration command compares the current state of the database, the snapshot of the database shape stored in the most recent migration, and the shape of your entity classes.

Hence, if you add migrations to two independent branches and then merge the result EntityFramework assumes that the least recent migration hasn't been generated nor applied. The fix is easy but tedious, you have to generate a new migration using the -IgnoreChanges option in order to tell it that the current database state is good.

Saving untracked entities is fraught with danger

For example, the following code duplicates the user.

var untrackedUser = ctx.Users.First(u => u.Id == userId).AsNoTracking();var book = new Book {     Title = "new book title",    Author = untrackedUser,};ctx.Books.Add(book);ctx.SaveChanges();

Untracked entities can come from many sources, from data sent from a client app, from a NoSQL cache, from a query with AsNoTracking, etc.

Might there be a better way...?

Here are a few ideas?

Migrations

An upgrade to EntityFramework Core would solve the problem, but would exchange it for another. Namely, many-to-many joins are not supported. In EntityFramework 6 you simply need ICollection properties in both classes, for example...

class User {    public string Name { get; set; }    public ICollection<OnlineCourse> OnlineCourses { get; set; }}class OnlineCourse{    public string Title { get; set; }    public ICollection<User> Users { get; set; }}

whereas in EntityFramework Core you need to add an Enrolment entity in between them, and link Users to their Enrolments rather than directly to their OnlineCourses.

class Enrolment{    public User User { get; set; }    public OnlineCourse OnlineCourse { get; set; }}

This might not be such a bad thing as it makes things more explicit. On the other hand, merging migrations is an infrequent hassle and definitely not a showstopper for us.

Imagining a new API for writing data to the database

EntityFramework.Plus provides a bulk update API that allows you to modify properties of a an entity without first loading the entities. Here is one of their examples...

// UPDATE all users inactive for 2 yearsvar date = DateTime.Now.AddYears(-2);ctx.Users.Where(x => x.LastLoginDate < date)         .Update(x => new User() { IsSoftDeleted = 1 });

However this API does not support linked entities.

Could we create a similar API that did support linked entities? Here are a few use cases that I would like it to support...

• Setting a singly linked property without modifying other properties of the linked entity
• Setting a singly linked property and simultaneously modifying other properties of the linked entity
• Creating a new entity and adding it to a list of linked entities on another entity
• Adding an existing entity to a list of linked entities on another entity, with or without modifying some of its properties.
• Removing an item from a list of linked entities without necessarily deleting the linked item

Maybe a call to our imaginary ExplicitUpdate method could return a list of modified properties with their old and new values...

More details to follow...

What about other ORM's?

Dapper is a well known alternative to EntityFramework with a reputation for being fast. But it is only a micro-orm which means that you don't get migrations and you have to write most of your SQL queries by hand. Now I know that I can write SQL queries that are less efficient than those that EntityFramework writes, and I am sure that I would spend weeks rewriting our queries if we decided to move to Dapper.

The thing micro-orm's don't give you is easy migrations. So we would need to find another system for migrations and use T4 to regenerate our entity classes every time.

Micro-ORM's such as Dapper, PetaPoco, linq2db, and many others seem like decent projects, but I can't see us porting our big existing app to any of them in a hurry.

Insight.Database seems to be a more interesting alternative.

Yet another option would be to hide the database access code in an F# project and use one of their SQL Server type providers. Instead of generating model classes using T4, the compiler connects to the database and generates the required classes on the fly. We could still use EntityFramework for legacy code and for migrations, and new code could use a new data access method with very little overhead.

But still, our best bet seems to be to put up with the migrations issue and to add a new API like bulk update on top of EntityFramework.

Conclusion

Immutable data and/or an explicit write API seems to be a sensible way forward for more reliable and testable software.