Philadelphia, PA +267 235 3589 info@proficiencyconsulting.com

Article

Using SQL Server Sequence with Entity Framework (EF 5)

Rating: 1142 user(s) have rated this article Average rating:4.99
Posted by: orest, on 3/25/2013, in category ".NET Articles "
Views: this article has been read 7171 times

SQL Server sequence has been introduced with SQL server 2012. One drawback of using sequences is that unlike identity column you needed to make a request to the server first to get your key. Another issue is that it’s not supported by Entity Framework right out the box and you need to tweak it a little bit to make it work. This article describes one of the approaches that can be used to make everything work together. I came up with 2 or 3 different approaches but I like this one the most. Big thank you to Julie Lerman for helping me come up with this solution.


I’m using EF 5 code first where I have 2 classes

  1. SeqTest – parent class
  2. SeqChildren Child of SeqTest

Both of them will be using a Sequence to generate Id. I want to get all of my keys at once, doesn’t matter how many new objects I’m saving.
So let’s start coding.

  1. Set correct HasDatabaseGeneratedOption for keys

    When context is being created we need mark key columns on both object as follows

    modelBuilder.Entity<SeqTest>().Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);   
    modelBuilder.Entity<SeqChildren>().Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                                    
    This is simply telling EF that we will be setting keys manually.

  2. Common Base Class

    Our context will contain objects that can have their keys created using sequence and some using Identity columns. To separate those 2 types I created new base class (interface can work as well) public class SequenceBase{} Objects that inherit from this class will have some additional pre-processing to get their keys generated by Sequence.

  3. Override DBContxt. SaveChanges method to generate the Keys and populate our objects.

    When we populate Parent class Entity Framework will take care of pushing that value to all children.

    a. Since we want to get all our keys at once we need to figure out how many new objects we have and what type of the object that is. DbContext has ChangeTracker property that can help us here. Since we inherited our classes from SequenceBase we can get to all new SequenceBase Entities using simple selector

     
    foreach (var entry in this.ChangeTracker.Entries().Where(e => e.Entity is SequenceBase && e.State == EntityState.Added)) 
                                    
    We can find entity type by simply doing entry.Entity.GetType().Name

    b. Next we need To Go to DB Generate Keys

    c. After we get the keys from DB we need to assign them to primary key of the object. This is a little tricky and there are few ways of doing it. One way was to use Reflection based off Object Context but Julie recommended using MetadataWorkspace that worked quite nicely.

     
    var wKey = w.GetEntityContainer(objectContext.DefaultContainerName, DataSpace.CSpace)
    .BaseEntitySets.First(meta => meta.ElementType.Name == entityName)
    .ElementType.KeyMembers.Select(k => k.Name).FirstOrDefault();v
                                    

    d. After we have a Key Filed name life is good because we can simply set it to new key

     
    entry.Property(wKey).CurrentValue = newKey; 
                                    


How would you rate this article?

Questions? Comments?

Want to get started?