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
3: public virtual String FirstName
9: public virtual String LastName
15: public virtual String FullName
19: return(String.Concat(this.FirstName, " ", this.LastName));
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:
- Change the FullName property so that it is a formula;
- Use LINQ to Objects to do a client-side query instead;
- Perform a different query.
Regarding option 1, you can define this property using mapping by code as:
1: mapper.Class<Person>(p =>
3: p.Property(x => x.FullName, x =>
5: x.Formula("FirstName + ' ' + LastName");
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.