Las tablas temporales de SQL Server realizan un seguimiento automático de todos los datos almacenados en una tabla, incluso después de que dichos datos hayan sido actualizados o eliminados. Esto se logra mediante la creación de una "tabla de historial" paralela, en la que se almacenan los datos históricos con una marca de tiempo cada vez que se realiza un cambio en la tabla principal. Esto permite consultar los datos históricos, como para auditarlos, o restaurarlos, como para recuperarlos después de una mutación o eliminación accidental.
La versión 6.0 de Entity Framework Core admite:
- La creación de tablas temporales mediante migraciones de EF Core
- Transformación de tablas existentes en tablas temporales, nuevamente usando migraciones
- Consulta de los datos históricos de estas tablas
- Restauración de datos de cualquier momento del pasado
Aplicación de ejemplo
La aplicación de ejemplo que vamos a utilizar en este post para ver estas características la tienes disponible en GitHub. Utiliza un modelo EF Core simple para clientes, productos y pedidos:
public class Customer
{
public Guid Id { get; set; }
public string Name { get; set; }
public List Orders { get; set; }
}
public class Order
{
public Guid Id { get; set; }
public DateTime OrderDate { get; set; }
public Product Product { get; set; }
public Customer Customer { get; set; }
}
public class Product
{
public Guid Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
Cómo configurar una tabla temporal
Los tipos de entidad se asignan a tablas temporales en el evento OnModelCreating utilizando el método IsTemporal(). Por ejemplo:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity()
.ToTable("Customers", b => b.IsTemporal());
modelBuilder
.Entity()
.ToTable("Products", b => b.IsTemporal());
modelBuilder
.Entity()
.ToTable("Orders", b => b.IsTemporal());
}
Las migraciones de EF Core crearán estas tablas como temporales o, si las tablas ya existen, se convertirán en tablas temporales. Por ejemplo, las migraciones ejecutarán el siguiente SQL para convertir la tabla ya existente Products en una tabla temporal:
ALTER TABLE [Products] ADD [PeriodEnd] datetime2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999';
ALTER TABLE [Products] ADD [PeriodStart] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.0000000';
ALTER TABLE [Products] ADD PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
ALTER TABLE [Products] ALTER COLUMN [PeriodStart] ADD HIDDEN
ALTER TABLE [Products] ALTER COLUMN [PeriodEnd] ADD HIDDEN
DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'ALTER TABLE [Products] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[ProductHistory]))')
Observa cómo la tabla, ahora, contiene dos campos ocultos de tipo datetime2 llamadas PeriodStart y PeriodEnd. Estas "columnas de período" representan el intervalo de tiempo durante el cual existieron los datos en la fila. Estas columnas se asignan a "propiedades de sombra" en el modelo EF Core, lo que permite su uso en consultas como verás un poco más adelante.
Nota: las horas de estas columnas son siempre la hora UTC generada por SQL Server. Las horas UTC se utilizan para todas las operaciones que involucran tablas temporales, como en las consultas que se muestran a continuación.
El comando final del fragmento anterior establece SYSTEM_VERSIONING = ON, que habilita el almacenamiento temporal en esta tabla. Esto hace que SQL Server cree una tabla de historial asociada llamada ProductHistory. Si lo deseas, puedes configurar en el modelo de EF los nombres para los campos del período y de la tabla de historial.
Consulta de los datos históricos
La mayoría de las veces, las tablas temporales se utilizan como los de cualquier otra tabla. Es decir, las columnas de período y los datos históricos son manejados de forma transparente por SQL Server, de modo que la aplicación puede hacer caso omiso de ellos. Las entidades se agregan, se consultan, se actualizan y se eliminan de la forma habitual.
La aplicación ejemplo se inicializa con datos de productos y pedidos que han cambiado con el tiempo. Con estos datos de ejemplo, el código para buscar el precio actual de un producto es el mismo que usaríamos si no tuviésemos tablas temporales. Por ejemplo:
var product = context.Products.Single(product => product.Name == productName);
Ten en cuenta que el método extensor Single se puede utilizar en este caso porque solo hay un producto con ese nombre en la tabla. Sin embargo, entre bastidores, SQL Server ha realizado un seguimiento de cada cambio en el precio de este producto. El método de extensión de LINQ TemporalFromTo se puede utilizar para consultar estos datos históricos entre dos fechas. Por ejemplo, la aplicación de ejemplo tiene una consulta que recupera los precios que ha tenido un producto entre dos fechas determinadas:
var productSnapshots = context.Products
.TemporalBetween(from, to)
.OrderBy(product => EF.Property(product, "PeriodStart"))
.Where(product => product.Name == productName)
.Select(product =>
new
{
Product = product,
PeriodStart = EF.Property(product, "PeriodStart"),
PeriodEnd = EF.Property(product, "PeriodEnd")
})
.ToList();
La consulta proyecta los valores PeriodStart y PeriodEnd en un tipo anónimo, junto con una instantánea de la instancia de la entidad en dicho periodo. Se utiliza el método EF.Property para obtener los valores del período ya que, como hemos visto, se asignan a propiedades de sombra en la entidad.
Al ejecutar esta consulta en los datos de ejemplo para el producto "DeLorean" (¡Regreso al futuro!), se obtienen los siguientes resultados:
The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d is currently $150000.00.
Historical prices from 10/5/2021 12:09:11 PM to 10/7/2021 12:09:37 PM:
The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $2000000.00 from 10/5/2021 12:09:11 PM until 10/5/2021 12:09:16 PM.
The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $2500000.00 from 10/5/2021 12:09:16 PM until 10/5/2021 12:09:27 PM.
The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $75000.00 from 10/5/2021 12:09:27 PM until 10/5/2021 12:09:32 PM.
The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $150000.00 from 10/5/2021 12:09:32 PM until 12/31/9999 11:59:59 PM.
Ten en cuenta que la consulta devuelve varias instancias de Product con la misma clave primaria. Esto se debe a que la base de datos nos dice cómo era esta entidad única varias veces en el pasado.
La última instantánea devuelve como hora de fin del periodo el "31/12/9999 11:59:59 PM". Esto indica que este registro todavía existe en la base de datos y es el actualmente en vigor. O sea, este es el registro/fila que se devuelve al consultar la tabla de la forma habitual.
EF Core admite varios operadores de consulta de tablas temporales:
TemporalAsOf: devuelve registros que estaban activos (actuales) a la hora UTC indicada. Este es un único registro de la tabla de historial para una clave primaria determinada.TemporalAll: devuelve todos los registros con los datos históricos.TemporalFromTo: obtiene todos los registros que estaban activos entre dos horas UTC dadas.TemporalBetween: lo mismo que el anterior, excepto que se incluyen también los registros por la parte superior (o sea, incluye aquellos registros que cumplen con la fecha superior, mientras que el anterior no).TemporalContainedIn: devuelve todos los registros que comenzaron a estar activos y terminaron estando activos entre dos horas UTC dadas.
Nota: consulta la documentación de las tablas temporales de SQL Server para obtener más información sobre qué filas exactamente se incluyen para cada uno de estos operadores.
Encontrar un registro histórico específico
Para seguir con nuestro ejemplo, supongamos que un cliente pidió un DeLorean en algún momento del pasado. Sin embargo, ahora han decidido que el acero cepillado no es lo suficientemente moderno y deciden devolverlo. El servicio de atención al cliente se complace en atender la petición y necesitan una consulta para obtener el pedido:
var order = context.Orders
.Include(e => e.Product)
.Include(e => e.Customer)
.Single(order =>
order.Customer.Name == customerName
&& order.OrderDate > on.Date
&& order.OrderDate < on.Date.AddDays(1));
Esto da como resultado la siguiente información:
Arturo compró un Delorean por 150.000€ el 5/10/2021 12:16:07 PM
El servicio de atención al cliente le devuelve 150.000€ a Arturo, pero Arturo no está precisamente contento ya que, en realidad, ¡pagó 2,5 millones de euros por el Delorean en su momento!
El responsable del Servicio de atención al cliente se pone en contacto con desarrollo e insiste en que, en el futuro, los pedidos deben disponer de un seguimiento del precio al que se vendió el producto. Los desarrolladores están de acuerdo, pero mientras tanto pueden usar los datos históricos para recuperar el precio del DeLorean en el momento en que se vendió. Esto se logra agregando TemporalAsOf a la consulta utilizada anteriormente, así:
var order = context.Orders
.TemporalAsOf(on)
.Include(e => e.Product)
.Include(e => e.Customer)
.Single(order =>
order.Customer.Name == customerName
&& order.OrderDate > on.Date
&& order.OrderDate < on.Date.AddDays(1));
que genera la siguiente consulta SQL:
SELECT TOP(2) [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[PeriodEnd], [o].[PeriodStart], [o].[ProductId], [p].[Id], [p].[Name], [p].[PeriodEnd], [p].[PeriodStart], [p].[Price], [c].[Id], [c].[Name], [c].[PeriodEnd], [c].[PeriodStart]
FROM [Orders] FOR SYSTEM_TIME AS OF '2021-10-05T12:18:54.3934318Z' AS [o]
LEFT JOIN [Customers] FOR SYSTEM_TIME AS OF '2021-10-05T12:18:54.3934318Z' AS [c] ON [o].[CustomerId] = [c].[Id]
LEFT JOIN [Products] FOR SYSTEM_TIME AS OF '2021-10-05T12:18:54.3934318Z' AS [p] ON [o].[ProductId] = [p].[Id]
WHERE (([c].[Name] = @__customerName_0) AND ([o].[OrderDate] > @__on_Date_1)) AND ([o].[OrderDate] < @__AddDays_2)
Fíjate en el uso que se hace de FOR SYSTEM_TIME AS OF. Esto le dice a SQL Server que busque en la tabla de historial y devuelva el registro tal y como estaba en ese momento. Esto da como resultado la siguiente información:
Arturo compró un DeLorean for 2.500.000.00€ el 5/10/2021 12:16:07 PM
Arturo obtiene sus 2.5 millones de euros, y luego se da de baja de cliente rápidamente por la experiencia.
Restaurar datos eliminados
Algún tiempo después, Arturo decide que realmente necesita un condensador de fluzo. Por desgracia, solo está disponible en la misma empresa que vende los DeLoreans. Así que solicita que se recupere su cuenta, incluidos todos los pedidos antiguos (Arturo es un tanto caprichoso). Esto se puede hacer en dos etapas. Primero, se puede encontrar la marca de tiempo de cuando el cliente se eliminó de la base de datos:
var customerDeletedOn = context.Customers
.TemporalAll()
.Where(customer => customer.Name == customerName)
.OrderBy(customer => EF.Property(customer, "PeriodEnd"))
.Select(customer => EF.Property(customer, "PeriodEnd"))
.Last();
Y esta marca de tiempo se usa luego para crear una consulta que devuelva al cliente y todos sus pedidos que existían en un momento específico, justo antes de que se eliminara:
var customerAndOrders = context.Customers
.TemporalAsOf(customerDeletedOn.AddMilliseconds(-1))
.Include(e => e.Orders)
.Single();
Al pedir los registros en un momento específico garantizamos que los pedidos que ya se eliminaron antes de que se eliminara la cuenta del cliente no se restauren ahora por error.
Una vez que se han recuperado los registros históricos, se pueden volver a insertar en la base de datos añadiéndoles al contexto y llamando al método SaveChanges:
context.Add(customerAndOrders);
context.SaveChanges();
Arturo ahora tiene su cuenta restaurada y puede pedir un nuevo condensador de flujo que, suponemos, le permitirá regresar al futuro y dejarnos un poco tranquilos 😉
En resumen
Las tablas temporales de SQL Server realizan un seguimiento automático del estado histórico de las tablas. EF Core 6.0 admite la creación y modificación de tablas temporales asignadas a tipos de entidad. Luego, estos datos se pueden consultar utilizando nuevos operadores LINQ, y los datos eliminados se pueden restaurar. Esto permite una fácil auditoría de los datos, así como análisis forense de los datos eliminados y la recuperación de errores de corrupción de datos.
Nota: este artículo es una traducción y adaptación propia al español del artículo original de Microsoft escrito por Jeremy Likness. Énfasis/negritas puesto por nosotros.
Fecha de publicación: