I have a very classical example of a ER model: a table of data, let's call i:
"PRODUCTS" (product_id, last_change_id)
and have a table of states, let's call it:
In the middle, i have a classic intermediate table for a relation of many to many (keeps history). Let's call it:
"STATE_CHANGES" (change_id, product_state_id, product_id)
Now for a very simple need: i need to find all products that are currently in the product state
I was expecting to do something like (the property names are kind of what i think they would be if this example was true):
ProductsCollection productsColl = ProductsCollection.RunSelect (x => x.last_change_id.HasValue &&
x.last_change_idItem.product_state_id = 13);
The problem with this is that the products do not have a "last_changeItem" corresponding to the table column last_change_id, probably because there is also a relation of 1 product to many state_changes. I do have the property
product."state_changes" (collection) but i don't know how to solve this with that property.
I can get the data i want by doing a very expensive search by checking the products one by one but that would be too performance expensive.
I designed the database to have, in the products, the id of the last change specifically for this query need. But i'm not beeing able make any use of it (maybe because i'm not very knowlegeable of LINQ nor of nHydrate).
Any help will be apreciated (i also need paging).
With SQL i would do like this:
from products P
inner join state_changes SC on P.last_change_id = SC.change_id
where SC.product_state_id = 13