How to turn IDENTITY_INSERT ON

[Sirozha1337]

Hi,

I'm trying to use Effort for testing in my project. I use Database first approach, create connection this way:
_connection = EntityConnectionFactory.CreateTransient("name=MyEntities");

I have a table in my DB with an ID column IDENTITY (1, 1). I want to insert a record into this column with ID=10, but it changes this ID to 1. I've seen examples on the web that use _connection.DbManager to set identity insert to ON, but there's no DbManager for EntityConnection.

How do I turn on IDENTITY_INSERT for my connection?

[JonathanMagnan]

Hello @Sirozha1337 ,

I will assign one of my developers to look at your question.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @Sirozha1337 ,

Sorry for the delay,

Here is a working example:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Data.Entity;
using System.Linq;
using System.Windows.Forms;
using Effort.Provider;

namespace Effort.Lab.EF6
{
    public partial class Form_Request_Identity : Form
    {
        public Form_Request_Identity()
        {
            InitializeComponent();

            var connection = (EffortConnection) DbConnectionFactory.CreateTransient();

            // MUST initialize first with the context with identity (Required for SetIdentityFields(false)
            using (var context = new EntityContext(connection))
            {
                context.Database.CreateIfNotExists();
            }

            // MUST open connection first (Required for SetIdentityFields(false)
            connection.Open();
            connection.DbManager.SetIdentityFields(false);
            connection.Close();

            // SEED
            using (var context = new EntityContextNoIdentity(connection))
            {
                context.EntitySimples.Add(new EntitySimple {ID = 4, ColumnInt = 1});
                context.EntitySimples.Add(new EntitySimple {ID = 12, ColumnInt = 2});
                context.EntitySimples.Add(new EntitySimple {ID = 24, ColumnInt = 3});
                context.SaveChanges();
            }

            // MUST open connection first (Required for SetIdentityFields(false)
            connection.Open();
            connection.DbManager.SetIdentityFields(true);
            connection.Close();

            // TEST
            using (var context = new EntityContext(connection))
            {
                context.EntitySimples.Add(new EntitySimple() {ColumnInt = 4});
                context.SaveChanges();
                var list = context.EntitySimples.ToList();
            }
        }

        public class EntityContext : DbContext
        {
            public EntityContext(DbConnection connection) : base(connection, true)
            {
            }

            public DbSet<EntitySimple> EntitySimples { get; set; }
        }

        public class EntityContextNoIdentity : EntityContext
        {
            public EntityContextNoIdentity(DbConnection connection) : base(connection)
            {
            }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<EntitySimple>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                base.OnModelCreating(modelBuilder);
            }
        }

        public class EntitySimple
        {
            [Key]
            public int ID { get; set; }

            public int ColumnInt { get; set; }
        }
    }
}

Let me know if that helped you to make it works.

Best Regards,

Jonathan

[Sirozha1337]

Hi,
I tried this solution but it throws an exception about Database First context being used as Code First. (I didn't make changes to OnModelCreating, because this is a context used in DB First app and this file gets recreated every time I make changes to mappings).

[AMGitsKriss]

So is SetIdentityFields only available in DbConnectionFactory? Not to hijack the issue from @Sirozha1337, but I've inherited some tests that use ObjectContextFactory instead (also Database First).

[JonathanMagnan]

Hello @AMGitsKriss ,

Do you think you could provide a project sample with this issue?

It will help my developer investigate the issue more efficiently.

We have seen several people using database first differently so we want to make sure we will provide a solution that support your project.

[AMGitsKriss]

@JonathanMagnan I've discovered that I don't need to perform identity inserts on the fly, so I got around it with a Data Loader.

[JonathanMagnan]

Great thank for letting us know ;)