Wednesday, 21 January 2015

EntityFramework: getting to the bottom of a murky error

In doing some work using EF which talks to existing databases on MSSQL in production and SQLCE for testing (check out https://github.com/fluffynuts/OrmNomNom.git for an example of using SQLCE and a transient database to do Entity testing -- there's also a bit in there about using NHibernate too), I used the Entity POCO generation tools to quickly spew out the classes that should work when talking to the existing database. I also wrote some FluentMigrator migrations so I could get a temporary database up to speed and set off to use that method to test some database interaction, when:
Boom! InvalidOperationException ("Sequence contains no elements").
Now the befuddling aspect of this exception is where it was thrown. I basically have some code like:
using (var ctx = new DragonContext(db.CreateConnection()))
{
    var unit = new SupplierPriceList() 
    { 
        SupplierPriceListID = "test", 
        SupplierID = "foo", 
        MaterialID = "foo", 
        CompanyCode = "foo", 
        Date = DateTime.Now, 
        SupplierPrice = 1.23
    };
    ctx.SupplierPriceLists.Add(unit);
    ctx.SaveChanges();
}
And the exception is thrown at the point of the call to Add(). Which doesn't seem to make a lot of sense as the temp database being used has no rows in any tables, so of course SupplierPriceLists is empty and one might expect that to be the source of the exception, considering the text, but it really shouldn't matter. Tables are empty all the time and we can add rows to them. Still, the error befuddles...
Looking at the stack trace, I see the last two frames are:
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)
   at System.Data.Entity.Utilities.DbProviderManifestExtensions.GetStoreTypeFromName(DbProviderManifest providerManifest, String name)
Ok, so the last frame explains the raw reason for the exception: something is doing a Single() call on an empty collection. But what? The call one up looks interesting: GetStoreTypeFromName seems to suggest that Entity is doing a lookup to find out how it should store the data for that property. So, on a hunch, comment out all properties except the implied key field (SupplierPriceListID) and the Add() call works. Hm. Perhaps SQLCE doesn't like the
double?
fields? Nope -- uncomment them and things still work.
Then the culprit leaps out. The generator tool has annotated a DateTime? field with:
[Column(TypeName = "date")]
Removing the annotation causes the error to subside -- and the SaveChanges() call on the context passes, on both MSSQL and SQLCE
I thought I'd just make a record of this here as I found nothing particularly useful through much googling and eventually found and resolved the problem based on a hunch. Perhaps this post can save someone else a little mission. Of course the DateTime? value is truncated for the Date field, but hey, that's what the client originally intended...

What's new in PeanutButter?

Retrieving the post... Please hold. If the post doesn't load properly, you can check it out here: https://github.com/fluffynuts/blog/...