asp.net - registros - importar txt a sql server 2012

¿Cómo puedo forzar el marco de entidad para insertar columnas de identidad?

Question

Quiero escribir un código C # para inicializar mi base de datos con algunos datos iniciales. Claramente, esto requerirá la capacidad de poder establecer los valores de varias columnas Identity cuando se inserte. Estoy usando un enfoque de primer código. De forma predeterminada, DbContext maneja la conexión de la base de datos y, por lo tanto, no puede SET IDENTITY_INSERT [dbo].[MyTable] ON . Entonces, lo que he hecho hasta ahora es utilizar el constructor DbContext que me permite especificar una conexión de base de datos que se utilizará. Luego, configuro IDENTITY_INSERT en ON en esa conexión de base de datos, y luego intento insertar mis registros utilizando el marco de entidad. Aquí hay un ejemplo de lo que tengo hasta ahora:

public class MyUserSeeder : IEntitySeeder {
    public void InitializeEntities(AssessmentSystemContext context, SqlConnection connection) {
        context.MyUsers.Add(new MyUser { MyUserId = 106, ConceptPersonId = 520476, Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginId = "520476", Password="28c923d21b68fdf129b46de949b9f7e0d03f6ced8e9404066f4f3a75e115147489c9f68195c2128e320ca9018cd711df", IsEnabled = true, SpecialRequirements = null });
        try {
            connection.Open();
            SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON", connection);
            int retVal = cmd.ExecuteNonQuery();
            context.SaveChanges();
        }
        finally {
            connection.Close();
        }
    }
}

Tan cerca y tan lejos, porque aunque cmd.ExecuteNonQuery() funciona bien, cuando ejecuto context.SaveChanges() , se me informa que "el valor explícito debe especificarse para la columna de identidad en la tabla ''MyUser'' cuando IDENTITY_INSERT está configurado en ON o cuando un usuario de replicación se está insertando en una columna de identidad NOT FOR REPLICATION ".

Presumiblemente, como MyUserId (que es la columna Identity en la tabla MyUser) es la clave principal, entity framework no intenta establecerlo cuando MyUser context.SaveChanges() , aunque le di a la entidad MyUser un valor para MyUserId propiedad.

¿Hay alguna manera de forzar el marco de la entidad para tratar de insertar incluso valores clave primarios para una entidad, entonces? ¿O tal vez una forma de marcar temporalmente MyUserId como un valor de clave principal, por lo que EF intenta insertarlo?

Answer 1

¿Hay alguna manera de forzar el marco de la entidad para tratar de insertar incluso valores de clave primaria para una entidad?

Sí, pero no tan limpiamente como me gustaría ver.

Suponiendo que está utilizando una clave de identidad generada automáticamente, EF ignorará por completo su intento de almacenar el valor de la clave. Esto parece ser "Por diseño" por las muchas buenas razones detalladas anteriormente, pero aún hay momentos en los que desea controlar completamente sus datos de inicialización (o una carga inicial). Sugiero que EF asista a este tipo de siembra en una versión futura. Pero hasta que lo hagan, solo escriba un pequeño código que funcione dentro del marco y automatice los detalles desordenados.

Eventho VendorID es ignorado por EF, puede usarlo con el bucle básico y el conteo para determinar cuántos registros de marcador de posición agregar entre sus registros en vivo. A los titulares de lugar se les asigna el siguiente número de identificación disponible cuando se agregan. Una vez que sus registros en vivo tengan las ID solicitadas, solo necesita eliminar la basura.

public class NewsprintInitializer: DropCreateDatabaseIfModelChanges<NewsprintContext>
{
    protected override void Seed(NewsprintContext context)
    {
        var vendorSeed = new List<Vendor>
        {
            new Vendor { VendorID = 1, Name = "#1 Papier Masson / James McClaren" },
            new Vendor { VendorID = 5, Name = "#5 Abitibi-Price" },
            new Vendor { VendorID = 6, Name = "#6 Kruger Inc." },
            new Vendor { VendorID = 8, Name = "#8 Tembec" }
        };

        //  Add desired records AND Junk records for gaps in the IDs, because .VendorID is ignored on .Add
        int idx = 1;
        foreach (Vendor currentVendor in vendorSeed)
        {
            while (idx < currentVendor.VendorID)
            {
                context.Vendors.Add(new Vendor { Name = "**Junk**" });
                context.SaveChanges();
                idx++;
            }
            context.Vendors.Add(currentVendor);
            context.SaveChanges();
            idx++;
        }
        //  Cleanup (Query/Find and Remove/delete) the Junk records
        foreach (Vendor del in context.Vendors.Where(v => v.Name == "**Junk**"))
        {
            context.Vendors.Remove(del);
        }
        context.SaveChanges();

        // setup for other classes

    }
}

Funcionó como se esperaba, excepto que tuve que hacer "Guardar Cambios" con frecuencia para mantener las ID en orden.

Answer 2

Aquí está la solución del problema. Lo probé en EF6 y funcionó para mí. Lo siguiente es un pseudo código que debería funcionar.

En primer lugar, debe crear la sobrecarga del dbcontext predeterminado. Si comprueba la clase base, encontrará la que pasa con dbConnection existente. Verifique el siguiente código

public MyDbContext(DbConnection existingConnection, bool contextOwnsConnection)
        : base(existingConnection, contextOwnsConnection = true)
    {
        //optional
        this.Configuration.ProxyCreationEnabled = true;
        this.Configuration.LazyLoadingEnabled = true;
        this.Database.CommandTimeout = 360;
    }

Y en Crear modelos, elimine la opción generada db como,

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyTable>()
            .Property(a => a.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        base.OnModelCreating(modelBuilder);
    }

Ahora en el código necesita pasar un objeto de conexión explícitamente,

using (var connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString))
        {
            connection.Open();
            using (var context = new MyDbContext(connection, true))
            {
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyTable] ON");
                context.MyTable.AddRange(objectList);
                context.SaveChanges();
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyTable] OFF");
            }

            connection.Close();
        }

Answer 3

Después de experimentar con varias opciones encontradas en este sitio, el siguiente código funcionó para mí ( EF 6 ). Tenga en cuenta que primero intenta una actualización normal si el elemento ya existe. Si no es así, intenta una inserción normal, si el error se debe a IDENTITY_INSERT, entonces prueba la solución. Observe también que db.SaveChanges fallará, de ahí la declaración db.Database.Connection.Open () y el paso de verificación opcional. Tenga en cuenta que esto no está actualizando el contexto, pero en mi caso no es necesario. ¡Espero que esto ayude!

public static bool UpdateLeadTime(int ltId, int ltDays)
{
    try
    {
        using (var db = new LeadTimeContext())
        {
            var result = db.LeadTimes.SingleOrDefault(l => l.LeadTimeId == ltId);

            if (result != null)
            {
                result.LeadTimeDays = ltDays;
                db.SaveChanges();
                logger.Info("Updated ltId: {0} with ltDays: {1}.", ltId, ltDays);
            }
            else
            {
                LeadTime leadtime = new LeadTime();
                leadtime.LeadTimeId = ltId;
                leadtime.LeadTimeDays = ltDays;

                try
                {
                    db.LeadTimes.Add(leadtime);
                    db.SaveChanges();
                    logger.Info("Inserted ltId: {0} with ltDays: {1}.", ltId, ltDays);
                }
                catch (Exception ex)
                {
                    logger.Warn("Error captured in UpdateLeadTime({0},{1}) was caught: {2}.", ltId, ltDays, ex.Message);
                    logger.Warn("Inner exception message: {0}", ex.InnerException.InnerException.Message);
                    if (ex.InnerException.InnerException.Message.Contains("IDENTITY_INSERT"))
                    {
                        logger.Warn("Attempting workaround...");
                        try
                        {
                            db.Database.Connection.Open();  // required to update database without db.SaveChanges()
                            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT[dbo].[LeadTime] ON");
                            db.Database.ExecuteSqlCommand(
                                String.Format("INSERT INTO[dbo].[LeadTime]([LeadTimeId],[LeadTimeDays]) VALUES({0},{1})", ltId, ltDays)
                                );
                            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT[dbo].[LeadTime] OFF");
                            logger.Info("Inserted ltId: {0} with ltDays: {1}.", ltId, ltDays);
                            // No need to save changes, the database has been updated.
                            //db.SaveChanges(); <-- causes error

                        }
                        catch (Exception ex1)
                        {
                            logger.Warn("Error captured in UpdateLeadTime({0},{1}) was caught: {2}.", ltId, ltDays, ex1.Message);
                            logger.Warn("Inner exception message: {0}", ex1.InnerException.InnerException.Message);
                        }
                        finally
                        {
                            db.Database.Connection.Close();
                            //Verification
                            if (ReadLeadTime(ltId) == ltDays)
                            {
                                logger.Info("Insertion verified. Workaround succeeded.");
                            }
                            else
                            {
                                logger.Info("Error!: Insert not verified. Workaround failed.");
                            }
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        logger.Warn("Error in UpdateLeadTime({0},{1}) was caught: {2}.", ltId.ToString(), ltDays.ToString(), ex.Message);
        logger.Warn("Inner exception message: {0}", ex.InnerException.InnerException.Message);
        Console.WriteLine(ex.Message);
        return false;
    }
    return true;
}

Answer 4

Después de una cuidadosa consideración, he decidido que la negativa de la estructura de la entidad a insertar columnas de identidad es una característica, no un error. :-) Si tuviera que insertar todas las entradas en mi base de datos, incluidos sus valores de identidad, ¡también tendría que crear una entidad para cada tabla de enlaces que el marco de entidades haya creado automáticamente para mí! Simplemente no es el enfoque correcto.

Entonces, lo que estoy haciendo es configurar las clases de siembra que solo usan el código C # y crear entidades EF, luego usar un DbContext para guardar los datos recién creados. Toma un poco más de tiempo tomar el SQL volcado y convertirlo en código C #, pero no hay (y no debería haber) demasiados datos solo para "sembrar" datos: debería ser una cantidad pequeña de datos que sea representativa. del tipo de datos que estarían en una base de datos en vivo que se puede poner rápidamente en una base de datos nueva para fines de depuración / desarrollo. Esto significa que si quiero vincular entidades, tengo que hacer consultas sobre lo que ya se ha insertado o mi código no conocería el valor de identidad generado, por ej. Este tipo de cosas aparecerán dentro del código de MyRoles , después de que haya configurado y hecho context.SaveChanges para MyRoles :

var roleBasic = context.MyRoles.Where(rl => rl.Name == "Basic").First();
var roleAdmin = context.MyRoles.Where(rl => rl.Name == "Admin").First();
var roleContentAuthor = context.MyRoles.Where(rl => rl.Name == "ContentAuthor").First();

MyUser thisUser = context.MyUsers.Add(new MyUser {
    Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginUsername = "naldred", Password="c1c966821b68fdf129c46de949b9f7e0d03f6cad8ea404066f4f3a75e11514748ac9f68695c2128e520ca0275cd711df", IsEnabled = true, SpecialRequirements = null
});
thisUser.Roles.Add(roleBasic);

Hacerlo de esta manera también hace que sea más probable que actualice mis datos de siembra cuando cambio el esquema, porque es probable que rompa el código de siembra cuando lo cambio (si elimino un campo o entidad, el código de siembra existente que usa ese campo) / entidad no podrá compilar). Con un script SQL para hacer siembra, ese no sería el caso, y tampoco el script SQL sería independiente de la base de datos.

Así que creo que si estás tratando de establecer los campos de identidad de las entidades para hacer los datos de siembra de DB, definitivamente has tomado el enfoque equivocado.

Si en realidad estuviera arrastrando una carga de datos de say SQL Server a PostgreSQL (una base de datos en vivo, no solo algunos datos de siembra), podría hacerlo a través de EF, pero me gustaría tener dos contextos abiertos al mismo tiempo, y escriba un código para tomar todas las diversas entidades del contexto de origen y ponerlas en el contexto de destino, luego guarde los cambios.

En general, la única vez que es apropiado insertar valores de identidad es cuando está copiando de un DB a otro DB dentro del mismo DBMS (SQL Server -> SQL Server, PostgreSQL -> PostgreSQL, etc.), y luego haría en una secuencia de comandos SQL y no en código de EF primero (la secuencia de comandos de SQL no sería DB-agnóstica, pero no tendría que ser así, no se va entre diferentes DBMS).

Answer 5

Método EF 6 , utilizando el artículo msdn :

    using (var dataContext = new DataModelContainer())
    using (var transaction = dataContext.Database.BeginTransaction())
    {
      var user = new User()
      {
        ID = id,
        Name = "John"
      };

      dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");

      dataContext.User.Add(user);
      dataContext.SaveChanges();

      dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");

      transaction.Commit();
    }

Actualización: para evitar el error "Se debe especificar un valor explícito para la columna de identidad en la tabla ''Nombre de tabla'' cuando IDENTITY_INSERT está establecido en ON o cuando un usuario de replicación se inserta en una columna de identidad NOT FOR REPLICATION", debe cambiar el valor de la propiedad StoreGeneratedPattern de Columna de identidad de Identity a None en el diseñador de modelos.

Nota, el cambio de StoreGeneratedPattern a Ninguno fallará al insertar el objeto sin el ID especificado (forma normal) con el error "No se puede insertar un valor explícito para la columna de identidad en la tabla ''TableName'' cuando IDENTITY_INSERT está desactivado".

Answer 6

No necesita hacer ningún negocio divertido con la conexión, puede cortar al intermediario y simplemente usar ObjectContext.ExecuteStoreCommand .

A continuación, puede lograr lo que desea al hacer esto:

context.ExecuteStoreCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON");

No obstante, no conozco ninguna forma incorporada de decirle a EF que establezca la inserción de identidad.

No es perfecto, pero sería más flexible y menos "hacky" que tu enfoque actual.

Actualizar:

Me acabo de dar cuenta de que hay una segunda parte de tu problema. Ahora que le ha dicho a SQL que desea insertar identidades, EF ni siquiera está tratando de insertar valores para dicha identidad (¿por qué?) No se lo hemos dicho.

No tengo ninguna experiencia con un primer enfoque de código, pero a partir de algunas búsquedas rápidas parece que necesita decirle a EF que su columna no debe ser generada desde la tienda. Tendrás que hacer algo como esto.

Property(obj => obj.MyUserId)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
    .HasColumnName("MyUserId");

Espero que esto te ayude a apuntar en la dirección correcta :-)

Answer 7

No pude encontrar una forma de insertar registros en una tabla. Básicamente, creé un script SQL con algo como esto ...

            sb.Append("SET IDENTITY_INSERT [dbo].[tblCustomer] ON;");

            foreach(...)
            {
                var insert = string.Format("INSERT INTO [dbo].[tblCustomer]
                     ([ID],[GivenName],[FamilyName],[NINumber],[CustomerIdent],
                      [InputterID],[CompanyId],[Discriminator]) 
                      VALUES({0}, ''{1}'', ''{2}'', ''{3}'', ''{4}'', 2, 2, ''tblCustomer''); ", 
                          customerId, firstName, surname, nINumber, Guid.NewGuid());

            sb.Append(insert);
                ...
            }

            sb.Append("SET IDENTITY_INSERT [dbo].[tblCustomer] OFF;");
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                var svrConnection = new ServerConnection(sqlConnection);
                var server = new Server(svrConnection);
                server.ConnectionContext.ExecuteNonQuery(sb.ToString());
        }

Estoy usando EF 6.

Answer 8

Solo soy un DBA, pero cada vez que aparece algo así, lo considero un olor a código. Es decir, ¿por qué tiene algo que dependa de ciertas filas que tienen ciertos valores de identidad? Es decir, en el ejemplo anterior, ¿por qué la Sra. Novelette necesita un valor de identidad de 106? En lugar de confiar en que ese sea siempre el caso, puede obtener su valor de identidad y usarlo donde sea que haya codificado 106. Un poco más engorroso, pero mucho más flexible (en mi opinión).

Answer 9

Un poco tarde para la fiesta, pero en caso de que alguien encuentre este problema en EF5 con DB primero: no pude conseguir que ninguna solución funcionara, pero encontré otra solución alternativa:

Antes de ejecutar el .SaveChanges() , restablecí el contador de identidad de la tabla:

Entities.Database.ExecuteSqlCommand(String.Format("DBCC CHECKIDENT ([TableNameHere], RESEED, {0})", newObject.Id-1););
Entities.YourTable.Add(newObject);
Entities.SaveChanges();

Esto significa que .SaveChanges() debe aplicarse después de cada adición, ¡pero al menos funciona!

Answer 10

¡Esta idea solo funciona de manera confiable si la tabla de destino está vacía, o si los registros se están insertando con identificadores más altos que todos los ID existentes en la tabla!

Tres años después y me tocó un problema similar al transferir los datos de producción a un sistema de prueba. Los usuarios querían poder copiar los datos de producción en el sistema de prueba cuando quisieran, así que en lugar de configurar un trabajo de transferencia en SQL Server, busqué la forma de realizar la transferencia en la aplicación utilizando las clases de EF existentes. De esta manera podría proporcionar a los usuarios un elemento de menú para iniciar la transferencia cuando lo deseen.

La aplicación utiliza una base de datos MS SQL Server 2008 y EF 6. Como las dos bases de datos generalmente tienen la misma estructura, pensé que podía transferir datos fácilmente de una instancia de DbContext a otra leyendo los registros de cada entidad usando AsNoTracking() y simplemente Add() (o AddRange() ) los registros a la propiedad apropiada en la instancia de DbContext de destino.

Aquí hay un DbContext con una entidad para ilustrar:

public class MyDataContext: DbContext
{
    public virtual DbSet<Person> People { get; set; }
}

Para copiar los datos de Personas, hice lo siguiente:

private void CopyPeople()
{
    var records = _sourceContext.People.AsNoTracking().ToArray();
    _targetContext.People.AddRange(records);
    _targetContext.SaveChanges();
}

Siempre que las tablas se hayan copiado en el orden correcto (para evitar problemas con restricciones de clave externa), esto funcionó muy bien. Desafortunadamente, las tablas que utilizan columnas de identidad dificultan las cosas, ya que EF ignoró los valores de id y simplemente dejó que SQL Server insertara el siguiente valor de identidad. Para las tablas con columnas de identidad terminé haciendo lo siguiente:

1. Lea todos los registros de una entidad dada
2. Ordena los registros por id en orden ascendente
3. establecer la semilla de identidad para la tabla con el valor de la primera identificación
4. siguiendo el siguiente valor de identidad, agregue los registros uno por uno. Si la identificación no es la misma que el valor de identidad siguiente esperado, establezca la semilla de identidad en el siguiente valor requerido

Mientras la tabla esté vacía (o todos los registros nuevos tengan IDs más altos que la ID actual más alta), y los ID estén en orden ascendente, EF y MS SQL insertarán los ID requeridos y ninguno de los sistemas se quejará.

Aquí hay un poco de código para ilustrar:

private void InsertRecords(Person[] people)
{
    // setup expected id - presumption: empty table therefore 1
    int expectedId = 1;

    // now add all people in order of ascending id
    foreach(var person in people.OrderBy(p => p.PersonId))
    {
        // if the current person doesn''t have the expected next id
        // we need to reseed the identity column of the table
        if (person.PersonId != expectedId)
        {
            // we need to save changes before changing the seed value
            _targetContext.SaveChanges();

            // change identity seed: set to one less than id
            //(SQL Server increments current value and inserts that)
            _targetContext.Database.ExecuteSqlCommand(
                String.Format("DBCC CHECKIDENT([Person], RESEED, {0}", person.PersonId - 1)
            );

            // update the expected id to the new value
            expectedId = person.PersonId;
        }

        // now add the person
        _targetContext.People.Add(person);

        // bump up the expectedId to the next value
        // Assumption: increment interval is 1
        expectedId++;
    }

    // now save any pending changes
    _targetContext.SaveChanges();
}

Usando la reflexión, pude escribir un método Load y Save que funcionó para todas las entidades en DbContext.

Es un poco complicado, pero me permite usar los métodos estándar de EF para leer y escribir entidades y supera el problema de cómo establecer columnas de identidad para valores particulares bajo un conjunto de circunstancias dadas.

Espero que esto sea de ayuda para otra persona que se enfrente a un problema similar.