Hello Guys,

I have read everything that is required to solve this but I still cannot find a solution, so sorry.

I want to create a typed list from a Typed View and an Entity Collection, which is illustrated in the following SQL query:

Select  c.strCustomerId, c.strCompanyName, 
    (Select SUM(ca.decTrxAmount - ca.decAmountAllocated) As Amount 
        From vwCustomerAllocation ca
        Where ca.blnAllocated = 0 And ca.strTrxAllocation = 'Debit' And ca.strCustomerId = c.strCustomerId
            And ca.dtmTrxDate Between '2010/06/15 0:00:00' And '2010/07/15 23:59:59') As ZeroToThirty
From Customer c

There are other fields I want to create, but, if we can achieve this first I think I can continue with the rest.

I have started like this:

        Dim fields As New ResultsetFields(3)
        fields.DefineField(CustomerFields.StrCustomerId, 0)
        fields.DefineField(CustomerFields.StrCompanyName, 1)

        Dim dtGroupBy As New GroupByCollection(fields(0))

        Dim ex As New Expression(CustomerAllocationFields.DecTrxAmount, ExOp.Sub, CustomerAllocationFields.DecAmountAllocated)
        Dim ef As New EntityField("MainField", ex, AggregateFunction.Sum)

        'Create field for 0 - 30 
        fields.DefineField(New EntityField("0-30", New ScalarQueryExpression(ef, CustomerAgingFilter(0, 30), CustomerRelations)), 2)

        Dim filter As IPredicateExpression = New PredicateExpression

        Dim results As New DataTable()
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, results, 0, Nothing, filter, Nothing, True, dtGroupBy, Nothing, 0, 0)

        Private Function CustomerAgingFilter(ByVal fromDays As Integer, ByVal toDays As Integer) As IPredicateExpression

        Dim fPR As IPredicateExpression = New PredicateExpression

        Dim dateFrom As Date = Date.Today.AddDays(toDays * -1)
        Dim dateto As Date = Date.Today.AddDays(fromDays * -1)

        fPR.Add(CustomerAllocationFields.StrCustomerId = CustomerFields.StrCustomerId)
        fPR.AddWithAnd(CustomerAllocationFields.BlnAllocated = False)
        fPR.AddWithAnd(CustomerAllocationFields.StrTrxAllocation = "Debit")

        fPR.AddWithAnd(New FieldBetweenPredicate(CustomerAllocationFields.DtmTrxDate, New Date(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0), New Date(dateto.Year, dateto.Month, dateto.Day, 23, 59, 59)))

        Return fPR

    End Function

        Private Function CustomerRelations() As IRelationCollection

        Dim r As IRelationCollection = New RelationCollection()
        r.Add(New EntityRelation(CustomerFields.StrCustomerId, CustomerAllocationFields.StrCustomerId, RelationType.OneToMany))

        Return r

    End Function

But it isn't working. What am I doing wrong? I keep getting the total for ALL customers in every row.

Customer = CustomerEntity CustomerAllocation = TypeView

Hi

Could you post the SQL that is actually generated by your call to GetMultiAsDataTable ? This will give us more of a clue what is going on.

Thanks

Matt

Thanks for the prompt reply Matt

Generated Sql query: 
Query: SELECT [InfomaticsERP].[dbo].[Customer].[strCustomerId] AS [StrCustomerId], [InfomaticsERP].[dbo].[Customer].[strCompanyName] AS [StrCompanyName], (SELECT SUM([InfomaticsERP].[dbo].[vwCustomerAllocation].[decTrxAmount] - [InfomaticsERP].[dbo].[vwCustomerAllocation].[decAmountAllocated]) AS [MainField] FROM ( [InfomaticsERP].[dbo].[Customer]  INNER JOIN [InfomaticsERP].[dbo].[vwCustomerAllocation]  ON  [InfomaticsERP].[dbo].[Customer].[strCustomerId]=[InfomaticsERP].[dbo].[vwCustomerAllocation].[strCustomerId]) WHERE ( [InfomaticsERP].[dbo].[vwCustomerAllocation].[strCustomerId] = [InfomaticsERP].[dbo].[Customer].[strCustomerId] AND [InfomaticsERP].[dbo].[vwCustomerAllocation].[blnAllocated] = @BlnAllocated1 AND [InfomaticsERP].[dbo].[vwCustomerAllocation].[strTrxAllocation] = @StrTrxAllocation2 AND [InfomaticsERP].[dbo].[vwCustomerAllocation].[dtmTrxDate] BETWEEN @DtmTrxDate3 AND @DtmTrxDate4)) AS [0-30] FROM [InfomaticsERP].[dbo].[Customer] 

Parameter: @BlnAllocated1 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Parameter: @StrTrxAllocation2 : AnsiString. Length: 6. Precision: 0. Scale: 0. Direction: Input. Value: "Debit".

Parameter: @DtmTrxDate3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 16/06/2010 12:00:00 AM.

Parameter: @DtmTrxDate4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 16/07/2010 11:59:59 PM.

In your SQL Code posted earlier, you don't JOIN to the Customer entity in the Sub-Select query, so why do you are you adding a relation to the ScalarQueryExpression?

I guess you're right; this is my gazillionth attempt at solving this problem. I then (after almost 4hours) gave up and decided to ask you guys for an answer.

I have since then decided to use another view and work from there, but would really like to see how I can solve this using LLBL, I hate having to right logical could in views unless I REALLY have to.

How is it solved?

Bear in mind that this works almost perfectly if it is between two tables (with a relation in the DB) but because there's a TypedView in the middle I guess it is different.

Also, this is clearly an 'aliasing' issue. Right?

According to your first approximate SQL, you don't need groupBy either a Join. See, this example that accomplish something similar:

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(CustomerFields.CustomerId, 0);
fields.DefineField(CustomerFields.CompanyName, 1);

Expression sumExp = new Expression(InvoiceFields.UnitPrice, ExOp.Add, InvoiceFields.Freight);
EntityField2 sumField = InvoiceFields.UnitPrice.SetExpression(sumExp).SetAggregateFunction(AggregateFunction.Sum);

IPredicateExpression innerFilter = new PredicateExpression(
    InvoiceFields.CustomerId == CustomerFields.CustomerId);

ScalarQueryExpression sumQueryExp = new ScalarQueryExpression(
    sumField, innerFilter);

fields.DefineField(InvoiceFields.UnitPrice.SetExpression(sumQueryExp), 2);

DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, null);
}

that produces this sql code:

SELECT 
     [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],    
     [Northwind].[dbo].[Customers].[CompanyName], 
     (SELECT 
          SUM([Northwind].[dbo].[Invoices].[UnitPrice] + [Northwind].[dbo].[Invoices].[Freight]) AS [UnitPrice] 
      FROM [Northwind].[dbo].[Invoices]   
      WHERE ( [Northwind].[dbo].[Invoices].[CustomerID] = [Northwind].[dbo].[Customers].[CustomerID])) AS [UnitPrice]

FROM [Northwind].[dbo].[Customers]  

Note the innerFilter variable and the WHERE in the subquery.