Hi.

When showing reports we usually use want to collect data from several entities into one entity. We noticed the feature WithProjector and thought that might suit our needs. However in one case we want to join a table with a related child table.

In a "normal" fetch we would get an entity which would have a list of a child entity. But with the use of WithProjector I don't know how to fill a sub-list of the report entity.

Is this possible?

This is a simplified example of a fetch we are doing:

Private Sub TestMethod()
            Dim qf As New QueryFactory
            Dim q As EntityQuery(Of CustomerEntity) = qf.Customer
            Dim adapter As New MyDataAccessAdapter(App.Settings.DatabaseConnection)

            Dim qDyn = q. _
           From(QueryTarget.InnerJoin(qf.CustomerOrder). _
                On(CustomerEntity.CustomerId = CustomerOrder.Fkcustomer)). _
        Select(CustomerEntity.Field1, CustomerEntity.Field2, CustomerOrder.CustOrdId) _
                .WithProjector(Of ReportCustomerEntity)(Function(r)


                                                            Return New ReportCustomerEntity() With {.Field1 = CastString(r("Field1")),
                                                                                        .Field2 = CastString(r("Field2")),
                                                                                        .CustOrdId = CastString(r("CustOrdId"))}


                                                        End Function)

            Dim resList = adapter.FetchQuery(qDyn)

        End Sub

In the above example, can we fill a list of CustomerOrder in our ReportCustomerEntity?

In the above example, can we fill a list of CustomerOrder in our ReportCustomerEntity?

I'm not sure what you are trying to do. Could you please layout the involved entities' schema.

And whether you need to fetch entities and related entities, or are you fetching projected resultSet of some Joins?

These are our entities, we want to display a report of customers and their newest customerOrder among other things.

Public Class CustomerEntity
    Public Property ID As Integer
    Public Property Name As String
    Public Property Orders As List(Of CustomerOrderEntity)
End Class

Public Class CustomerOrderEntity
    Public Property ID As Integer
    Public Property FkCustomer As Integer
    Public Property Sum As Decimal
End Class

So we create a Report entity that contains all data we want to be accessible in a couple of reports:

Public Class ReportCustomerEntity
    Public Property ID As Integer
    Public Property Name As String

    Public Property NewestOrder As CustomerOrderEntity

    Public Property SomeOtherField As String
    Public Property ReportCreated As Date
    Public Property ReportCreatedBy As String
End Class

Through Projection we understand that we can join different tables and fill a custom class. The problem here is the CustomerOrderEntity, can we call a llblgen fill method which takes a projectionrow and returns a customerorderentity?

Please check Hierarchical Sets

Ah, that looks interesting Walaa!

Trying that but I've already stumbled upon a new problem. When I do this:

Dim meta As New LinqMetaData(adapter)
Dim qDyn As List(Of ReportCustomerEntity) = (From c In meta.Customer
                    Where c.Id > 0
                    Select New ReportCustomerEntity With
                    {
                        .Name = c.Name
                    }).ToList

I get an exception:

Exception has been thrown by the target of an invocation. {"Unable to cast object of type 'Swordfish.Model.HelperClasses.EntityCollection1[Swordfish.Model.EntityClasses.CustomerEntity]' to type 'System.Collections.Generic.IList1[Swordfish.Model.Report.ReportCustomerEntity]'."}

What am I doing wrong?

Got rid of the exception. It was something with inheritance since my ReportCustomerEntity inherits from CustomerEntity. When I removed the inheritance it worked.

Fixed it with the help of the link. The resulting code:

Private Sub TestMethod()
            Dim adapter As New DataAccessAdapter(App.Settings.DatabaseConnection)
            Dim meta As New LinqMetaData(adapter)

            Dim tl As List(Of ReportCustomerEntity)

            Dim records As IQueryable(Of ReportCustomerEntity) = (From c In meta.Customer
                                                                  Join t2 In meta.CustomerOrder On c.Id Equals t2.FKCustomer
                                                                   Select New ReportCustomerEntity With {
                                                                        .Id = c.Id,
                                                                        .Email = c.Email,
                                                                        .FirstName = c.FirstName,
                                                                        .Orders = (From o In c.Orders
                                                                                            Where o.OrderEmp = "SYS"
                                                                                            Select o).ToList
                                                                                                                                    })
            tl = records.ToList
        End Sub

Thanks