Version LLBLGen 5.5.3
I have the following tables - definitions slighlty abridged - I wish to join in a temporal 'as of' query.
[dbo].[ProductPricing]
(
[Id] INT IDENTITY (1000, 1) NOT NULL,
[ProductId] INT NOT NULL,
[BusinessUnitId] INT NOT NULL,
[SupplierId] INT NOT NULL,
[ManufacturerId] INT NOT NULL,
[SiteId] INT NULL,
[PlotId] INT NULL,
[HouseTypeVersionId] INT NULL,
[SalesPrice] MONEY NOT NULL,
[MaterialCost] MONEY NOT NULL,
[SupplierProcurementType] NVARCHAR(5) NOT NULL,
[ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START
[ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END
[ModifiedBy] NVARCHAR(200) NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ,
CONSTRAINT [PK_ProductPricing] PRIMARY KEY ([Id]),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductPricingHistory, DATA_CONSISTENCY_CHECK=ON))
CREATE TABLE [dbo].[TradeRequirement]
(
[Id] INT IDENTITY (1000, 1) NOT NULL,
[ProductPricingId] INT NOT NULL,
[TradeCategoryId] INT NOT NULL,
[FitterId] INT NOT NULL,
[LabourPrice] MONEY NOT NULL,
[ProcurementType] NVARCHAR(5) NOT NULL,
[IsOneOff] BIT NOT NULL CONSTRAINT [DF_TradeRequirement_IsOneOff] DEFAULT 0,
[ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
[ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
[ModifiedBy] NVARCHAR(200) NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
CONSTRAINT [PK_TradeRequirement] PRIMARY KEY ([Id])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TradeRequirementHistory, DATA_CONSISTENCY_CHECK=ON))
My requirement is that I select a ProductPricing at a point in time and related TradeRequirements available at the same point in time
My QuerySpec is
public ProductPricing GetProductPricingAsOf(ProductPricing productPricing, DateTime asOf)
{
IRelationPredicateBucket filterBucket = GetRelationPredicateBucketForProductPricing(productPricing);
EntityQuery<ProductPricingEntity> query = qf.ProductPricing.ForSystemTime("AS OF {0}", asOf)
.From(QueryTarget
.LeftJoin(qf.TradeRequirement.ForSystemTime("AS OF {0}", asOf))
.On(ProductPricingFields.Id == TradeRequirementFields.ProductPricingId))
.Where(filterBucket.PredicateExpression)
.WithPath(ProductPricingEntity.PrefetchPathTradeRequirements)
.OrderBy(CreateProductHouseTypeVersionSitePlotSortExpression());
...
}
The issue is That while the ProductPricing is returned from the specified point in time, the TradeRequirements returned are current ones and not from the specified point in the past.
I captured the following in Sql Profiler. As you can see there is no temporal syntax in the generated SQL.
exec sp_executesql N'SELECT [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[FitterId], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[Id], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[IsOneOff], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[LabourPrice], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[ModifiedBy], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[ProcurementType], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[ProductPricingId], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[TradeCategoryId], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[ValidFrom], [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[ValidTo] FROM [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement] WHERE ( ( ( [OptionsOnlineCore_TeamProdCopy_20190610].[dbo].[TradeRequirement].[ProductPricingId] = @p1)))',N'@p1 int',@p1=159979
Is it possible using QuerySpec to have joins that returns temporal data? If so where am I going wrong?