Unit testing NHibernate application with SQLite: it writes to the database but cannot read back

Question

I have an application using NHibernate that is already deployed and working properly, and I'm re-factoring the unit tests to use SQLite for improved performance, and to keep unit test data out of the "real" database.

I have a simple test that creates an Calendar entity, saves it, then tries to read it back and verifies that it's the same object. The write works, but the subsequent select to read it back returns 0 records. A Calendar has a GUID as a primary key, and I understand that requires an extra parameter on the SQLite connection string. This is my connection string:

data source=:memory:;Version=3;New=true;Pooling=true;Max Pool Size=1;BinaryGuid=False

Through the logged SQL statements coming from NHibernate, I see the inserts to write the entity and its dependencies, then the subsequent select statement. It all looks good, but nothing is selected. If I use a file database instead of an in-memory database, I can open up the table in Visual Studio's Server Explorer, and I see the correct data in the tables. If I write a query to try selecting the record, like so:

SELECT     CalendarID, Name, Description
FROM         dbo_Calendars
WHERE     (CalendarID = 'a9cd9820-1694-4645-88d4-f682c5a6b9cc')

it also fails to select anything. I think it's an issue with GUID handling, but I'm flummoxed.

Update

Here's what the test case looks like:

[Test]
    public void SaveAndLoadCalendar()
    {
        Guid calId;

        DAOFactory factory = (DAOFactory)DAOFactory;
        ISession s = factory.SessionManager.CurrentSession;
        using (var tx = s.BeginTransaction())
        {
            Calendar cal = new Calendar("Test Calendar", CalendarType.Test);
            cal.Active = true;
            cal.Browsable = true;
            s.Save(cal);

            tx.Commit();
            calId = cal.ID;
        }

        Logger.InfoFormat("Calendar ID is {0} ", calId);

        s.Clear();

        using (var tx2 = s.BeginTransaction())
        {
            Calendar cal = s.Get<Calendar>(calId);
            Assert.IsNotNull(cal, "Could not retrieve saved calendar");
            Assert.AreEqual("Test Calendar", cal.Name, "Saved calendar not equal to original calendar");
        }
    }

Answer 1

I would guess that the transaction handling could be the problem.

So maybe the transaction inserting the record is not yet committed and so the (different) transaction performing the select does not yet see the new data - so the select returns nothing.

Answer 2

I figured it out, and the problem isn't NHibernate or SQLite, it's me. Each Calendar has an associated Theme. In our production database, these are manually entered, and expected to exist in advance. Now that I'm using SQLite for testing, I'm starting with an empty database, and this reference data isn't pre-populated. NHibernate's Select statement to fetch the Calendar uses an inner join on the Themes table, and with nothing in that table, the select will return empty. D'oh.

After updating my test setup code to save the default theme, the test passes.