adam-langley / efcore-temporal-query Goto Github PK
View Code? Open in Web Editor NEWLinq extensions to Entity Framework Core 3.1 to support Microsoft SQL Server Temporal Table Querying
License: MIT License
Linq extensions to Entity Framework Core 3.1 to support Microsoft SQL Server Temporal Table Querying
License: MIT License
I am having an issue that the JOINS do not apply AS OF.
EF Core Query:
var date = new DateTime(2020, 4, 29, 23, 0, 0);
var customerRecord = _bla.Blogs
.Include(x => x.Posts)
.AsOf(date)
.AsNoTracking()
.ToList();
Profiler captured this query:
exec sp_executesql
N'SELECT [b].[BlogId], [b].[Rating], [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
FROM [Blogs] FOR SYSTEM_TIME AS OF @__ef_temporal__p_0 AS [b]
LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]',N'@__ef_temporal__p_0 datetime2(7)',@__ef_temporal__p_0='2020-04-29 23:00:00'
The "AS OF" is missing after LEFT JOIN.
This means that the relations do not apply temporal data.
In the read.me it states:
"All joined relationships will have the FOR SYSTEM_TIME predicate applied to the generated SQL."
Any idea why this is happening?
I have the following issue:
this linq query
var result = _context.DistributionListEntries .AsOf(pager.TimeIndex) .Where(x => x.DistributionListId == distributionListId) .Include(x => x.PlaceholderValues) .Skip((pager.PageNumber - 1) * pager.PageSize).Take(pager.PageSize) .AsNoTracking();
when materialized
return await result.ToListAsync();
throws this exception
Message:
System.NotImplementedException : Unhandled method: AsOf
Stack Trace: QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) MethodCallExpression.Accept(ExpressionVisitor visitor) ExpressionVisitor.Visit(Expression node) QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) MethodCallExpression.Accept(ExpressionVisitor visitor) ExpressionVisitor.Visit(Expression node) QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) MethodCallExpression.Accept(ExpressionVisitor visitor) ExpressionVisitor.Visit(Expression node) QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) MethodCallExpression.Accept(ExpressionVisitor visitor) ExpressionVisitor.Visit(Expression node) QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) MethodCallExpression.Accept(ExpressionVisitor visitor) ExpressionVisitor.Visit(Expression node) QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) Database.CompileQuery[TResult](Expression query, Boolean async) QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) <>c__DisplayClass12_0'1.<ExecuteAsync>b__0() CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func'1 compiler) CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func'1 compiler) QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) EntityQueryable'1.GetAsyncEnumerator(CancellationToken cancellationToken) ConfiguredCancelableAsyncEnumerable'1.GetAsyncEnumerator() EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable'1 source, CancellationToken cancellationToken) DLPOCRepository.GetTemporalEntriesForDistributionList(Int64 distributionListId, TemporalPagingFilter pager) line 142 DLPOCRepositoryTest.TestDLRepositoryPaging() line 51 --- End of stack trace from previous location where exception was thrown ---
Entities
DistributionListEntry
and
PlaceholderValue
are marked as temporal
builder.Entity<DistributionListEntry>(dle => dle.HasTemporalTable()); builder.Entity<EntryPlaceholderValue>(epv => epv.HasTemporalTable());
and DbContext is initialized
`services.AddDbContext<DLPOCContext>(
opt => opt
.UseSqlServer(Configuration.GetConnectionString("DLPOC"))
.EnableTemporalTableQueries());`
Did I miss some configuration options?
Is there are sample project which implements 'efcore-temporal-query'?
When I send several queries with similar arguments yet different asOf timestamps using a single instance of DbContext, I always get similar results, apparently because of tracking. I solve this issue in my code by adding AsNoTracking():
public static IQueryable<TSource> AsOfConditional<TSource>(this IQueryable<TSource> source, DateTime? asOf)
where TSource : class
{
if (asOf.HasValue)
return source.AsOf(asOf.Value).AsNoTracking();
return source;
}
This may be not the responsibility of this library, but it might be helpful to extend AsOf() or at least give users some hints.
Hi Adam,
Firstly - awesome library! Thanks for creating it and making it available.
I need to be able to do an Include As Of a particular date.
My scenario - I have questions and answers. It's possible a question's text may change, but when I look at answers, I want to see the answer to the question as it was worded when answered.
public class Question
{
public Guid id {get; set;}
public string text {get; set;}
}
public class Response
{
public Guid id {get; set;}
public Guid questionId {get; set;}
public Question question {get; set;}
public string response {get; set;}
public DateTime submissionDateTimeUTC {get; set;}
}
I want to be able to do something like:
var responseWithQuestion=context.Responses
.IncludeAsOf (x=>x.question, x=>x.submissionDateTimeUTC);
I know I could do it in 2 queries (one to get the submissionDateTimeUTC, and then use the "AsOf") but thought I'd ask if this is something you would consider worthwhile?
Thanks.
Darryl
Hi,
For testing purposes, I've tried to update our project to .net5. This only required to change the target framework to net5.0 and updating the referenced nuget packages. After this, the project could be built.
When running it, I get an exception when adding the DbContext and enabling temporal table queries:
Exception details:
System.TypeLoadException
HResult=0x80131522
Message=Method 'Create' in type 'EntityFrameworkCore.TemporalTables.Query.AsOfQueryableMethodTranslatingExpressionVisitorFactory' from assembly 'Dabble.EntityFrameworkCore.Temporal.Query, Version=1.0.3.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
Source=Dabble.EntityFrameworkCore.Temporal.Query
StackTrace:
at Microsoft.EntityFrameworkCore.SqlServerAsOfEntityTypeBuilderExtensions.EnableTemporalTableQueries(DbContextOptionsBuilder optionsBuilder)
at Refdata.SAI.Data.Extensions.ServiceCollectionExtensions.<>c__DisplayClass0_0.<AddEntityFrameworkRepositories>b__0(DbContextOptionsBuilder builder) in D:\Dev\Refdata.SAI\Source\Refdata.SAI.Data\Extensions\ServiceCollectionExtensions.cs:line 38
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass1_0`2.<AddDbContext>b__0(IServiceProvider p, DbContextOptionsBuilder b)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.CreateDbContextOptions[TContext](IServiceProvider applicationServiceProvider, Action`2 optionsAction)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass17_0`1.<AddCoreServices>b__0(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__17`1.<AddCoreServices>b__17_1(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, Boolean isDefaultParameterRequired)
at Microsoft.AspNetCore.Mvc.Controllers.ControllerActivatorProvider.<>c__DisplayClass4_0.<CreateActivator>b__0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Controllers.ControllerFactoryProvider.<>c__DisplayClass5_0.<CreateControllerFactory>g__CreateController|0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
Do you plan on supporting .net 5?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.