Fighting with NHibernate Queries for dummies, I use a Fetch which brings a MyDataPartRecord, but I realize that to obtain thecorresponding MyDataPart object it is not obvious.
A new MyDataPar{ Record=MyDataPartRecord) seems missing something important (or I have a bug somewehere else.

This seems so easy that it is frustrating...as usual.

How the get the associated Part when you have the Record object ?

You should never have a part record without a content item, and if you have the content item, you have the parts.

When you follow the exemple for 1-N relations, address-state, you get a StateRecord in the AddressPart ?
In my case, I have a situation where 1 part contains 3 records from other Parts: a product price (productpricelist), attached to a currency( CurrencyPart ), a product,(ProductPart) and a Price List (PriceListPart).
I follow the doc sample, only creating the columns for the 'external indexes' in the product price table.
It works as expected, reading the ProductPriceListPart, I get the records automatically filled.

I have situations where from a list of Price Lists, selecting one I display all the product prices for a selected currency.


The query to select the prices is logically like this.
Get the Price List Part using _contentManager.Get<PriceListPart>(PriceListId);
Then select the currencies already existing in the Prices for this Price List, here it became lees obvious

        public IEnumerable<CurrencyPartRecord> GetCurrencies(PriceListPart PLPart) {
            return _contentManager
                .List<ProductPriceListPart>()
                .Where(p => p.PriceList.Id == PLPart.Id)
                .Select( p => p.Currency )
                .Distinct();
        }

Here I get a list of currency records. with the Id in one of these currencies, I select the PriceListParts using it with

        {
            return _contentManager.Query<ProductPriceListPart, ProductPriceListPartRecord>()
               .Where(p => p.PriceList.Id == PLPart.Id && p.Currency.Id == cpart.Id)
               .List()
               .Select( prop => prop.As<ProductPriceListPart>());
        }

On this step I have ProductPriceList content parts, but each contains a ProductPartRecord, and if, in the template displaying this list of elements, I want to insert an Url.ItemDisplayUrl(...) I need a ContentItem (all the Html and Url nice extenders don't work with records)., So the only solution I found have been to reload in a loop all the productPart and insert them in a Dictionary<int,ProductPart> and access it in the loop building the html table.

Same problem for currencies, I have to reload them with the ids in the list of CurrencyPartRecords.

Seems that I am doubling the database access, even if the NH cache (?) could do something nice, but each cache have its limits in term of number and size it manages.

Is it the correct way to deal with this in Orchard ?

StateRecord is not a part record. You should be connecting items, not part records, and you should take advantage of GetMany to retrieve them.

If I follow what you say, my ProductPriceListPart should not contains the 'external relations records' because these are Parts.
In my migration, I should Create the related ContentPart and attach to it the 3 related parts ?
I don't really understand how to have the 3 '1-N' relations with this concept ? Could you give more detail ?

Moreover, I must manage the unicity of the quadruplet (price,product,pricelist,currency), and if I have the 3 indexes in one productPriceListCurrency record, it is easy (or should be, here I see that there is something that the Orchard dev are not used to ???? ) to put a unique constraint on it and have the garanty that the DB keeps the constraint in coherence as a last show stopper security.
How to do this with attached parts in a ContentType ?

I have seen number of Orchard code samples managing relations as I have done (this is the reason why I did like this), I was thinking it is the normal and more efficient way.
Especially on a concept as pricing where you may have hundred of PriceList, several currencies and hundred of products, database must be designed for efficiency.

If I look at Nwazet Commerce for example, the fact that the Product Name is in a separate Part leads to difficulties and complexities: when you get the product part, if you want the name of the product (not the sku), you must get the related TitlePart ?

(are you no more sleeping, as it is morning here....)

No that's not at all what I mean. What I mean is that your relationship is between one content part, and content items, not between content parts, because a part is meaningless without a content item. So the relationship points to a content item id. When you want to use anything on that related content item you must get the whole thing. This is how Orchard is designed: content items are composite entities made of parts. This does not lead to "difficulties and complexities", this is precisely what creates the flexibility, and pretty much the whole point of Orchard.
There is an alternative however, because a full content item is not always required: relating to plain records. That's what's done in the docs iirc.
Sleep is overrated.

Thanks, its going its way.