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.

                             

2 Comments

  • 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.

  • 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.

Comments have been disabled for this content.