QuerySpec Temporal join not working

Posts   
 
    
DominicF
User
Posts: 9
Joined: 02-Jul-2019
# Posted on: 02-Jul-2019 15:10:27   

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?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 03-Jul-2019 05:21:47   

Reproduced:

var qf = new QueryFactory();
var query = qf.Customer.ForSystemTime("AS OF {0}", DateTime.Now)  
    .From(QueryTarget.LeftJoin(qf.Order.ForSystemTime("AS OF {0}", DateTime.Now)).On(OrderFields.CustomerId == CustomerFields.CustomerId))
    .Select(CustomerFields.Country);

var list = adapter.FetchQuery(query);

Produces:

SELECT [NORTHWIND].[dbo].[Customers].[Country] FROM ([NORTHWIND].[dbo].[Customers] LEFT JOIN [NORTHWIND].[dbo].[Orders] FOR SYSTEM_TIME AS OF @p2 ON [NORTHWIND].[dbo].[Orders].[CustomerID] = [NORTHWIND].[dbo].[Customers].[CustomerID])
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Jul-2019 08:46:49   

We'll look into it!

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Jul-2019 11:25:31   

Fixed in hotfix builds 5.4.7 and 5.5.4 which are now available (also on nuget, but their filtering/indexing takes some time as they're running on a nintendo gameboy)

The issue was related to aliases and temporal directives. We hope we've found all situations now and have applied proper handling of these situations. If you run into a situation where you apply a temporal directive like in your queries and e.g. an alias and the temporal directive isn't applied, please let us know so we can look into it.

Frans Bouma | Lead developer LLBLGen Pro
DominicF
User
Posts: 9
Joined: 02-Jul-2019
# Posted on: 04-Jul-2019 17:52:44   

Thank you for your speedy response and fix.

Unfortunately I still have this issue. I upgraded all my Llblgen packages to 5.5.4-hotfix-20190703 and asked another developer to double check nothing was missed and he agreed with my findings. The same exact SQL is being generated for fetching TradeRequirements.

Is there any other information I can provide to help solve this?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Jul-2019 18:55:09   

The query you obtain from the profiler doesn't have a join in it, are you perhaps grabbing the wrong query? You also specify a prefetch path, with no hint. Perhaps the query you see in the sql profiler is from that?

As you specify a prefetch path, you have to see 2 queries, one with a join and after that one without a join.

Edit: Thinking about it, it looks like we have a gap in the API there: there's on way to specify a temporal predicate on the prefetch path node if that entity is mapped on a temporal table. (The prefetch path api doesn't support that at the moment).

As v5.6 is still in beta and not yet released, we'll see if we can add it to v5.6 before it's released.

That said, the join you specify does have the temporal predicates and that issue should have been fixed now (we can't reproduce it here anymore).

Frans Bouma | Lead developer LLBLGen Pro
DominicF
User
Posts: 9
Joined: 02-Jul-2019
# Posted on: 05-Jul-2019 12:17:35   

My query generates two select statements which are below.

exec sp_executesql N'

SELECT DISTINCT 

[dbo].[ProductPricing].[Id], 
[dbo].[ProductPricing].[BusinessUnitId], 
[dbo].[ProductPricing].[HouseTypeVersionId], 
[dbo].[ProductPricing].[ManufacturerId], 
[dbo].[ProductPricing].[MaterialCost], 
[dbo].[ProductPricing].[ModifiedBy], 
[dbo].[ProductPricing].[PlotId], 
[dbo].[ProductPricing].[ProductId], 
[dbo].[ProductPricing].[SalesPrice], 
[dbo].[ProductPricing].[SiteId], 
[dbo].[ProductPricing].[SupplierId], 
[dbo].[ProductPricing].[SupplierProcurementType], 
[dbo].[ProductPricing].[ValidFrom], 
[dbo].[ProductPricing].[ValidTo] 

FROM ([dbo].[ProductPricing] 
FOR SYSTEM_TIME AS OF @p4 
LEFT JOIN [dbo].[TradeRequirement] 
FOR SYSTEM_TIME AS OF @p2 
ON [dbo].[ProductPricing].[Id] = [dbo].[TradeRequirement].[ProductPricingId]) 

WHERE ( (
( [dbo].[ProductPricing].[BusinessUnitId] = @p5 AND [dbo].[ProductPricing].[ProductId] = @p6 AND 
( [dbo].[ProductPricing].[HouseTypeVersionId] = @p7 OR [dbo].[ProductPricing].[HouseTypeVersionId] IS NULL) AND 
( [dbo].[ProductPricing].[SiteId] = @p8 OR [dbo].[ProductPricing].[SiteId] IS NULL) AND 
( [dbo].[ProductPricing].[PlotId] = @p9 OR [dbo].[ProductPricing].[PlotId] IS NULL)))) 

ORDER BY [dbo].[ProductPricing].[BusinessUnitId] ASC, 
[dbo].[ProductPricing].[ProductId] ASC, 
[dbo].[ProductPricing].[HouseTypeVersionId] ASC, 
[dbo].[ProductPricing].[SiteId] ASC, 
[dbo].[ProductPricing].[PlotId] ASC',N'

@p2 datetime,
@p4 datetime,
@p5 int,
@p6 int,
@p7 int,
@p8 int,
@p9 int',

@p2='2019-04-30 13:50:19.203',
@p4='2019-04-30 13:50:19.203',
@p5=1010,
@p6=2740,
@p7=4893,
@p8=1536,
@p9=83639
exec sp_executesql N'
SELECT dbo].[TradeRequirement].[FitterId], 
dbo].[TradeRequirement].[Id], 
dbo].[TradeRequirement].[IsOneOff], 
dbo].[TradeRequirement].[LabourPrice], 
dbo].[TradeRequirement].[ModifiedBy], 
dbo].[TradeRequirement].[ProcurementType], 
dbo].[TradeRequirement].[ProductPricingId], 
dbo].[TradeRequirement].[TradeCategoryId], 
dbo].[TradeRequirement].[ValidFrom], 
dbo].[TradeRequirement].[ValidTo] 
FROM dbo].[TradeRequirement] 
WHERE ( ( ( dbo].[TradeRequirement].[ProductPricingId] = @p1)))',N'@p1 int',@p1=159979

I expected the latter query to also have the 'As Of '.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jul-2019 10:37:07   

The latter is the prefetch path query, it's created from the specified prefetch path, not from elements in the query it's part of.

At the moment in v5.5 we don't have support for temporal predicates on prefetch paths. This is a limitation which exists since the start. After your post we realized we never planned a workitem to implement this so it kind of fell off the radar. We'll address this limitation in v5.6 which is currently in beta so we can ship it before RTM of v5.6.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Jul-2019 15:57:58   

We pushed a new beta for v5.6, with among some fixes, the feature to have a hint and/or temporal predicate specification on a prefetch path node. This should address your problem, e.g. one can now do:

var qf = new QueryFactory();
var q = qf.FiledOrderLine
          .WithPath(FiledOrderLineEntity.PrefetchPathFiledOrder
                                        .ForSystemTime("AS OF {0}", DateTime.Now)
                                        .WithSubPath(FiledOrderEntity.PrefetchPathEmployee
                                                                 .ForSystemTime("AS OF {0}", DateTime.Now)));

This addresses your problem. Not in the current version however as we had to add new stuff so we added it to v5.6 as it was still in beta.

Frans Bouma | Lead developer LLBLGen Pro
DominicF
User
Posts: 9
Joined: 02-Jul-2019
# Posted on: 09-Jul-2019 16:16:05   

I can confirm that the v5.6 beta fixes this issue. If I find any other temporal query issues, I will let you know.

Many thanks for your help.