Archive for the ‘nhibernate’ category

Get your entity rather than the proxy

February 2, 2010

A small problem we’ve encountered today- this is truly basics, but you can never know, this might serve me or someone at some point. One of my colleagues was trying to figure out why was his UserRepository.Get(id) method was returning him a proxy rather than a proper User. He was trying to determine what sort of user is it after getting it to know what should he display, and since a proxy was returned, none of the conditions were met (we have other sub-user classes and tables derived from the User class).
It turned out that he was calling during the same lifespan of his session another method that gets another entity which references his User (say Demand.User) and since by default NH gets us a proxy, the session returned to him what it had in his cache.
What we did was quite simply add the method DemandRepository.GetDemandWithUser(id), eagerly fetching the User property, which looks in NHibernate.Linq (old version- 1.1 as far as I remember) like that:

 return Session<Demand>().Expand("User").SingleOrDefault(d=>d.id == demandId);

Substring and an In clause with NH Projections

January 10, 2010

This is something I’ve stumbled upon and couldn’t really find where did it all went wrong.

I have a Where clause containing a substring and an In clause. In T-Sql it looks like this:

 SELECT * FROM Employee WHERE SUBSTRING(Code, 1, 2) In ('one', 'two', 'three', 'four', 'five');

I’ve been trying to do this in NHibernate using ICriteria & Projections and came up with this code:

ICriteria criteria = session.CreateCriteria(typeof (Employee));

IProjection postCodeSubstring = Projections.SqlFunction
 ("substring", NHibernateUtil.String, Projections.Property
 ("FirstName"),
 Projections.Constant(1), Projections.Constant(2));

List<string> deptsIds = new List<string> {"one", "two", "three",
 "four", "five"};
 criteria.Add(Restrictions.In(postCodeSubstring, deptsIds));

IList<Employee> employees = criteria.List<Employee>();

However, this code generates some faulty t-sql:


SELECT this_.Id as Id0_0_, this_.FirstName as FirstName0_0_, this_.LastName as LastName0_0_
FROM [Employee] this_ WHERE substring(this_.FirstName, @p0, @p1)
in (@p2, @p3, @p4, @p5, @p6);
@p0 = 1, @p1 = 2, @p2 = 1, @p3 = 2, @p4 = 'one', @p5 = 'two', @p6 = 'three', @p7 = 'four', @p8 = 'five'

As you can see, the first two parameters of the In clause are the ints 1 and 2, instead of the varchars ‘one’ and ‘two’. On top of that 2 supplementary parameters were generated (@p7 = ‘four’, @p8 = ‘five’).

For the time being, the only way I found to resolve this issue is a workaround- mapping another property containing a substring formula. My FluentNH map contains these two lines now:

Map(x => x.FirstName);

Map(x => x.SubName).Formula("substring(FirstName, 1, 2)");

And the criteria would be:

ICriteria criteria = session.CreateCriteria(typeof (Employee));</pre>
List<string> deptsIds = new List<string> {"one", "two", "three", "four", "five"};
criteria.Add(Restrictions.In("SubName", deptsIds));

IList<Employee> employees = criteria.List<Employee>();

If I figure out what went wrong there with the projections, I’ll let the world know :).

Filtering queries with AOP?

December 25, 2009

Our client asked us to enable the filtering of certain queries by the profile of the current logged user. For example, a user from a certain region could see information concerning his region only. The first thing that came into my mind was setting the context with AOP; however, using NHibernate, I was kindly directed by Tuna Toksöz (thanks again!) to this truly elegant solution. However, this work only when the context is “prefixed” and known in advanced. When in need to enable a more dynamic approach, where the (admin) users could configure themselves what filters should be enabled on which users, have a look at Ayende’s way of doing this.

One thing you should bear in mind- if your context is an enum, they are mapped by default to a string/nvarchar column by NH today, so that adding the filter parameter type would be:

filterParametersType.Add("current", NHibernateUtil.String);

And the filter enabling in your Interceptor would be

public override void SetSession(ISession session)
 {
 session.EnableFilter("contextFilter").SetParameter("current", Context.Current.ToString());
 }

Getting return values from an SP in NHibernate

December 20, 2009

In order to get a return value from a stored procedure in NHibernate, you’ll need to, depending on the returned value, either (for singular column returned values):

  1. Create your sp in your db.
  2. Call it using the Session.CreateSQLQuery(“exec myStoredProc”)

Or (for ‘complex’, multiple column returned values):

  1. Create your sp
  2. map it.
  3. Call it.

First and foremost, remember always to use SELECT in your SP rather than RETURN.
Now, for the first case, this would look like this:

 CREATE PROCEDURE [dbo].[GetIt]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
SELECT 1342
END

This would be then called like this:

int query = session.CreateSQLQuery("exec GetIt").List<int>()[0];

As for the second case, this would look like this:

 ALTER PROCEDURE [dbo].[GetIt]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
SELECT 1342 as Id, 'MyName' as Name
END

The mapping would then be:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Examples.FirstProject"  namespace="Examples.FirstProject" >

<sql-query name="GetIt">
<return alias="result">
<return-property column="It" name="Id" />
<return-property column="Name" name="Name" />
</return>
exec GetIt
</sql-query>

</hibernate-mapping>

And finally, this would be executed like this:

IList<User> users = session.GetNamedQuery("GetIt").List<User>();

Beware that the return class referenced in the sql-query mapping should be mapped itself on its own, separately!