The Trials and Tribulations of a Database Context in .NET Core (pt. 1)

When I started using .NET Core this time around, I was pleasantly surprised. But over the past couple of days, I have been unpleasantly surprised. There are two reasons for this, and they both center around the DBContext.

One of my issues is with the caching. If I'm calling a method that fetches from the database, I want it to fetch from the database. If I wanted to cache the results, I'd do that myself.

My other issue is...

The dependency-injection paradox

Microsoft recommends injecting the DbContext into controllers. For testing, I'm a big fan of dependency injection if good-old-fashioned Python monkey patching isn't an option. Which it isn't if you're using C#. My issue isn't with the dependency injection, but with what is being injected. Another recommendation is to use the Repository pattern and inject that instead. Both of those result in thousands of deaths.

What's the issue? Well, say you're running a loop and saving data within that loop. Say we're running through a bunch of objects, converting them to another object, and saving them. For fun-sies, I'll inject a Repository and you can extrapolate that to a DbContext.

class MyController {

    private IRepository Repository;

    public MyController(IRepository repo) {
        this.Repository = repo;
    }

    public ConvertFoosToBars() {
        var toConvert = Repository.FetchFoosToConvert.ToList();

        foreach (foo in toConvert) {
            try {
                var bar = ConvertFoo(foo);
                Repository.SaveNew(bar);
            } catch (Exception e) {
                logger.Error(e);
                foo.Error = e.Message;
            }
        }
    }
}

Looks straightforward enough, right? See if you can spot the bug.
No? Well, that makes sense, there's not really anything else to look at.

OK, assume that ConvertFoo goes over well enough. But THEN it turns out that the newly converted bar is actually a duplicate of another bar so the SaveNew method fails. Also, assume that I don't want to check for duplicates - I just want to fail and move on. Maybe it's a love of speed. Or maybe it's a desire for thread-safety coupled with a slight aversion towards transactions. Maybe it's something ineffable.

Anyways, the Save method fails and I move on...convert more foos to bars because that's my lot in life. Should be fine, there's some error handling there. BUT IT'S NOT FINE.

Now this might not have been a surprise to someone that isn't steeped in the ActiveRecord way of doing things that some other MVC frameworks prefer to follow (Django, Rails, ...). Let's take a look at the Save method, shall we?

class Repository : IRepository {

    private DbContext context;

    public Repository(DbContext context) {
        this.context = context;
    }

    public void SaveNew(Bar bar) {
        context.Bar.Add(bar);
        context.SaveChanges();
    }
}

See the problem now?

That context never changes throughout the controller's lifetime. Which means that when one bad Bar gets added, it stays added. An exception doesn't automagically trigger its removal. As we iterate through the loop, adding more (and valid) Bars, all of the saves fail. This is because the context batches up any changes to be made and performs them all in one big statement. When one of those statements fails, they all fail.

Sure, we could change the MaxBatchSize to 1. That would probably fix the problem (in this instance anyways). But what if we want to batch something down the line? Then we're screwed.

Or maybe we can take out the problem Bar by ourselves. Indeed, we should be able to - many a blog entry and stack overflow question suggested this should work - but it didn't for me. When I marked the entity as detached, it prevented other entities from being saved for some reason that I never figured out. I abandoned the approach because it seemed like kind of a hack anyways. What the hell am I talking about? Here's what the hell I'm talking about.

public void SaveNew(Bar bar) {  
    try {
        context.Bar.Add(bar);
        context.SaveChanges();
    } catch (Exception e) {
        var entry = context.Entry(bar);
        entry.State = EntityState.Detached;  // This means ignore it from now on
    }
}

Now maybe that didn't work because the primary key for Bar just happens to be a string. I don't know. It's a UUID btw. Not, like, "hi tim".

I couldn't figure out any other good ways to reset the list of Bar. Like a Reload or Reset method? A .Remove(entry) method? No. Transactions? Nope. Unit of work? More of the same. The best way sounds like its to embrace scoping and use a different context for each iteration of the loop.

I called this a dependency injection paradox because Microsoft also touts DbContexts as being lightweight, which sounds like it's sure something that we could use more than one of per controller. It sounds like something I should be able to make if for no other reason than to throw them away. And I can't do that if I'm just injecting them once into a controller.

Victory (kind of)

How do I create a new context whenever I want? I like tests, so I'd like to keep the dependency injection, and I don't want to be dealing with connection strings all willy nilly, so I can't directly instantiate them. The solution, of course, turned out to be to push most of my context-centric code into a ContextFactory. This ContextFactory is what gets injected into controllers and all that. It also ended up helping a lot with integration testing.

public class ContextFactory : IContextFactory {  
    private IConfiguration config;

    public ContextFactory(IConfiguration config) {
        this.config = config;
    }

    public DbContext CreateDbContext() {
        var options = new DbContextOptionsBuilder<DbContext>()
                .UseSqlServer(BuildConnectionString());
        return new DbContext(options.Options);
    }
}

You can fance that class up at your pleasure.

Now here is my controller all fixed up (PLUS it's fairly backwards compatible!):

class MyController {

    private IContextFactory ContextFactory;
    private IRepository Repository;

    public MyController(IContextFactory factory, IRepository repository) {
        ContextFactory = factory;
        Repository = repository;
    }

    public ConvertFoosToBars() {

        var toConvert = Repository.FetchFoosToConvert.ToList();

        foreach (foo in toConvert) {
            try {
                var bar = ConvertFoo(foo);
                using (var context = new ContextFactory.CreateDbContext()) {
                    context.Bar.Add(bar);
                    context.SaveChanges();
                }
            } catch (Exception e) {
                logger.Error(e);
                foo.Error = e.Message;
            }
        }
    }
}

Phew! That actually feels great. Each iteration is truly separate from the others.

However, I just put this in yesterday and there is already a dark side of DbContext seeping through the cracks. I'll leave that to part II, as I'm working through them right now. But I'll give you a hint: this article is a pretty interesting read.