Query with join by last change id

Jun 7, 2011 at 12:08 PM

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:

"PRODUCT_STATES" (product_state_id)

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 13.

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:

select P.*
from products P
    inner join state_changes SC on P.last_change_id = SC.change_id
where SC.product_state_id = 13

Jun 9, 2011 at 12:57 AM

The infomration you need does not actually require a join. If you know the state id which it seems you do, then simply ask for it. You are only asking for products with a property of X so just ask for it.

ProductsCollection productsColl = ProductsCollection.RunSelect (x => x.LastChangeId == 13);

I started to build out the model before I realized this. If you wish to walk from Product to ProductState to StateChange then add these two relations and you can walk with the query syntax. If my first query does not work for your situation then send your real model and broken code example to chrisd@nhydrate.org.

Jun 9, 2011 at 10:37 AM
Edited Jun 9, 2011 at 10:54 AM

I'm sorry but i may have not explained myself well enough.

That query would give me a specific "STATE_CHANGE". I do keep the id of the last state_change. But what i need is all products whose last "STATE_CHANGE" has product_state_id == 13 (FK for the 3rd table).

Instead of your example:

ProductsCollection productsColl = ProductsCollection.RunSelect (x => x.LastChangeId == 13);

I would need something like:

ProductsCollection productsColl = ProductsCollection.RunSelect (x => x.LastChangeItem.product_state_id == 13);


Is there anything i could do to explain it better? Is it possible to upload a ER diagram image?

The problem is quite simple, in reality, but explaining it isn't all that trivial.

Jun 9, 2011 at 10:52 AM
Edited Jun 9, 2011 at 10:56 AM

I implemented one solution by "extending" de nHydrate DAL. What i did was:

# Stored procedure that selects the IDs of the products (including a select count and paging in a similar method as the other nHydrate Stored Procedures):

select products.product_id
from products
    inner join state_changes on products.last_change_id = state_changes.change_id
where state_changes.product_state_id = @WhatEver

# In the productsCollection.cs i added a new method to access the DataBase and retrieve a DataSet with the count and with the list of IDs (should allways be around 5~10 ids). I build a List<int> with the IDs returned then i execute the following nHydrate query:

resultsCollection = RunSelect (x => productIDs.Contains (x.product_id));


It is solving the problem. If answering my original question proves complicated, could you instead give me any advice on any bad performance you may notice in the process i implemented?

Jun 10, 2011 at 8:39 AM

Ok, never mind the whole question. The property i was looking for does exists, i just wasn't finding it because it has a weird name that i wasn't expecting.

The real tables are these (although the structure is exactly the same as the "products" example):

livretes (products)

    - livreteid

    - ultima_mudanca_estado_id *** (last_change_id -> FK for the next table:  mudancas_estado_livrete.internal_id)

mudancas_estado_livrete (state_changes)

    - internal_id

    - livreteid

    - estado_livrete_id

estados_livrete (product_states)

    - estado_livrete_id


The property i was looking to find an ITEM for (***) has the weird name:


So the select works with:

_TotalResults = livretesCollection.GetCount (x => x.Livretes_estados_livretemudancas_estado_livrete.estado_livrete_id == processStateID);