Hej Hej,

I am a newbie at LLBLGen. I have the following Table - FuelTankId - Date - Volume

Sample data: FuelTankID Date volume 1 13/09/2010 9:00 20L 1 13/09/2010 8:00 15L 1 12/09/2010 8:45 22L 2 13/09/2010 8:40 8L 2 13/09/2010 8:20 6L 2 12/09/2010 9:05 3L

Now I have to fetch for each tank, the last volume of a day so the Entitycollection should contain: FuelTankID Date volume 1 13/09/2010 9:00 20L 2 12/09/2010 9:05 3L

But to be frankly I don't have a clue how to create a predicate / relation on this.

can anyone help me with creating this for LLBLGen?

Greetz,

Jonathan

Hi Jonathan,

If you are using LLBLGen API classes, please read this.

If you plan to use LINQ2LLBL, please read this section of the manual.

Hej,

I am using the LLBLGen api classes. This is the sql query that I should create in LLBLGEN

select *
   from core.FuelTankGauges FTG
      , (select Max(Date) as MaxDate
           from core.FuelTankGauges
          group by FuelTankID, DATEADD(D, 0, DATEDIFF(D, 0, Date))) DATES
  where FTG.Date = DATES.MaxDate

as you can see it's complicated to put it in LLBlGen. Does somebody have an idea to convert this to LLBLGEN?

Greetz,

Jonathan

You will need to use Derived tables and dynamic relations

Could you please elaborate using Jonathan's example?

It is an excellent question which applies to similar cases where the collection of 'current' values need to be fetched. It could therefore very well fortify the documentation as well as help us a bit figure out how to implement this.

Please check the link I've posted. It should have a code sample.

I did check the link as well as the code examples you provided for the Northwind database but could still use some help.

Basically, I am trying to get the current or last set of records by date.

Here is a query that gets the last customers orders for a particular employee using the Northwind Orders table:

        'SELECT  CustomerID, MAX(OrderDate) AS LastOrderDate, EmployeeID
        'FROM        dbo.Orders
        'GROUP BY CustomerID, EmployeeID
        'HAVING   (EmployeeID = 1) AND (MAX(OrderDate) < CONVERT(DATETIME, '1997-01-01 00:00:00', 102))

This returns 6 records. Here is my attempt at accomplishing the same with the Query Factory:

    <TestMethod()>
    Public Sub GetLastOrderDatePerCustomerUsingJoinWithGroupedSet()

        Dim QF As QueryFactory = New QueryFactory()
        Dim q = QF.Order.From(
            QueryTarget.InnerJoin(QF.Create("q1"
                                            ).Select(OrderFields.OrderDate.Max().As("LastOrderDate"),
                                                     OrderFields.CustomerId,
                                                     OrderFields.OrderId.Min
                                                     ).Where((OrderFields.EmployeeId = 1).And(OrderFields.OrderDate < New Date(1997, 1, 1))
                                                          ).GroupBy(OrderFields.CustomerId)
                                                      ).On(OrderFields.OrderId = QF.Field("OrderId").Source("q1")))
        Dim orders = New OrderCollection()
        orders.GetMulti(q)
        Assert.AreEqual(6, orders.Count)
    End Sub

This returns 22 records. Here is my attempt at using a query without a join:

    <TestMethod()>
    Public Sub GetLastOrderDatePerCustomerUsingGroupByWithHaving()

        Dim QF As QueryFactory = New QueryFactory()
        Dim q = QF.Order.Select(OrderFields.OrderDate.Max().As("LastOrderDate")
                                                     ).GroupBy(OrderFields.CustomerId
                                                               ).Having(OrderFields.EmployeeId = 1)
        Dim orders = New OrderCollection()
        orders.GetMulti(q)
        Assert.AreEqual(65, orders.Count)
    End Sub

This generates a dynamic query which is not a valid argument for GetMulti.

Clearly, I am missing the obvious.

Thanks

The query you are looking for in your last post should look like:

var q = qf.Create()
        .Select(OrderFields.CustomerId,
                OrderFields.OrderDate.Max(),
                OrderFields.EmployeeId)
        .GroupBy(OrderFields.CustomerId, OrderFields.EmployeeId)
            .Having((OrderFields.EmployeeId == 1)
                .And(OrderFields.OrderDate.Max().LesserThan(new DateTime(1997, 1, 1)))
        );

There is no need for a join, and the predicate should be in the .Having method.

Thanks! This is really appreciated here. Your support level is the best and I do make it known to my colleagues.

I am still getting the overload resolution error.

Here is the code:

        Dim QF As QueryFactory = New QueryFactory()
        Dim q = QF.Create.Select(OrderFields.CustomerId,
                                 OrderFields.OrderDate.Max(),
                                 OrderFields.EmployeeId
                                 ).GroupBy(OrderFields.CustomerId, OrderFields.EmployeeId
                                           ).Having((OrderFields.EmployeeId = 1
                                                     ).And(OrderFields.OrderDate.Max().LesserThan(New DateTime(1997, 1, 1))))
        Dim orders = New OrderCollection()
        orders.GetMulti(q)

and the error:

Error 1 Overload resolution failed because no accessible 'GetMulti' can be called with these arguments: 'Public Function GetMulti(selectFilter As SD.LLBLGen.Pro.ORMSupportClasses.IPredicate) As Boolean': Option Strict On disallows implicit conversions from 'SD.LLBLGen.Pro.QuerySpec.DynamicQuery' to 'SD.LLBLGen.Pro.ORMSupportClasses.IPredicate'. Extension method 'Public Function GetMulti(Of TEntity)(query As SD.LLBLGen.Pro.QuerySpec.EntityQuery(Of TEntity)) As SD.LLBLGen.Pro.ORMSupportClasses.IEntityCollection' defined in 'SD.LLBLGen.Pro.QuerySpec.SelfServicing.SelfServicingExtensionMethods': Data type(s) of the type parameter(s) cannot be inferred from these arguments. Specifying the data type(s) explicitly might correct this error. C:\My\EXAMPLES\Northwind\UnitTests\QueryFactoryTest.vb 180 9 UnitTests

I have the proper imports, I believe:

Imports SD.LLBLGen.Pro.QuerySpec
Imports SD.LLBLGen.Pro.QuerySpec.SelfServicing

David wrote:

Thanks! This is really appreciated here. Your support level is the best and I do make it known to my colleagues.

Thanks for the feedback

David wrote:

I am still getting the overload resolution error.

As you are fetching a DynamicQuery, use Dao classes, as described in the docs:

Dim dao As New OrderDAO()
Dim results = dao.FetchQuery(q)

This code works and returns anonymous type:

        Dim QF As QueryFactory = New QueryFactory()
        Dim q = QF.Create.Select(Function() New With {Key .CustomerID = OrderFields.CustomerId.ToValue(Of String)(),
                                 Key .Date = OrderFields.OrderDate.Max(),
                                 Key .employeeId = OrderFields.EmployeeId}
                                 ).GroupBy(OrderFields.CustomerId, OrderFields.EmployeeId
                                           ).Having((OrderFields.EmployeeId = 1
                                                     ).And(OrderFields.OrderDate.Max().LesserThan(New DateTime(1997, 1, 1))))
        Dim dao As New Northwind.DAL.DaoClasses.OrderDAO()
        Dim results = dao.FetchQuery(q)
        Assert.AreEqual(5, results.Count)

Is there a way to build a similar entity query that could then be used with GetMulti and fetch a typed collection.

Otherwise, is there a way to use the dynamic query but return an entity collection?

You are doing a groupBy, so How do you want to select the Order to include in the typed collection fetched? Or, how would you do it on sql?