Maybe this can already be done but let me explain what i am doing.

I a product entity, and it has multiple attribute entities, and each attribute entities has multiple option entities. Now i my business logic, i first have to retrieve the attribute objects for the product, which that isnt bad, but what i dont like is that i have to do

foreach(Attribute in product) { GetOptions(Attribute); }

And load each options attribute indiviaully which can result in a LOT of round trips to be db. I think there should be some type of funcinction or something that would allow me to take my attribute entity collection and retrieve all options for the attributes in one round trip then assign them on the client, thus eliminating a crap load of round trips.

Is this possible already and i am jsut going about it wrong? If not, think we could add somthing like this to the additions you are making?

Answer wrote:

Maybe this can already be done but let me explain what i am doing.

I a product entity, and it has multiple attribute entities, and each attribute entities has multiple option entities. Now i my business logic, i first have to retrieve the attribute objects for the product, which that isnt bad, but what i dont like is that i have to do

foreach(Attribute in product) { GetOptions(Attribute); }

And load each options attribute indiviaully which can result in a LOT of round trips to be db. I think there should be some type of funcinction or something that would allow me to take my attribute entity collection and retrieve all options for the attributes in one round trip then assign them on the client, thus eliminating a crap load of round trips.

Is this possible already and i am jsut going about it wrong? If not, think we could add somthing like this to the additions you are making?

Well, if you've set up a relationship between Attribute and Option in your database schema, then there's a property in AttributeEntity called Attribute.Option defined as type "OptionCollection". While these Options aren't loaded by default when Attribute is loaded (it's called "Lazy Loading"), they're dynamically retrieved from the DB when the property is accessed (yes, in separate calls to the DB).

However, If you just wanted to get the collection of Options that are related to the Product, then you can bypass that previous step by accessing the property Product.Option(s) as opposed to Attribute.Option(s) because there's an implicit relationship between Product and Option via Attribute (n:m) and Frans was good enough to make sure that that collection of objects is there. So, Product.Option(s) will retrieve all Options related to that Product.

Another alternative is to use the GetMultiManyTo* overloads available in appropriate EntityCollections. Check those out to see if any will help you. They allow you to fill a collection with a set of entities based on a "filter" entity...check the docs for more info.

Jeff...

Ok, so that gets me half way there

I am using adapter by the way, i forgot to mention that.

Now, is there quick way to assign those product.options, to the product.attributes.options (obvisouly each product.option would have its own attribute it belongs to)?

Or am i going to have to write the logic to do this?

Do you see my point on how it seems very inefficient to load each one so i can go product.attributes.options. Cause if i have 50 or so options total and 12 attributes, thats 12 hits to the db, which it really should only be 1.

Answer wrote:

Ok, so that gets me half way there

I am using adapter by the way, i forgot to mention that.

Now, is there quick way to assign those product.options, to the product.attributes.options (obvisouly each product.option would have its own attribute it belongs to)?

Or am i going to have to write the logic to do this?

Do you see my point on how it seems very inefficient to load each one so i can go product.attributes.options. Cause if i have 50 or so options total and 12 attributes, thats 12 hits to the db, which it really should only be 1.

True, that's why multi-fetch paths will be added to adapter with the current runtime lib upgrades so you can specify a graph of objects to be fetched, which are then fetched from the DB in 1 go, using per object type 1 query.

I assume you are just grabbing attributes and attribute options for a product for the product detail page on an e-commerce website.

If this is the case, I recommend a stored procedure as this data is for read-only purposes and you want it quickly as possible.

If you have a table called Attribute, with a FK to the Product such as:

Attribute Table
------------------
Id (PK)
ProductId(FK)

and an Option Table(??) that has the options for each Attribute such as:

Option Table
----------------
Id (PK)
AttributeId (FK)
Title
DisplayOrder

then you can just grab the options for a product with a SP:

SELECT  *
FROM         Option INNER JOIN
                      Attribute ON Attribute.Id = Option.AttributeId
WHERE    (Attribute.ProductId = @ProductId)
ORDER BY Option.DisplayOrder, Option.Title;

I have my own e-commerce framework that I have built and I actually have a stored procedure, called GetProductDetails, that contains code like above, but also returns two other resultsets that get the details for the product and, of course, the list of attributes, which you will also need to display all the details about a product. Here in one call to the database (1 trip) you can get everything to display the product details.

Hope this helps.

True, that's why multi-fetch paths will be added to adapter with the current runtime lib upgrades so you can specify a graph of objects to be fetched, which are then fetched from the DB in 1 go, using per object type 1 query.

Excellent

Dave:

Its not actually for an ecommerce, but something very similar, right now im writing the code to add/edit the products so its not read only, but i will certainly keep what you said in mind once i get to area where i use it for readonly purposes as it makes sense.

How will "multi-fetch paths" work? What version are they going to be released in?

CT

ctadlock wrote:

How will "multi-fetch paths" work? What version are they going to be released in?

CT

In runtime library version 1.0.2004.1, which is close to completion (beta). multi-fetch paths will work like this: you specify which entities to retrieve based using code generated into the classes. You then specify that multifetchcollection with a fetch routine (entity fetch or collection fetch) and behind the scenes all queries and objects are loaded for you.