Columna computada basada en filas de otra tabla

Tengo la siguiente tabla (simplificada)

public class Door
{
    public int Id { get; set; }
    public bool Locked { get; set; }
}

Ahora el problema con esta mesa es que no sé cuánto tiempo la puerta ha estado cerrada o no. Podría añadir una nueva columna DateTime, pero entonces, si la puerta estaba cerrada, abierta y encerrada de nuevo, no sabría exactamente cuándo estaba cerrada la primera vez ya que eso sería sobrescrito. Así que añadí una nueva mesa

public class Locks
{
    public int Id { get; set; }
    public int DoorId { get; set; }
    public virtual Door Door { get; set; }
    public DateTime? LockedFrom { get; set; }
    public DateTime? LockedTo { get; set; }
}

y añadido a public Collection a mi mesa de la puerta. Todo esto es agradable y bueno y resolvería mi primer problema. Sin embargo, Puerta y Puerta. Locked ha sido utilizado ampliamente a través de nuestro código y cambiar todas esas consultas sería un trabajo muy grande. Así que mi esperanza era que pudiera reescribir Door.Locked para ser una columna computada. Debe recoger las entradas para esa puerta de Locks y comparar sus DateTimes con el momento actual para averiguar si la puerta está cerrada ahora mismo. Es importante que se trata de una columna computada (accionada en el lado servidor) y no una función o parámetro calculado en la entidad, ya que eso causaría todas las consultas que se filtran en la puerta. Cerrado para ser manejado en memoria (y tengo puertas de caoba en mi db). También preferiría si Door. Encerrado no puede ser nulo, para evitar cambiar su tipo a Bool?, y esto parece ser posible haciendo que la columna persista. He visto ejemplos de columnas computadas en . NET Core donde usan entityTypeBuilder.Property(x => x.Locked).HasComputedColumnSql("some sql formula"), pero ninguno que haga esto cuando la fórmula depende del contenido de otra tabla.

Conclusión: Quiero crear un código de columna calculado primero que calcula su valor utilizando el contenido de otra tabla.

¡Gracias!

Pregunta hecha hace 3 años, 4 meses, 29 días - Por scriptsculptor

2 Respuestas:

  • Para crear una columna computada en Entity Framework Core que depende del contenido de otra tabla, puedes usar la función HasComputedColumnSql pero con una sentencia SQL que incluya una subconsulta para obtener los valores necesarios de la otra tabla.

    En tu caso, podrías hacer algo así:

    csharp
entityTypeBuilder.Property(x => x.Locked)
    .HasComputedColumnSql(@"
        CASE
            WHEN EXISTS 
                (SELECT 1 FROM Locks l 
                 WHERE l.DoorId = Door.Id 
                   AND l.LockedFrom <= GETDATE() 
                   AND (l.LockedTo IS NULL OR l.LockedTo >= GETDATE())
                ) THEN 1
            ELSE 0
        END
    ");

    En este código, la columna Locked de la entidad Door se calcula dinámicamente en función de si existe alguna entrada en la tabla Locks para la misma puerta (Door.Id) en la que la puerta está actualmente cerrada (LockedFrom <= fechaactual) y no ha sido desbloqueada (LockedTo IS NULL o LockedTo >= fechaactual).

    Es importante tener en cuenta que para que esto funcione correctamente, la base de datos debe tener soporte para columnas calculadas, y la sentencia SQL utilizada en HasComputedColumnSql debe ser compatible con el proveedor de base de datos que estés utilizando.

    Espero que esta solución te ayude a lograr lo que necesitas en tu aplicación. ¡Déjame saber si tienes alguna otra pregunta!

    Respondida el Dec 18, 2020 a las 12:20 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Solución:

    Para crear una columna computada que se basa en filas de otra tabla, puedes utilizar el siguiente enfoque:

    • Crea una función SQL que calcule el valor de la columna computada. La función debe tomar el ID de la puerta como parámetro de entrada y devolver un valor booleano que indique si la puerta está cerrada.
    • Agrega la función SQL a tu base de datos.
    • Utiliza la anotación `[Computed]` en tu entidad de Puerta para especificar la función SQL que debe usarse para calcular el valor de la columna computada.

    Código de ejemplo:

    csharp
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Door
{
    public int Id { get; set; }

    [Computed("dbo.IsDoorLocked(@Id)")]
    public bool Locked { get; set; }
}

public static class Functions
{
    [DbFunction("dbo.IsDoorLocked")]
    public static bool IsDoorLocked(int doorId)
    {
        // Implement the logic to determine if the door is locked.
    }
}

    Explicación:

    La función IsDoorLocked se crea como una función SQL que toma el Id de la puerta como parámetro de entrada. La anotación [Computed] se agrega a la propiedad Locked de la entidad de la Puerta. El parámetro de la anotación especifica el nombre de la función SQL que debe usarse para calcular el valor de la columna computada.

    Beneficios:

    Este enfoque permite crear columnas computadas que dependen de los datos de otras tablas. Mantiene la propiedad Locked como una propiedad no nula, lo que evita cambios en su tipo. * La columna computada se calcula en el lado del servidor, lo que evita problemas de rendimiento en la memoria.

    Respondida el Dec 18, 2020 a las 12:26 - por Gemini

    Votos positivos: 0 | Votos negativos: 0