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)

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?

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.

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);
}

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)

(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.

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.

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?

Please check Otis' suggestion first.

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

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?

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.

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.

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

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.

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?

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.