NHibernate

NHibernate: Projections.RowCount()

February 19, 2010 NHibernate, QuickTip No comments

 What do you see to be wrong with this code?

        public bool RuleExists(string ruleName, string documentName)
        {
            var result = Session.CreateCriteria(typeof(Rule)“r”)
                //some aliaces 
             
                .SetProjection(Projections.RowCount())

                .Add(Restrictions.Eq(“r.Name”, ruleName))
                //other restrictions
                .List();

            return result.Count > 0;
        }


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.

SELECT count(* ) AS y0_
FROM   TBL_RULE this_
       INNER JOIN — some joins here
     
WHERE  this_.NAME = ‘absolultely_incorrect_rule_name’ /* @p0 */
       AND — other conditions 

Result of this query is number of rows like on picture below:

So, verification return result.Count > 0is absolutely incorrect.
I’ve chagned it to return (int)result[0] > 0;

Moral:
Do not be lazy to write Unit Tests both for success and failure sceneries.


No comments


Few NHibernate hints on Query Criteria

February 15, 2010 NHibernate, QuickTip No comments

ORDER BY


To add “order by” to your criteria you need this statement.
.AddOrder(Order.Asc(“Priority”))

TOP


To add “top 10” to your criteria you need this statement
.SetMaxResults(10)

Criteria

So code about which I’m talking could look like:

        public IList<Customer> FetchTopPriorityCustomers()
        {
            var result = Session.CreateCriteria(typeof(Customer))
                .SetFetchMode(“CustomerStatusType”FetchMode.Eager)
                .Add(Expression.Eq(“CustomerStatusType.CustomerStatusTypeID”, (Int32)CustomerStatusType.Created))
                .SetResultTransformer(new DistinctRootEntityResultTransformer())
                .AddOrder(Order.Asc(“Priority”))
                .SetMaxResults(10)
                .List<Customer>();
            return result;
        }

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.


No comments


Save entity with assigned ID, not generated by NHibernate.

February 3, 2010 NHibernate No comments

I want generate INSERT which include my Primary Key (not generated) by NHibernate. How to map Id column for this?

I have table CUSTOMER where CUSTOMER_ID is primary key.

Table

  

Code:

CREATE TABLE [CUSTOMER](
    [CUSTOMER_ID] [int] NOT NULL,
    [START_DATE] [varchar(30)] NOT NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED ( [CUSTOMER_ID] ASC …..

Mapping

Code:

public class CustomerMap : ClassMap<Customer> {
    public CustomerMap()
    {
        WithTable(“CUSTOMER”);
        Id(x => x.CustomerID, “CUSTOMER_ID”);
        Map(x => x.Name, “NAME”);
    }
}

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:

  

Code:

INSERT INTO [CUSTOMER] ([CUSTOMER_ID] ,[NAME]) VALUES (777,‘Andriy Buday’)

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.

Solution

So I need to manually setup my ID property with adding GeneratedBy.Assigned();
Like here:

Code:

Id(x => x.CustomerID, “CUSTOMER_ID”)
      .GeneratedBy.Assigned();


No comments


Session.Merge(object obj) to RESCUE

January 29, 2010 Errors, NHibernate No comments

Error:

“a different object with the same identifier value was already associated with the session: 14, of entity: Developer.RoadMap.To.Success.Entities.Customer”

I had something like this in the SaveCustomer method:

if ( customer.CustomerID > 0 )
{
    Session.Update( customer);
}
else
{
    Session.SaveOrUpdate( customer);
}
transaction.Commit();

And this don’t work correctly. Simple change to

if ( customer.CustomerID > 0 )
{
    customer = Session.Merge( customer );
}
else
{
    Session.SaveOrUpdate( customer);
}
transaction.Commit();

resolved my issue.


No comments


Quick Verification if row exists in database with Nhibernate

December 23, 2009 NHibernate No comments

Next SQL, which verifies if customer row exists in database:

SELECT customer0_.CUSTOMER_ID AS x0_0_
FROM   T_CUSTOMER customer0_
WHERE  (customer0_.CUSTOMER_ID = 123456)

Will be generated with next NHibernate Query:

public bool CustomerExistsInDatabase(Int32 customerId)
{
    var result = Session.CreateQuery(string.Format(“select c.CustomerID from Customer c where c.CustomerID = {0}”, customerId)).UniqueResult<Int32?>();

    return result.HasValue;
}

Please note, that you could use not only primary key, but any other property in your where condition.


No comments


NHibernate: The column name COLUMN_ID is specified more than once in the SET clause

December 9, 2009 NHibernate 4 comments

Sometime ago I faced with NHibernate issue and spent much time on figuring out how to resolve it. Issue was with saving complicated entity.
Error:

The column name ‘RESOURCE_ID’ is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name ‘RESOURCE_ID’ may appear twice in the view definition.

With NHibernate Profiler I found that this fails on the next SQL: 

UPDATE SOME_TABLE SET SOME_COLUMN1 = ?, SOME_COLUMN2 = ?, RESOURCE_ID = ?, SOMETHING_ELSE = ?, RESOURCE_ID = ? WHERE SOME_TABLE_ID = ?

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.

References(x => x.Resource)
        .Access.AsCamelCaseField(Prefix.Underscore)
        .WithForeignKey(“RESOURCE_ID”).TheColumnNameIs(“RESOURCE_ID”)
        .FetchType.Join();

References(x => x.ResourceRole)
        .Access.AsCamelCaseField(Prefix.Underscore)
        .WithColumns(“RESOURCE_ROLE_ID”, “RESOURCE_ID”)
        .FetchType.Join();

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.

Solution:
insert=”false” update=”false” attributes for one of references solves the issue.

In Fluent NHibernate it looks like:

.SetAttribute(“update”, “false”);


4 comments


NHibernate Criteria for Nullable property

December 7, 2009 NHibernate 2 comments

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:

public IList<Friend> GetFriends(string firstName, string lastName, int? age)
{
    IList<Friend> friends = Session.CreateCriteria(typeof(Friend))
        .Add(Expression.Eq(“FirstName”, firstName))
        .Add(Expression.Eq(“LastName”, lastName))
        .Add(Expression.Eq(“Age”, age))
        .List<Friend>();

    return friends;
}

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:

public IList<Friend> GetFriends(string firstName, string lastName, int? age)
{
    AbstractCriterion ageExpression;
    if (age == null)
    {
        ageExpression = Expression.IsNull(“Age”);
    }
    else
    {
        ageExpression = Expression.Eq(“Age”, age);
    }

    IList<Friend> friends = Session.CreateCriteria(typeof(Friend))
        .Add(Expression.Eq(“FirstName”, firstName))
        .Add(Expression.Eq(“LastName”, lastName))
        .Add(ageExpression)
        .List<Friend>();

    return friends;
}


2 comments