What do you see to be wrong with this code?
At first I did not see any issues with it so I copied it and changed a bit for my another query. But current method is wrong. I discovered this with UTs.
First, Projections.RowCount() generates COUNT(*) in select statement.
This is query, which I got from my new Unit Tests: RuleExists_HitsDatabase_ThereIsNoRule.
Result of this query is number of rows like on picture below:
So, verification return result.Count > 0; is absolutely incorrect.
I’ve chagned it to return (int)result > 0;
Do not be lazy to write Unit Tests both for success and failure sceneries.
To add “order by” to your criteria you need this statement.
To add “top 10” to your criteria you need this statement
So code about which I’m talking could look like:
SetResultTransformer or “Why did I get 5 results instead of 10?”
So you expect to have top 10 priority Customers with status Created.
In scope of my current task it was needed to add priority to this query, so I decided to unit test it of course.
In Debug I found that there are actually 5 results in resulting collection.
That is because generated SQL generates result which contains duplicated CUSTOMER_IDs, that is because I have join-s there. But then why did not I get 10 duplicated Customers? Because query has ResultTransformer which is applied after SQL has been ran. (That is 100% since I took a look at generated SQL via NHibernateProfiler).
So .SetResultTransformer(new DistinctRootEntityResultTransformer()) is removing of all duplicated entries of my root entity (Customer).
Good explanation to this you can find here.
I have table
CUSTOMER_ID is primary key.
I need to handle assigning of the CustomerID property manually. For
example I created new Customer with CustomerID = 777 and Name
= “Andriy Buday”.
When I call method Session.Save(customer);
I want NHibernate to generate me SQL like this:
Unfortunately I’m getting errors.
Main issue here is that Nhibernate tries to generate ID for me. But I want to save my entity exactly with 777.
So I need to manually setup my ID property with adding GeneratedBy.Assigned();
I had something like this in the SaveCustomer method:
And this don’t work correctly. Simple change to
resolved my issue.
Next SQL, which verifies if customer row exists in database:
Will be generated with next NHibernate Query:
Please note, that you could use not only primary key, but any other property in your where condition.
Sometime ago I faced with NHibernate issue and spent much time on figuring out how to resolve it. Issue was with saving complicated entity.
With NHibernate Profiler I found that this fails on the next SQL:
With better look you will see that there two times RESOURCE_ID mentioned so this looks like wrong mapping. But I was sure that everything is ok there.
This two References are wrong.
At first glance do you see why? At that moment I was not able also.
Here Resource has key (RESOURCE_ID).
ResourceRole has composite key (ROLE_ID and RESOURCE_ID).
After long searching and many tries I found why mapping is not correct. Even have my explanation for it.
When we reference ResourceRole we already use RESOURCE_ID field, so when we setup Resource and say “Hey, there is one more RESOURCE_ID“, then NHibernate cannot find out how to update all this correctly.
insert=”false” update=”false” attributes for one of references solves the issue.
In Fluent NHibernate it looks like:
Image that you want to fetch friends from database by the First Name, Last Name and Age, which are properties of your class Friend. With NHibernate you could write query which will look like:
But it is wrong. Since Age is the Nullable type (int?), your call GetFriends(“Andriy”,”Buday”,null) will not get my record even if there is such in database and the column AGE is NULL there. So to request you need this: ageExpression = Expression.IsNull(“Age”); but it will not work for not null age.
For our luck there is AbstractCriterion so we can generalize our Expressions like here: