Hi there

(LLBLGen 2.5, could upgrade to 2.6)

I have a simple shop solution that consists of the following entities: - ProductEntity - OrderEntity - OrderedProductEntity

...accordingly, there is an m:n relation between products and orders through the OrderedProduct entity.

I need to aggregate order data from a product's perspective in order to do some simple analysis. For this, I created a simple TypedList, that provides the following information:

• ProductId (GROUPBY)
• OrderStatus (GROUPBY)
• Total ordered products: SUM(OrderedProduct.Quantity)

Accordingly, for each product, I get the number of ordered items, grouped by the order status (paid / still open), e.g:

Lenovo_T61 / Paid / 15 pieces Lenovo_T61 / UnPaid / 7 pieces Lenovo_X30 / Paid / 12pieces Lenovo_X30 / UnPaid / 7pieces ...

What's missing is a link to my product entites:

• I have an EntityCollection<ProductEntity> that was created on dynamic filters (e.g. get all Lenove notebooks)
• Foreach product, I would like to get the aggregated data of my typed list

...of course, I could do the plumbing manually, but I have the feeling that there should be an easier way, as LLBLGen does know that there is a relation between my product entities and the TypedList. Any hints?

Thanks for your advice Philipp

Hi Phillip,

You can't join directly TypedLists and Entities as TypedLists are build of entities, so that doesn't make sense completely.

As I see, you have 3 options here:

A. Modify your TypedList so it include the missing ProductEntity fields. This is likely the easiest way if you already have the TypedList and you only want read-only results.

B. Create a custom property on your ProductEntity generated code (either in USER_CODE_REGIONS or partial class). This property would return a filled typedList filtered on the involved product.

C. Mapping DB View as Entity and relate it to ProductEntity:

1. Create you DB View based on the desire result.

CREATE VIEW [dbo].[ProductCountryQuantity]
AS
SELECT   TOP (100) PERCENT od.ProductID, o.ShipCountry, SUM(od.Quantity) AS TotalQuantity
FROM         dbo.[Order Details] AS od INNER JOIN
                      dbo.Orders AS o ON od.OrderID = o.OrderID
GROUP BY od.ProductID, o.ShipCountry
ORDER BY od.ProductID, o.ShipCountry

1. At your LLBLGenPro project, map the new view as an entity ([CTRL] + [V]). Say you named it "[i]ProductStatusQuantity[/i]"

2. Edit the _ProductStatusQuantity _Entity. Make the _ProductId _and _Status _primary keys (check the "Is part of primary key" checkbox" for each field).

3. Create a relation between _Product _Entity and _ProductStatusQuantity _Entity (_ProductStatusQuantity _--(m:1)--> _Product _via ProductId).

4. Ready!! Regenerate. You now could do something like:

EntityCollection<ProductEntity> products = new EntityCollection<ProductEntity>();

PrefetchPath2 path = new PrefetchPath2((int)EntityType.ProductEntity);
path.Add(ProductEntity.PrefetchPathProductStatusQuantity);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(products, null, path);
}

// at this point you can access the related ProductStatusQuantitycollection ...
// produtcs[0]. PrefetchPathProductStatusQuantity

So, A and C don't require extra code Please let us know if you need further help on this.

Hi David

First of all, thanks for the fast reply! Great support

I was going for option C (although I was hoping I could go without putting a view or procedure on the server), but there is a catch:

• as already mentioned, I want to aggregate order information for certain products
• however, I also need to restrict the aggregated data based on order data (e.g. only include orders of the year 2007).

With a typed list, the relation to the "Orders" table is still intact, so I can easily set my filter to fields of "OrderEntity". With a view, however, there is no such thing as an "OrderDate", because that information is already lost once the view has been queried (and aggregated all orders). So I'm one step behind.

However, option B still sounds not very attractive (determine all products in an "EntityCollection<ProductEntity>", then query the DB with all the product IDs, then assign the results to the individual "ProductEntity" collections). Is there something I overlooked?

Thanks again

Well you may also add a property to the ProductEntity (inside a custom code region or in a partial class file), to return an instance of the TypedList filtered on the ProductId.

You may also add a method inside the ProductEntity to set another filters for the TyedList property.

Walaa wrote:

Well you may also add a property to the ProductEntity (inside a custom code region or in a partial class file), to return an instance of the TypedList filtered on the ProductId.

You may also add a method inside the ProductEntity to set another filters for the TyedList property.

Thats not really an option, because it would require me to trigger a query for each individual product. I currently added a property to ProductEntity that provides a collection of MyTypedListRows. Then, I construct a query that gets a typed list for all my products and subsequently update the individual collections. That does work of course, but I was hoping for a simpler solution. It's not that bad, though

I currently added a property to ProductEntity that provides a collection of MyTypedListRows. Then, I construct a query that gets a typed list for all my products and subsequently update the individual collections. That does work of course, but I was hoping for a simpler solution. It's not that bad, though

Can't think of a better solution.