CODEWITHSUNDEEP
c#.netsql-serverentity-framework
Nivedita
Nivedita

Reputation: 1

How can I set the Identity Insert on using DBContext as I'm trying to add values(supposed to be exactly same) from a different table

I am using Database first approach.

My destination table is first truncated using RemoveRange() and then data is transferred from source table using Destination.Tables.AddRange(Source.Tables);

All other column data is copied properly. But 'Id' field is populated from a larger no. I want the Id field to be same. That's why I'm trying to set identity insert on.

I tried to follow this tutorial but it doesn't work for me http://blog.robertobonini.com/2014/10/09/entity-framework-with-identity-insert/

table.cs 

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

Inside function

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
            {
                using (var Destination = new DestinationEntities(NewconnDestination))
                {
                    using (var Source = new SourceEntities(NewconnSource))
                    {

                        Source.Configuration.LazyLoadingEnabled = false;
                        Source.Configuration.ProxyCreationEnabled = false;
                        Destination.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT dbo.[Table] ON");
                        Destination.Tables.AddRange(Source.Tables);
                        SaveChanges(Destination);
                        Destination.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT dbo.[Table] OFF");
                        scope.Complete();
                    }
                } }

These are my column properties on the table Id properties

Is there something else required? Or any other method to achieve this?

This is the issue. I need Id column to be same in both tables. Source Table ID column Destination Table ID column

.Net version: 4.5 Entity framework: 6.1.3

Upvotes: 0

Views: 998

Answers (1)

Felix Lechenbauer
Felix Lechenbauer

Reputation: 310

In the tutorial you linked, the author states:

All the primary keys that you want to Force to be inserted should be changed to [DatabaseGenerated(DatabaseGeneratedOption.None)] This ensures that EF will not try and change the values and will simply insert them with the rest of the data.

EDIT:

I tried this setup and it worked: (Thanks to Gert Arnold.)

ItemID is the identity column of the table ItemTable.

[Table("ItemTable")]
public partial class ItemTable
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ItemID { get; set; }

    [StringLength(50)]
    public string ItemName { get; set; }
}

static void Main(string[] args)
    {
        List<ItemTable> ItemsList = new List<ItemTable>
        {
            new ItemTable { ItemID=1, ItemName="one" },
            new ItemTable { ItemID=2, ItemName="two" },
            new ItemTable { ItemID=3, ItemName="three" }
        };

        TestDbContext testDbContext = new TestDbContext();

        try
        {
            testDbContext.Database.Connection.Open();
            testDbContext.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [TestDB].[dbo].[ItemTable] ON");
            testDbContext.ItemTables.AddRange(ItemsList);
            testDbContext.SaveChanges();
        }
        finally
        {
            testDbContext.Database.Connection.Close();
        }
    }

Upvotes: 1

Related Questions