dotnet / dotnet/efcore

When using temporal tables - property named PeriodEnd or PeriodStart has to be DateTime

Open
#27,316 5 comments 1 reaction 0 assignees View on GitHub
area-model-building area-temporal-tables customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

Given a temporal table as
```sql
CREATE TABLE Thingie
(
ThingieId INT identity NOT NULL PRIMARY KEY CLUSTERED,
PeriodStart date,
PeriodEnd date,
Whatever date,
validFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
validTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (validFrom, validTo)
)
WITH (SYSTEM_VERSIONING = ON);
```
with context

```C#
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace PeriodEndIssue;

internal class Context : DbContext
{
public virtual DbSet Things { get; set; }

public Context(DbContextOptions options)
: base(options)
{
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(entity =>
{
entity.HasKey(e => e.ThingieId);
entity.ToTable("Thingie", b => b.IsTemporal(t =>
{
t.HasPeriodStart("ValidFrom");
t.HasPeriodEnd("ValidTo");
t.UseHistoryTable("MSSQL_TemporalHistoryFor_1127675065");
}));
});
}

protected override void ConfigureConventions(ModelConfigurationBuilder builder)
{
builder.Properties()
.HaveConversion()
.HaveColumnType("date");
}
}

public class DateOnlyConverter : ValueConverter
{
public DateOnlyConverter() : base(
d => d.ToDateTime(TimeOnly.MinValue),
d => DateOnly.FromDateTime(d))
{ }
}
```

When calling
```C#
b.IsTemporal(t =>
{
t.HasPeriodStart("ValidFrom");
t.HasPeriodEnd("ValidTo");
t.UseHistoryTable("MSSQL_TemporalHistoryFor_1127675065");
}));
```
I get an exception saying:
System.InvalidOperationException: 'The property 'PeriodStart' cannot be added to type 'Thingie' because the type of the corresponding CLR property or field 'DateOnly' does not match the specified type 'DateTime'.'

If i change the type of PeriodEnd, PeriodStart to DateTime - I do not get that exception for propterty "Whatever".

Thingie class:
```C#
namespace PeriodEndIssue;
public class Thingie
{
public int ThingieId { get; set; }
//public DateTime PeriodEnd { get; set; }
//public DateTime PeriodStart { get; set; }

public DateOnly PeriodEnd { get; set; }
public DateOnly PeriodStart { get; set; }

public DateOnly Whatever { get; set; }
}

```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.