Fluent NHibernate: Custom condition (WHERE clause) on get, update and delete

Question

I have a table that contains information from many customers

ID | employeename | customerId
------------------------------
 1 | employee1    |  188
 2 | employee2    |  188
 3 | employee3    |  177

Now I would like to get only those employees, whose customerId is 188. How do I map this with Fluent NHibernate so, that on update and delete there would also be WHERE customerId = 188 ?

Current Mapping is something like:

Id(x => x.Id);
Map(x => x.Name).Column("employeename");
Map(x => x.CustomerId).Column("customerId");

Adding Where("customerId = 188") only results custom where clause in SELECT. I would need following UPDATE-clause to happen on saveorupdate.

UPDATE employees SET employeename="employ" WHERE ID = 2 AND customerId = 188;

Answer 1

You are thinking wrong with this SQL mind in your head.

1) Add HasMany( x => x.Employees ).Inverse().AsSet(); in your Customer class.
2) Add References( x=> x.Customer ); in your Employee class.

This is called bidirectional mapping.

Look here: http://www.progware.org/Blog/post/NHibernate-inverse3dtrue-and-cascade3dsave-update-demo.aspx

The idea is that you create your objects and you assign values to them. After that NHibernate executes SQL statements if you have proper mapping files. Dont write your sql queries and then forcing NHibernate to generate exactly the same ones. Instead write your mappings and see what SQL NHibernate generates and try to optimize it.

PS: Dont forget to add the cascade...