Performing an "identity insert"

Jan 21, 2013 at 2:56 PM

Hello, I am very interested in your Effort library and have been working to integrate it into our system. I was previously using a "hacked up" system involving HashSets to accomplish this.

A problem I've had is that all our mock objects are defined using entities and Id values, such as:

 

var mockClient = new Client { Id = 1001, Name = "Test Client" };

var mockProduct = new Product { Id = 2001, ClientId = 1001, Name = "Test Product" };

 

The problem with this approach is that there are CSV and database "loaders", but no "entity loader". I tried writing a loader for these entities (I submitted a pull request with some exception handling that helped me in this case) but found it to be cumbersome doing so many type conversions.

So then I tried simply "adding" these entities to the DbSet and calling SaveChanges() in my [TestInitialize] method. The problem however is that Effort/NMemory will change the Id values when they are saved, and so now mockProduct points to a missing ClientId.

What is your recommended approach in this scenario? Should I go back to trying to finish the "entity loader"? Or is there a way I can get NMemory to perform an "Identity insert" and leave the Id values alone?

Many thanks for your time & suggestions.

Coordinator
Jan 21, 2013 at 8:55 PM

Hello,

First of all I would like thank you for your contribution. I like your changes, but I asked a question about your request that I want to be clarified before I accept them.

Data loaders work on the lower layers of Effort by using the storage model of your schema, so perhaps creating a data loader that works with conceptual entity types is not the best idea. However, you could propose an API addition for NMemory and Effort that could disable/enable the identity field and take advantage of this change, respectively. Currently I do not have any good idea how should it be done in a clean way.

Anyway I strongly recommend to use data loaders instead of manual addition through EF, because it has really great performance benefit.

Jan 21, 2013 at 9:03 PM

Whoops, for some reason I didn't get the notice about your question. I'll follow up there but the quick answer is no, I haven't done any performance testing on it.

I understand your point about conceptual vs. storage model, it just so happens that all my mock data currently exists in the conceptual layer and frankly it seems easier to work with that way. We don't have a ton of data that we test with so raw performance isn't as much of an issue.

Coordinator
Feb 17, 2013 at 8:15 PM
Edited Feb 17, 2013 at 8:17 PM
Hey, I made your feature request work, here is the solution:
EffortConnection connection =
    (EffortConnection)DbConnectionFactory.CreateTransient();

StaffDbContext context = new StaffDbContext(connection);
context.Database.Initialize(true);

{
    // Create a separate context for initializing the data (schema without 
    // identity field)
    StaffDbContextNoIdentity dataInitContext = new StaffDbContextNoIdentity(connection);

    // DbConfiguration require open connection
    connection.Open();
    // Disable identity fields
    connection.DbConfiguration.SetIdentityFields(false);
    // Clear migration history to avoid exception
    connection.DbConfiguration.ClearMigrationHistory();
    // EF cannot handle open connection (fixed in EF6)
    connection.Close();

    // Add data with explicitly set id
    Person initPerson = new Person { Id = 5, FirstName = "John", LastName = "Doe" };
    dataInitContext.People.Add(initPerson);
    dataInitContext.SaveChanges();

    Assert.AreEqual(5, initPerson.Id);

    // Enable identity field
    connection.Open();
    connection.DbConfiguration.SetIdentityFields(true);
    connection.Close();
}

Person person = new Person { Id = 0, FirstName = "Mike", LastName = "Smith" };
context.People.Add(person);
context.SaveChanges();

Assert.AreEqual(6, person.Id);
The problem comes from the fact that Entity Framework will not push the value of the Id field to the database if it is set to be am identity field. This follows that a new DbContext definition is required that is identical to normal expect id generation is disabled. In the previous code this DbContext is used to fill the initial data. The migration history table has to be cleared, because using both DbContext on the same database instance would result in an exception.

Do you like this solution despite it is a bit clunky?
Feb 17, 2013 at 11:29 PM
Hi there.. well that does look rather promising at first glance, assuming I could perform each unit test within a transaction, so that the next test starts with a clean slate?

When you say "Clear migration history", does that mean you cannot have DataLoaders and Identity insert in the same operation?

Unfortunately I had a couple other issues come up at work and have not been able to spend much time on this lately. Hopefully I'll be able to get back to it soon. I really do appreciate your taking the time to make this work! I need to also look into EF6 as well...
Coordinator
Feb 18, 2013 at 1:16 PM
The CreateTransient method always returns with a connection object that points to a completely new and clean in-memory database instance. This makes possible to isolate the tests completely from each other, you do not need to clean up the database manually, the tests can even run in parallel!

Clearing migration history ("delete from __migrationhistory") is required because the schemes of the two DbContext definitions are not completely identical. Calling dataInitContext.SaveChanges would throw an exception because of this. StaffDbContextNoIdentity enables to set the ID of an entity, StaffDbContext doesn't.

A data loader could have been used without any trouble when the CreateTransient method was called.
Feb 18, 2013 at 3:50 PM
Thanks, I realize that about the transient connection (and had indeed experimented with using data loaders) but the problem in my case was that the tests seemed to be running a good deal more slowly than my existing strategy of using HashSets to simulate the database, even when I wasn't pre-loading any data. So I thought that using a single connection for all tests might help speed things up if I could just roll back the changes for each test.

Again I haven't done much digging into this to figure out where the slowdown was and unfortunately I've been pulled off of pursuing this further right now. I do think your solution here looks like a workable one in my scenario, as long as I can get the test initialize time in line with our current tests.

Thanks,
Toby
Coordinator
Feb 19, 2013 at 3:46 PM
Edited Feb 19, 2013 at 3:47 PM
I had a discussion before about performance issues with another partner. The conclusion was that the database initialization goes quite fast. You can measure this by registering a trace listener.
<system.diagnostics>
 <trace autoflush="true" />
 <sources>
   <source 
      name="Effort.Diagnostics.Tracing" 
      switchType="System.Diagnostics.SourceSwitch" 
      switchName="SourceSwitch">
  
      <listeners>
        <add name="text"
           type="System.Diagnostics.TextWriterTraceListener"
           initializeData="C:\users\tom\desktop\effort.log" />
      </listeners>
   </source>
 </sources>
 <switches>
   <add name="SourceSwitch" value="Verbose" />
 </switches>
</system.diagnostics> 
Jun 2, 2013 at 11:58 AM
Hi Tamas

Is there a way to disable Identity Insert using the database first approach? I, like Irontoby, specify my ids in my unit tests so this would certainly help.

Thanks

A