dotnet
GroupJoin in EF Core 9/10 returns single row for SQL Server temporal tables
Bug description
Use case: Get one entity by Id (PK), and all its history from the temporal table.
When I'm trying to collect the historical rows, EF Core returns only a single history row when using .TemporalAll() and GroupJoin together, however the generated SQL query looks fine. I've attached a runnable Program.cs that reproduces the problem.
Note, that the same problem can be observed when manual left join is used (see in code example), the generated SQL query is the same.
The problem happens with EF Core versions 9.0.11 and 10.0.0.
Your code
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
var options = new DbContextOptionsBuilder<MyContext>()
.UseSqlServer("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Ef10TemporalSample;Integrated Security=true;")
.EnableSensitiveDataLogging()
.LogTo(Console.WriteLine, LogLevel.Information)
.Options;
int blogId;
await using (var context = new MyContext(options))
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
Blog[] blogs = [
new Blog { Url = "https://1.blog.com" },
new Blog { Url = "https://2.blog.com" }
];
context.Blogs.AddRange(blogs);
await context.SaveChangesAsync();
var updateBlog = blogs[0];
updateBlog.Url = "https://foo.blog.com";
await context.SaveChangesAsync();
updateBlog.Url = "https://bar.blog.com";
await context.SaveChangesAsync();
// Note the updated blog id.
blogId = updateBlog.Id;
}
var expectedHistoryCount = 3; // 1 create + 2 update
await using (var context = new MyContext(options))
{
var actualCount = await context.Blogs.TemporalAll()
.Where(x => x.Id == blogId)
.CountAsync();
Console.WriteLine($"=== Temporal table direct query: Expected {expectedHistoryCount}, actual {actualCount}");
}
await using (var context = new MyContext(options))
{
var blogWithHistory = await context.Blogs
.Where(x => x.Id == blogId)
.GroupJoin(
context.Blogs.TemporalAll(),
blog => blog.Id,
history => history.Id,
(blog, histories) => new
{
blog.Id,
History = histories
.Select(x => new
{
From = EF.Property<DateTime>(x, "ValidFrom"),
To = EF.Property<DateTime>(x, "ValidTo"),
x.Url
})
.ToArray()
})
.FirstAsync();
Console.WriteLine($"=== GroupJoin: Expected {expectedHistoryCount}, actual {blogWithHistory.History.Length}");
}
await using (var context = new MyContext(options))
{
var blogWithHistory = await context.Blogs
.Where(x => x.Id == blogId)
.Select(blog => new
{
blog.Id,
History = context.Blogs.TemporalAll()
.Where(x => x.Id == blog.Id)
.Select(x => new
{
From = EF.Property<DateTime>(x, "ValidFrom"),
To = EF.Property<DateTime>(x, "ValidTo"),
x.Url
})
.ToArray()
})
.FirstAsync();
Console.WriteLine($"=== LEFT JOIN: Expected {expectedHistoryCount}, actual {blogWithHistory.History.Length}");
}
class MyContext : DbContext
{
public MyContext(DbContextOptions<MyContext> options)
: base(options)
{
}
public DbSet<Blog> Blogs { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>(e =>
{
e.ToTable(
"Blogs",
table => table.IsTemporal(x =>
{
x.UseHistoryTable("BlogHistory");
x.HasPeriodStart("ValidFrom");
x.HasPeriodEnd("ValidTo");
}));
e.HasKey(x => x.Id);
});
}
}
class Blog
{
public int Id { get; set; }
public required string Url { get; set; }
}
Stack traces
Verbose output
info: 2025. 12. 03. 11:17:12.556 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (6ms) [Parameters=[@blogId='1'], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*)
FROM [Blogs] FOR SYSTEM_TIME ALL AS [b]
WHERE [b].[Id] = @blogId
=== Temporal table direct query: Expected 3, actual 3
info: 2025. 12. 03. 11:17:12.680 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (4ms) [Parameters=[@blogId='1'], CommandType='Text', CommandTimeout='30']
SELECT [b1].[Id], [b0].[ValidFrom], [b0].[ValidTo], [b0].[Url], [b0].[Id]
FROM (
SELECT TOP(1) [b].[Id]
FROM [Blogs] AS [b]
WHERE [b].[Id] = @blogId
) AS [b1]
LEFT JOIN [Blogs] FOR SYSTEM_TIME ALL AS [b0] ON [b1].[Id] = [b0].[Id]
ORDER BY [b1].[Id]
=== GroupJoin: Expected 3, actual 1
info: 2025. 12. 03. 11:17:12.702 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (1ms) [Parameters=[@blogId='1'], CommandType='Text', CommandTimeout='30']
SELECT [b1].[Id], [b0].[ValidFrom], [b0].[ValidTo], [b0].[Url], [b0].[Id]
FROM (
SELECT TOP(1) [b].[Id]
FROM [Blogs] AS [b]
WHERE [b].[Id] = @blogId
) AS [b1]
LEFT JOIN [Blogs] FOR SYSTEM_TIME ALL AS [b0] ON [b1].[Id] = [b0].[Id]
ORDER BY [b1].[Id]
=== LEFT JOIN: Expected 3, actual 1
EF Core version
10.0.0
Database provider
Microsoft.EntityFrameworkCore.SqlServer
Target framework
.NET 10
Operating system
Windows 11
IDE
No response
Hello EFCore community As far as I understand, this issue is related to the same ID/key values in the temporal table. So EFCore skips reading next rows because it assumes that it's the same record. I thought that it could be an easy fix to add additional TemporalPeriodStartColum and TemporalPeriodEndColumn into the identifier collection. Tried to add it to SqlServerQueryableMethodTranslatingExpressionVisitor.cs file, but there is some additional issue with that. I cannot fetch column names for period columns - GetPeriodStartPropertyName() and GetPeriodEndPropertyName(). Does anyone know what could be the reason for it?
if (entityType.IsTemporal())
{
var periodStartName = table[SqlServerAnnotationNames.TemporalPeriodStartColumnName];// ?? "PeriodStart";
var periodEndName = table[SqlServerAnnotationNames.TemporalPeriodEndColumnName];// ?? "PeriodEnd";
if (periodStartName != null)
{
var psProperty = entityType.FindProperty(periodStartName.ToString());
if (psProperty != null)
{
var psColumn = CreateColumnExpression(psProperty, table, alias, nullable: false);
identifier.Add((psColumn, psProperty.GetKeyValueComparer()));
}
}
if (periodEndName != null)
{
var peProperty = entityType.FindProperty(periodEndName.ToString());
if (peProperty != null)
{
var peColumn = CreateColumnExpression(peProperty, table, alias, nullable: false);
identifier.Add((peColumn, peProperty.GetKeyValueComparer()));
}
}
}
As far as I understand, ID (primary key) column could be duplicated in temporal table. That's why EFCore can't identify if the "history" record is same or not.
There is some idea on how to fix this issue. Please let me know how it should be adjusted
- RuntimeEntityType should contain annotations for TemporalPeriodStartPropertyName and TemporalPeriodEndPropertyName. This data is important for next steps
- SqlServerQueryableMethodTranslatingExpressionVisitor.cs could check if it's temporal table and add Start and End date properties to identifier collection. So code will check if temporal record is same based on ID, StartPeriod and EndPeriod.
- Not sure if I can adjust existing TemporalComplexNavigationsCollectionsQuerySqlServerTest.cs to support such cases.
p.s. do not hesitate to reply to this message that it does not make sense :-)