Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> QuerySpec Temporal join not working
 

Pages: 1
LLBLGen Pro Runtime Framework
QuerySpec Temporal join not working
Page:1/1 

  Print all messages in this thread  
Poster Message
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
6 posts
# 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.
Code:

[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))



Code:
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

Code:
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.

Code:
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?


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14531 posts
# Posted on: 03-Jul-2019 05:21:47.  
Reproduced:
Code:
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:
Code:
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])


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 03-Jul-2019 08:46:49.  
We'll look into it!
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
6 posts
# 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?

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
6 posts
# Posted on: 05-Jul-2019 12:17:35.  
My query generates two select statements which are below.

Code:
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


Code:
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 '.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# 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:

Code:
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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
DominicF
User



Location:

Joined on:
02-Jul-2019 13:07:55
Posted:
6 posts
# 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.


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.