Hi,

Problem Statement:

We have a database View which is generated as TypedView by LLBLGen. We want to apply predicate expressions and relation expressions, to give search results to user. The problem we found is -- LLBLGen does not take relations in the Fill method of the typed view. It has ChildRelations and ParentRelations properties, but we found no documentation for that and we are not sure whether we can use it or not.

Considering this as a limitation, we went for TypedList, because it does take relation expression in Fill method. We suceedded in making the TypedList by painfully going thro the designer screens, but then found that you cannot have group by as aggregate function against the fields. However, other aggregate functions like Sum etc are supported. We thought LLBLGen would include all other select fields in the group by clause because otherwise the query cannot run anyway, but it seems that it did not. Though it might be possible to pass group by fields using the code, you can see from the SQL below that there are lot of fields in the group by.

Do you have any way to go around this TypedView or TypedList problem? Note: Ideally we would like to continue using TypedView.

Other Info Version used 2 (July release) Database : SQL Server 2005

If you want to have a look at the view then, here it is:

SELECT  
    dbo.SIHead.SIHeadId, dbo.SIHead.InvoiceTypeId, dbo.SIHead.InvoiceNumber, dbo.SIHead.CreationDate, dbo.SIHead.DeliveryDate, 
    dbo.SIHead.ReceivedDate, dbo.SIHead.CaptureDate, dbo.SIHead.BuyerGLDate, dbo.SIHead.SupplierGLDate, dbo.SIHead.TaxPoint, 
    dbo.SIHead.CurrencyCode, dbo.SIHead.TotalBeforeTax AS TotalPrice, dbo.SIHead.TotalTax, dbo.SIHead.TotalIncludingTax, dbo.SIHead.Comment, 
    dbo.SIHead.FromCompanyId, dbo.SIHead.ToCompanyId, dbo.SIHead.PayeeAuxId, dbo.SIHead.BuyerInvoiceStateMask, 
    dbo.SIHead.SupplierInvoiceStateMask, dbo.SIHead.FactorInvoiceStateMask, dbo.SIHead.DueDate, dbo.SIHead.RawSalesInvoiceId, 
    FactorOrg.OrganisationId AS FactorOrganisationId, FactorOrg.OrganisationName AS FactorOrganisationName, 
    BuyerCompanyAux.CompanyName AS BuyerCompanyName, BuyerOrg.OrganisationId AS BuyerOrganisationId, 
    BuyerOrg.OrganisationName AS BuyerOrganisationName, SupplierCompanyAux.CompanyName AS SupplierCompanyName, 
    SupplierOrg.OrganisationId AS SupplierOrganisationId, SupplierOrg.OrganisationName AS SupplierOrganisationName, dbo.SIHead.FactorCompanyId, 
    dbo.SIHead.DiscountSchemeId, dbo.DiscountScheme.Code AS DiscountSchemeCode, FactorCompany.CompanyName AS FactorCompanyName,
    COUNT(dbo.SIHeadConversationThread.SIHeadId) AS NumOfConvThreads, COUNT(dbo.SIHeadAttachment.SIHeadId) AS NumOfAttachment
FROM        
    dbo.Organisation AS SupplierOrg RIGHT OUTER JOIN
    dbo.Company AS SupplierCompany ON SupplierOrg.OrganisationId = SupplierCompany.OrganisationId RIGHT OUTER JOIN
    dbo.CompanyAux AS BuyerCompanyAux RIGHT OUTER JOIN
    dbo.SIHead INNER JOIN
    dbo.CompanyAux AS SupplierCompanyAux ON dbo.SIHead.FromCompanyId = SupplierCompanyAux.CompanyAuxId LEFT OUTER JOIN
    dbo.SIHeadAttachment ON dbo.SIHead.SIHeadId = dbo.SIHeadAttachment.SIHeadId LEFT OUTER JOIN
    dbo.SIHeadConversationThread ON dbo.SIHead.SIHeadId = dbo.SIHeadConversationThread.SIHeadId LEFT OUTER JOIN
    dbo.Company AS FactorCompany ON dbo.SIHead.FactorCompanyId = FactorCompany.CompanyId ON 
    BuyerCompanyAux.CompanyAuxId = dbo.SIHead.ToCompanyId LEFT OUTER JOIN
    dbo.Company AS BuyerCompany LEFT OUTER JOIN
    dbo.Organisation AS BuyerOrg ON BuyerCompany.OrganisationId = BuyerOrg.OrganisationId ON 
    BuyerCompanyAux.CompanyId = BuyerCompany.CompanyId ON SupplierCompany.CompanyId = SupplierCompanyAux.CompanyId LEFT OUTER JOIN
    dbo.DiscountScheme ON dbo.SIHead.DiscountSchemeId = dbo.DiscountScheme.DiscountSchemeId LEFT OUTER JOIN
    dbo.Organisation AS FactorOrg ON FactorCompany.OrganisationId = FactorOrg.OrganisationId

GROUP BY 
    dbo.SIHead.SIHeadId, dbo.SIHead.InvoiceTypeId, dbo.SIHead.InvoiceNumber, dbo.SIHead.CreationDate, dbo.SIHead.DeliveryDate, 
    dbo.SIHead.ReceivedDate, dbo.SIHead.CaptureDate, dbo.SIHead.BuyerGLDate, dbo.SIHead.SupplierGLDate, dbo.SIHead.TaxPoint, 
    dbo.SIHead.CurrencyCode, dbo.SIHead.TotalBeforeTax, dbo.SIHead.TotalTax, dbo.SIHead.TotalIncludingTax, dbo.SIHead.Comment, 
    dbo.SIHead.FromCompanyId, dbo.SIHead.ToCompanyId, dbo.SIHead.PayeeAuxId, dbo.SIHead.BuyerInvoiceStateMask, 
    dbo.SIHead.SupplierInvoiceStateMask, dbo.SIHead.FactorInvoiceStateMask, dbo.SIHead.DueDate, dbo.SIHead.RawSalesInvoiceId, 
    FactorOrg.OrganisationId, FactorOrg.OrganisationName, BuyerCompanyAux.CompanyName, BuyerOrg.OrganisationId, BuyerOrg.OrganisationName, 
    SupplierCompanyAux.CompanyName, SupplierOrg.OrganisationId, SupplierOrg.OrganisationName, dbo.SIHead.FactorCompanyId, 
    dbo.SIHead.DiscountSchemeId, dbo.DiscountScheme.Code, FactorCompany.CompanyName

thanks Puneet

We suceedded in making the TypedList by painfully going thro the designer screens, but then found that you cannot have group by as aggregate function against the fields.

What's so particular painful about the typedlist editor? It's IMHO easier to use than for example a view designer in sqlserver management studio.

There's no groupby definition in the typedlist editor because that would lock the typedlist in always have a groupby. The aggregate function sets are indeed designable, because setting these up would be less straight forward: the groupby collection is passed to the fetch method, while the aggregate functions are defined on the fields and this then should be done in a partial class of the typedlist, which can be cumbersome, hence the editor.

If you have a lot of fields in a groupby, you can create the groupby collection by simply looping through the fields of the typedlist in a few lines of code. GroupByCollection groupBy = new GroupByCollection(); foreach(IEntityField field in typedlist.BuildResultset()) { groupBy.Add(field); }

Often groupby clauses are used in sets which have few fields, so one doesn't run into a problem with this. Having a large groupby collection is typically not that useful. One then can also opt for a dyn. list with a scalarqueryexpression (Scalar subquery in the selectlist) for example if you want to merge a normal set and aggregated data from a related table (which is what you're trying to do as it seems).

What's so particular painful about the typedlist editor? It's IMHO easier to use than for example a view designer in sqlserver management studio.

When first started off by adding all the tables in the designer, and LLBLGen automatically created all the joins. Some of which we did not need. We tried various things to delete some relations anda change others, but it did not work well. After this, we specified relations after adding each new table in the designer. The story after that is as I described inthe problem stamement above.

I am not aware how LLBLGen handles group by clauses. But from the user point of view, wouldn't it be good that all non agggregate fields are automatically added by LLBLGen and any additional ones specified by user in the code get added to the collection. This way both situations will be satisfied. Again - this si only an idea, I do not know about practicality of doing it.

If you have a lot of fields in a groupby, you can create the groupby collection by simply looping through the fields of the typedlist in a few lines of code. GroupByCollection groupBy = new GroupByCollection(); foreach(IEntityField field in typedlist.BuildResultset()) { groupBy.Add(field); }

This sounds like a good idea, we can implement this. Thanks !

Often groupby clauses are used in sets which have few fields, so one doesn't run into a problem with this. Having a large groupby collection is typically not that useful. One then can also opt for a dyn. list with a scalarqueryexpression (Scalar subquery in the selectlist) for example if you want to merge a normal set and aggregated data from a related table (which is what you're trying to do as it seems).

Yes, it is strange looking query with many fields in the group by. I am not sure about the performance implications of the sub-query. We need to do some load testing to choose one of the two ways. This is the query as it exists at the moment, it can change in few days.

puneet wrote:

What's so particular painful about the typedlist editor? It's IMHO easier to use than for example a view designer in sqlserver management studio.

When first started off by adding all the tables in the designer, and LLBLGen automatically created all the joins. Some of which we did not need. We tried various things to delete some relations anda change others, but it did not work well. After this, we specified relations after adding each new table in the designer. The story after that is as I described inthe problem stamement above.

As discussed in the documentation, you should HIDE relations you don't need, in the entity editor's Relations tab, where you can select more of them at once and hide them. By default m:n relations are hidden, so you don't get them. As discussed in the documentation, hidden relations aren't showing up in the generated code.

And what has this to do with the typedlist editor btw?.

The relations aren't 'joins', they're relations.

I am not aware how LLBLGen handles group by clauses.

Group by is also described in the documentation, for example in the How Do I? section in the Best practises: "How do I use a group by clause in a dynamic list ?", or in the example with a typedlist in the "Using the generated code -> SelfServicing -> Using TypedLists/views.. -> Using GROUP BY and HAVING clauses.".

But from the user point of view, wouldn't it be good that all non agggregate fields are automatically added by LLBLGen and any additional ones specified by user in the code get added to the collection. This way both situations will be satisfied. Again - this si only an idea, I do not know about practicality of doing it.

We can't do that, as group by is order specific, so in which order should these fields be added to the group by collection?