Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

NHibernate Pitfalls: Querying Unmapped Properties With LINQ

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

Imagine you have a class like this:

   1: public class Person
   2: {
   3:     public virtual String FirstName
   4:     {
   5:         get;
   6:         set;
   7:     }
   8:  
   9:     public virtual String LastName
  10:     {
  11:         get;
  12:         set;
  13:     }
  14:  
  15:     public virtual String FullName
  16:     {
  17:         get
  18:         {
  19:             return(String.Concat(this.FirstName, " ", this.LastName));
  20:         }
  21:     }
  22: }

You might be tempted to issue a query such as this:

   1: var people = session.Query<Person>().Where(x => x.FullName == "Ricardo Peres").ToList();

Unfortunately, this will not work, because FullName is not a mapped property, and will result in an exception being thrown.

You have three options:

  1. Change the FullName property so that it is a formula;
  2. Use LINQ to Objects to do a client-side query instead;
  3. Perform a different query.

Regarding option 1, you can define this property using mapping by code as:

   1: mapper.Class<Person>(p =>
   2: {
   3:     p.Property(x => x.FullName, x =>
   4:     {
   5:         x.Formula("FirstName + ' ' + LastName");
   6:         x.Generated(PropertyGeneration.Always);
   7:         x.Insert(false);
   8:         x.Update(false);
   9:     });
  10: }

Whereas for option 2, you have to have all entities materialized and then apply the condition:

   1: var people = session.Query<Person>().ToList().Where(x => x.FullName == "Ricardo Peres");
Be aware that you are first bringing ALL records from the database and only filtering the in-memory data.

As for option 3, it’s pretty straightforward:

   1: var people = session.Query<Person>().Where(x => x.FirstName == "Ricardo" && x.LastName == "Peres").ToList();

There may be far more complex cases, however, when you will not be able to do this so easily, for example, if you have complex logic in your composite property.

Comments

Juanma said:

I would add a forth option: write it denormalized to the database. This way you have extra space in the db, but logic is not duplicated. With options 2 and 3, you are duplicating the logic to build the fullname both in the entity and the mapping or query.

# July 30, 2012 4:34 PM

Deumber said:

Hi.

You could always do this with using Specification pattern, EF have this same issue, it is know that when you create a lambdas to generate a query like this the ORM translate this to sql query so use not mapped field it is a problem.

# October 26, 2012 1:34 AM