Is it possible to query a custom type with multiple properties mapped to a single column?

Question

I have the following domain model:

public class Name { private readonly string fullName; public Name(string fullName) { this.fullName = fullName } public string FullName { get { return fullName; } } public string FirstName { get { /* ... */ } } public string MiddleNames { get { /* ... */ } } public string LastName { get { /* ... */ } } public static implicit operator Name(string name) { /* ... */ } } public class Person { public Name BirthName { get; set; } public Name Pseudonym { get; set; } } 

I implemented IUserType , so I can map each name to one database column with a full name.

Queries like this job:

 var people = session.QueryOver<Person>() .Where(p => p.Name == "John Doe") .List(); 

But I can’t query like this:

 var people = session.QueryOver<Person>() .Where(p => p.Name.LastName == "Doe") .List(); 

Can I get NHibernate to work with this?

Answer 1

I am not a nhibernate user, but, analyzing all the information that we have about the script here, I have a strong feeling that you cannot answer.

You map the value contained in this class to a single value in db.

To resolve the query on its fragments, he would have to understand how all these pieces of data in the Name class are associated with the full value.

That would mean understanding what C # custom code does.

---

update 1:

The above answer is that all this is automatically generated for you. And using the exact request you mentioned there.

You can definitely solve the problem suggested by @cs. That is, by defining a user-defined function that does what you want and matching it in nHibernate.

At least this will allow you to do something like:

 session.QueryOver<Person>() .Where(p => session.PersonLastName(p) == "Doe") .List(); 

Another way would be to define an extension method that will translate to what you want by implementing it, as in this article: http://fabiomaulo.blogspot.com/2010/07/nhibernate-linq-provider-extension.html

Usage will then look like this:

  session.QueryOver<Person>() .Where(p=> p.Name.LastNameExt() == "Doe")// Ext just to avoid name collision .List(); 

Finally, I'm not sure if it is possible to map helper properties for user-defined functions for each, so that your request can remain unchanged, for example:

  session.QueryOver<Person>() .Where(p => p.Name.LastName == "Doe") .List(); 

Not in all cases you go from data concatenation to data analysis. You are the only one who knows whether it really buys something, and also saves the properties separately.

Answer 2

I believe this is possible, but will require some work on the SQL side of things. The first thing I will probably try to do is create a UDF that will split the name for you and make the necessary comparison. You can then extend NHibernate to map to this UDF, and you should be able to call this function from your query, whether it be SQL, HQL, or ICriteria.

Answer 3

You can only request the full name due to the fact that this is just one database field.

If you map it differently so that FirstName and Lastname are separate columns, you should be able to query for each of them, but then you may have difficulty in querying the FullName property.

Answer 4

What you can do is implement your own repository shell for these objects and implement methods such as GetUsersByLastName () that can use HQL to execute these types of "partial value search" queries.

Something like the following should allow you to search for any partial value, provided that you go to the correct search string:

 var hql = "select p from People p where lower(p.BirthName) like :searchText"; 

Edit:

Removed LastName from the request. How this works, you must use the anchored search text to start or end the BirthName field. For example, if you want to find all the people with the last name Smith, you can do:

 var hql = "select p from People p where lower(p.BirthName) like '% smith'"; 

And in the case of people named Joe:

 var hql = "select p from People p where lower(p.BirthName) like 'joe %'"; 

Answer 5

You need to create calculated columns. They will be read-only, but you can request them. See here .