Longing to know

Last night I was working on a project that had some many to many database relationships. On one side of the relationship, someone decided to use zero as the default “nothing selected” sort of key. This simply doesn’t work with Entity Framework, or probably most other .NET ORMs.

What’s the problem with zero? In CLR terms, how does one know the difference between an Integer being set or not being set when it has a default value of zero? There simply isn’t any way to tell based on this salient value. This is why a zero primary key in your databases is a bad idea. Entity Framework, specifically, will do weird things when you attempt to create the entry in the linking (many to many) table when one side of the join is zero. I found that it would attempt to insert a NULL value when it saw zero. Zero is magically, nothing. This makes sense for a semantic CLR viewpoint.

Well, what happens when you’re forced to simply deal with it? How does one deal with this?? It’s not a horribly difficult problem to surmount, but it does require hooking into the SqlServer level of Entity Framework and running raw SQL.

Fortunately, as I’ve blogged about previously, using the EF metadata in raw queries is not super hard. I think that post is specifically for EF6, but I do have equivalent code for EF Core. The example I show below will focus on EF Core as well.

In my scenario, I’m looping over a bunch of objects and want to creating the linking entry. The model for the linking entry only has the (2) Foreign Keys and nothing else. The process, then, would be to get the (2) keys and create the INSERT statement. We’ll only do this when we detect that one half of the relationship is zero.

First, we get the schema information:

var schemaName = _repo.DbSchema;
var tableName = _repo.GetTableName();
var leftIdCol = _repo.GetColumnName<MyLinkObject>(x => x.LeftId);
var rightIdCol = _repo.GetColumnName<MyLinkObject>(x => x.RightId);
var manualInsert = string.Empty;
var manualInsertedLinks = new List<MyLinkObject>();

Second, we figure out what has to be manually inserted and what EF can handle. For the relationships that EF can handle (because they’re more than nothing), we’ll simply add our created entity to one of the collections to the left or right of the entity. For the relationships that EF can’t handle, we’ll create a big insert with all necessary rows specified.

foreach (var linkViewModel in linksToAdd)
{
    var link = new MyLinkObject()
    {
        LeftId = linkViewModel.LeftId,
        RightId = linkViewModel.RightId
    };

    if (linkViewModel.Id == 0)
    {
        manualInsert = string.IsNullOrWhiteSpace(manualInsert) ?
            $"INSERT INTO [{schemaName}].[{tableName}] ([{leftIdCol}], [{rightIdCol}]) VALUES ({link.LeftId}, {link.RightId})" :
            $"{manualInsert}, ({link.LeftId}, {link.RightId})";
        manualInsertedLinks.Add(link);
    }
    else
    {
        // Non-zero, so just add to the collection on either side of the link
        leftObj.Links.Add(link);
    }
}

Finally, we need to execute the save on our DbContext and ExecuteNonQuery for the INSERT. Hooking into the “Database” object the EF Core provides may differ slightly between providers. I’m only considering the case where the underlying persistence engine is MSSQL. My repository class has Transaction helpers for creating and participating in transactions. Interestingly, we need this transaction information, when participating in a transaction, for the purposes of executing a non-query. Fortunately, EF Core makes it easy to get the current DbTransaction. Our code will execute the insert and DbContext.Save().

_repo.CreateTransaction();

if (!string.IsNullOrWhiteSpace(manualInsert))
{
    var connection = _repo.DbContext.Database.GetDbConnection();
    if (connection.State == System.Data.ConnectionState.Closed)
    {
        connection.Open();
    }

    using (var command = connection.CreateCommand())
    {
        command.Transaction = _repo.DbContext.Database.CurrentTransaction.GetDbTransaction();
        command.CommandText = manualInsert;
        command.ExecuteNonQuery();
    }
}

_repo.Save();
_repo.CommitTransaction();

There it is in all its glory. It’s a big work-around to deal with nothing. My recommendation is to avoid dealing with nothing.