Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Per row Nested subquery
 

Pages: 1
LLBLGen Pro Runtime Framework
Per row Nested subquery
Page:1/1 

  Print all messages in this thread  
Poster Message
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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:-

Code:

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
Code:
            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)
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8065 posts
# Posted on: 08-May-2017 10:22:49.  
I think that the .Where inside your condition (the outer where) should be a CorrelatedOver method:
Code:
.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'ing (articles and code snippets) | linkedin | twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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:-
Code:
            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.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8065 posts
# Posted on: 08-May-2017 10:56:12.  
I found an old similar example (adapted with Northwind, Customers and Orders).

Query:
Code:
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:
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'ing (articles and code snippets) | linkedin | twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 08-May-2017 12:35:10.  
I can't get that to work!
Code:
            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:-
Code:
--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)
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14480 posts
# Posted on: 08-May-2017 18:15:09.  
Quote:
(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.


  Top
Otis
LLBLGen Pro Team



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



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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?


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14480 posts
# Posted on: 09-May-2017 20:24:44.  
Please check Otis' suggestion first.
  Top
Otis
LLBLGen Pro Team



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



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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:-
Code:
.Where(PolicyFields.ID == ValuationFields.AssetID.Source("v2"))

to be a CorrelatedOver call instead. Is that right?
  Top
Otis
LLBLGen Pro Team



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



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 09-May-2017 21:29:30. Goto attachments  
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.
  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 10-May-2017 08:32:12.  
Just tried the CorrelatedOver suggestion - same result.
Also tried the In() instead of Contains - same result.


  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8065 posts
# Posted on: 10-May-2017 09:05:53. Goto attachments  
I tried with a similar model as yours (see attached image). You should use a CorrelatedOver construct.

Code:
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:
Code:
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.
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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?



  Top
Otis
LLBLGen Pro Team



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


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

Version: 2.1.12172008 Final.