Per row Nested subquery

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 07-May-2017 19:16:57   

I have a ValuationEntity which has AssetID, ValuationDate, Value fields. There is also an AssetEntity which has PolicyEntity derived from it. Asset.ID is related to Valuation.AssetID as a 1:many.

What I need to do is write a DynamicQuery to grab a couple of Fields from Policy and the ValuationDate and Value fields from the Valuation table for the latest Valuation for that Asset (if any).

This SQL gives the right sort of result:-


SELECT
    p.ID,
    p.PolicyNumber,
    v.ValuationDate,
    v.Value
FROM Policy p
LEFT JOIN Valuation v ON v.AssetID = p.ID
WHERE v.ID IS NULL OR v.ID = 
  (SELECT TOP 1 v2.ID
  FROM Valuation v2
  WHERE v2.AssetID=p.ID
  ORDER BY v2.ValuationDate DESC, v2.ID DESC)

but I don't know if it is even possible to translate it to QuerySpec or just me doing it wrong.

My QuerySpec attempt was

            var query = qf.Policy
                .From(QueryTarget.LeftJoin(ValuationEntity.Relations.AssetEntityUsingAssetID))
                .Where(PolicyFields.ID == requiredPolicyIDs)
                .AndWhere(ValuationFields.ID == DBNull.Value | ValuationFields.ID == qf.Valuation
                              .TargetAs("v2")
                              .Where(ValuationFields.AssetID.SetObjectAlias("v2") == PolicyFields.ID)
                              .OrderBy(ValuationFields.ValuationDate.SetObjectAlias("v2") | SortOperator.Descending, ValuationFields.ID.SetObjectAlias("v2") | SortOperator.Descending)
                              .Limit(1)
                              .Select(ValuationFields.ID.SetObjectAlias("v2").ToValue<int?>())
                              )
                .Select(() =>
                    new
                        {
                            ID = PolicyFields.ID.ToValue<int>(),
                            Number = PolicyFields.PolicyNumber.ToValue<string>(),
                            CurrentValuationDate = ValuationFields.ValuationDate,
                            CurrentValuation = ValuationFields.Value
                        }
                );

So how can I achieve what I mention in the second paragraph? (Using LLBLGen 4.2 Final and Sql Server)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 08-May-2017 10:22:49   

I think that the .Where inside your condition (the outer where) should be a CorrelatedOver method:

.AndWhere(PolicyFields.ID ==
   qf.Valuation
      .CorrelatedOver(PolicyEntity.Relations.ValuationEntityUsingValuationId)
      .OrderBy(ValuationFields.ValuationDate.SetObjectAlias("v2") | SortOperator.Descending, ValuationFields.ID.SetObjectAlias("v2") | SortOperator.Descending)
      .Limit(1)
      .Select(ValuationFields.ID.SetObjectAlias("v2").ToValue<int?>()

)

... What is the result with the code that you have so far?

David Elizondo | LLBLGen Support Team
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 08-May-2017 10:55:01   

Thanks Daelmo

I don't think trying this method will work at all.

I tried your suggestion - it said no projection elements.

I changed it to ".Select(() => ValuationFields.ID.SetObjectAlias("v2").ToValue<int>())" - it said cannot convert result to int.

I added ".ToSingleResult" but it just added an extra parameter to the outer query with a zero value resulting in no rows returned.

For now, I am using this:-

            var query = qf.Policy
                .Where(PolicyFields.ID == requiredPolicyIDs)
                .Select(() =>
                    new
                        {
                            ID = PolicyFields.ID.ToValue<int>(),
                            CurrentValuation = qf.Valuation
                                .CorrelatedOver(ValuationEntity.Relations.AssetEntityUsingAssetID)
                                .OrderBy(ValuationFields.ValuationDate | SortOperator.Descending)
                                .Select(() => new ValuationInfo
                                                  {
                                                      ValuationDate = ValuationFields.ValuationDate.ToValue<DateTime>(),
                                                      Valuation = ValuationFields.Value.ToValue<decimal>()
                                                  })
                                .ToSingleResult(),
                        }
                );

        class ValuationInfo
        {
            public decimal Valuation;
            public DateTime ValuationDate;
        }


It works and only needs two database calls but I would love to know if there is a way to get the two valuation fields directly onto the outer anonymous type.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 08-May-2017 10:56:12   

I found an old similar example (adapted with Northwind, Customers and Orders).

Query:

SELECT [Northwind].[dbo].[Customers].[CustomerID]  AS [CustomerId],
       [Northwind].[dbo].[Customers].[CompanyName] AS [ClientName],
       [Northwind].[dbo].[Orders].[OrderDate],
       [Northwind].[dbo].[Orders].[OrderID]     AS [OrderId]
FROM   ( [Northwind].[dbo].[Customers]
         LEFT JOIN [Northwind].[dbo].[Orders]
             ON [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID])
WHERE  ((([Northwind].[dbo].[Orders].[CustomerID] IS NULL
       OR [Northwind].[dbo].[Orders].[OrderID] IN
          (SELECT TOP(1) [o2].[OrderID] AS [OrderId]
           FROM   [Northwind].[dbo].[Orders] [o2]
           WHERE  (([Northwind].[dbo].[Customers].[CustomerID] = [o2].[CustomerID]))
           ORDER  BY [o2].[OrderDate] DESC,
                     [o2].[OrderID] DESC)))) 

QuerySpec code:

// build the query
var qf = new QueryFactory();
var q = qf.Customer
.From(QueryTarget.LeftJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId))
.Where(
    OrderFields.CustomerId.IsNull()
    .Or(qf.Order.As("o2")
            .Where(CustomerFields.CustomerId == OrderFields.CustomerId.Source("o2"))
            .OrderBy(OrderFields.OrderDate.Source("o2").Descending(), OrderFields.OrderId.Source("o2").Descending())
            .Select(OrderFields.OrderId.Source("o2"))
            .Limit(1)
            .Contains(OrderFields.OrderId)))
.Select(CustomerFields.CustomerId, CustomerFields.ClientName, OrderFields.OrderDate, OrderFields.OrderId);

// fetch            
using (var adapter = new DataAccessAdapter())
{
    var orders = adapter.FetchQuery(q);

    // test
    Assert.AreEqual(94, orders.Count);
}
David Elizondo | LLBLGen Support Team
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 08-May-2017 12:35:10   

I can't get that to work!

            var query = qf.Policy
                .From(QueryTarget.LeftJoin(PolicyEntity.Relations.ValuationEntityUsingAssetID))
                .Where(
                    ValuationFields.AssetID.IsNull()
                        .Or(qf.Valuation.As("v2")
                            .Where(PolicyFields.ID == ValuationFields.AssetID.Source("v2"))
                            .OrderBy(ValuationFields.ValuationDate.Source("v2").Descending(), ValuationFields.ID.Source("v2").Descending())
                            .Select(ValuationFields.ID.Source("v2"))
                            .Limit(1)
                            .Contains(ValuationFields.ID)))
                //.AndWhere(PolicyFields.ID == requiredPolicyIDs)
                .Select(() =>
                    new
                        {
                            ID = PolicyFields.ID.ToValue<int>(),
                            ValuationDate = ValuationFields.ValuationDate.ToValue<DateTime>(),
                            Valuation = ValuationFields.Value.ToValue<decimal>()
                        }
                );

And here is the SQL I see:-

--Retrieval Query:
DECLARE @p2 bigint; SET @p2='1'

SELECT
  [LPA_L2].[ID],
  [Valuation].[ValuationDate],
  [Valuation].[Value] 
FROM
  (( [Asset] [LPA_L1]  
INNER JOIN
  [Policy] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[ID]) 
LEFT JOIN
  [Valuation] ON [LPA_L2].[ID] = [Valuation].[AssetID]) 
WHERE
  ( ( ( [Valuation].[AssetID] IS NULL OR [Valuation].[ID] IN (
SELECT
  TOP(@p2) [v2].[ID] 
FROM
  ( [Asset]  
LEFT JOIN
  [Policy] ON [Asset].[ID] = [Policy].[ID]) 
WHERE
  ( ( [LPA_L2].[ID] = [v2].[AssetID])) 
ORDER BY
  [v2].[ValuationDate] DESC,
  [v2].[ID] DESC))))

The Sql generated does not define [v2] for some reason. (My runtime version is 4.2.15.0309)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 08-May-2017 18:15:09   

(My runtime version is 4.2.15.0309)

That's a bit old. Could you please try the latest release of v.4.2?

Sign in to the Customer section of LLBLGen.com, then go to "MyAccount", then "Downloads". Download and install the full package of v.4.2.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-May-2017 14:57:33   

The v2 join is missing in the nested query, I think because of a missing correlation relation, but I can't create a simple repro here with the info provided. The 'In' SQL sub query contains a join on policy but no join with valuation, which is what causes the problem.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 09-May-2017 19:01:24   

I'm a bit reluctant to get new DLLs just now and I have a working solution so it isn't urgent for me.

Would me sending you my LLBLGen project help? Or would you prefer to leave it for now if its only me seeing a problem?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-May-2017 20:24:44   

Please check Otis' suggestion first.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-May-2017 20:52:50   

simmotech wrote:

I'm a bit reluctant to get new DLLs just now and I have a working solution so it isn't urgent for me.

Would me sending you my LLBLGen project help? Or would you prefer to leave it for now if its only me seeing a problem?

I think if you add a correlation from inner to outer query it would be solved. Or try the 'In()' function on the field instead of the Contains() call. But I think it's the correlation relation that's missing that causes the issue.

If I can build a repro case I can look at what's wrong better. A full project is perhaps a bit overkill I think simple_smile Trim it down to the minimum query, then briefly explain what's special about the entities (I see one is in an inheritance hierarchy?) and the relationships whether they're inherited or not, so we can build a repro case on e.g. adventureworks and see if we can reproduce it.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 09-May-2017 20:57:46   

My database is changing at the moment so I'll have a look tomorrow.

I think what you are suggesting is that I change this line:-

.Where(PolicyFields.ID == ValuationFields.AssetID.Source("v2"))

to be a CorrelatedOver call instead. Is that right?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-May-2017 21:06:45   

Yes. THough thinking about it, that shouldn't influence things that much. Please describe the entities a bit when you have the time so we can built a reprocase.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 09-May-2017 21:29:30   

I can't really describe the entities more simply than the first message. Policy.ID and Asset.ID are 1: 1 related through inheritance Valuation.ID is a PK and Valuation.AssetID is an FK related 0..1: Many to allow many Valuations for an Asset but a Valuation is not necessarily linked to an Asset - there are other FKs to other tables.

I have attached a diagram from LLBLGen in case it helps.

Attachments
Filename File size Added on Approval
Otis.png 66,935 09-May-2017 21:29.39 Approved
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 10-May-2017 08:32:12   

Just tried the CorrelatedOver suggestion - same result. Also tried the In() instead of Contains - same result.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 10-May-2017 09:05:53   

I tried with a similar model as yours (see attached image). You should use a CorrelatedOver construct.

var qf = new QueryFactory();
var query = qf.Policy
    .From(QueryTarget.LeftJoin(PolicyEntity.Relations.ValuationEntityUsingAssetId))
    .Where(
        ValuationFields.AssetId.IsNull()
            .Or(qf.Valuation.As("v2")
            .CorrelatedOver(PolicyFields.Id == ValuationFields.AssetId.Source("v2"))
                .OrderBy(ValuationFields.ValuationDate.Source("v2").Descending(), ValuationFields.Id.Source("v2").Descending())
                .Select(ValuationFields.Id.Source("v2"))
                .Contains(ValuationFields.Id)))
    .Select(() =>
        new
        {
            Id = PolicyFields.Id.ToValue<int>(),
            ValuationDate = ValuationFields.ValuationDate.ToValue<DateTime>(),
            ValuationId = ValuationFields.Id.As("ValuationId").ToValue<int>()
        }
    );

Generated SQL:

SELECT [LPA_L2].[Id],
       [trick].[dbo].[Valuation].[ValuationDate],
       [trick].[dbo].[Valuation].[Id] AS [ValuationId]
FROM (([trick].[dbo].[Asset] [LPA_L1]
       INNER JOIN [trick].[dbo].[Policy] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[Id])
      LEFT JOIN [trick].[dbo].[Valuation] ON [LPA_L2].[Id]=[trick].[dbo].[Valuation].[AssetId])
WHERE ((([trick].[dbo].[Valuation].[AssetId] IS NULL
         OR [trick].[dbo].[Valuation].[Id] IN
           (SELECT TOP(@p2) [v2].[Id]
            FROM [trick].[dbo].[Valuation] [v2]
            WHERE (([LPA_L2].[Id] = [v2].[AssetId]))
            ORDER BY [v2].[ValuationDate] DESC, [v2].[Id] DESC))))

Isn't that what you expect in generated sql? Using LLBLGen RTL 4.2.16.929.

Attachments
Filename File size Added on Approval
trick.zip 88,900 10-May-2017 09:06.47 Approved
David Elizondo | LLBLGen Support Team
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 10-May-2017 09:18:31   

Just cut and pasted your code into my app and changed "Id" to "ID" to make it compile.

The query failed with the same problem as before, so I guess the issue is down to my old DLLs.

Thanks for looking into that. I'll update to the latest DLLs in a future version of my app.

I do have one question: Since your original code used a .Where() and this uses CorrelatedOver(), is the rule that when inheritance is involved, CorrelatedOver() must be used otherwise .Where() is sufficient?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 10-May-2017 11:25:09   

CorrelatedOver always should be used when you want to tie an inner query with an outer query, so it can take into account alias scoping.

Frans Bouma | Lead developer LLBLGen Pro