NHibernate - Query multiple tables with one query

Question

I'm using NHibernate 2, and I'd like to be able to do the following:

I have many DAO types. They are all subclasses of BaseDAO, but they are not related in the NHibernate mappings at all. (These subclasses dot not all have common properties.)

I want to write a method that will search all of my DAO types and return a List<BaseDAO> containing all matches.

The method signature should look something like:

public IList<BaseDAO> GlobalSearch(string searchTerm, int startIdx, int maxRows);

The query needs to check searchTerm against all string properties of the domain types. We are currently doing this for one type at a time using a method that builds a Disjunction to search on all properties for a given type.

private Disjunction BuildDisjunction(string searchTerm, Type type)

I'd like to combine all of the disjunctions for all of my domain types and create one query that will return a list of the BaseDAO.

Here is what I have so far:

public IList<DomainBase> GlobalSearch(string searchTerm, 
                                      int startIndex, int maxRows)
{
    ICriteria crit = GetCriteria<BaseDAO>();
    foreach (Type t in GetAllDomainTypes())
    {
        Disjunction disj = BuildDisjunction(searchTerm, t);
        // somehow use this disjunction
    }
    crit
        .AddOrder(Order.Asc("DispName"))
        .SetFirstResult(startIdx)
        .SetMaxResults(maxRows);
    return crit.List<BaseDAO>(); ;
}

Is this possible? How can I modify/complete the above method to achieve the functionality I need?

I don't think this is going to be possible. Think about how you would do this in SQL, if it's impossible to do in one SQL query, then it's going to be impossible to do in NHibernate. — Vadim
– Vadim, Commented Apr 28, 2011 at 17:09
@Vadim, that is kinda what I was afraid of. Unfortunately my knowledge of SQL is too little to know if this is possible or not. — jjnguy
– jjnguy, Commented Apr 28, 2011 at 17:37
Are you searching on a property of the base class? If so then it would be possible to generate each query into a MultiCritera, execute it and populate all the results back into a list. — Phill
– Phill, Commented Apr 29, 2011 at 2:47
@Phill,could you show me how to do that? But, no. I'm searching on all public string properties of all subclasses. — jjnguy
– jjnguy, Commented Apr 29, 2011 at 5:32
Would probably need to do some reflection in order to do it, I get home from work in a few hours, I'll see if i can write up an example, see if it is in-fact possible, I'm 98% sure it is. Will let you know. — Phill
– Phill, Commented Apr 29, 2011 at 5:34

jjnguy · Accepted Answer · 2011-04-30 00:38:56Z

2

This method will only work with MSSQL Server.

Ok I've written the following, I hope it's what you're after, it can be refactored but it will get you started.

Given the following test classes:

public class BaseClass
{
    public virtual int Id { get; set; }   
}

public class TestClassOne : BaseClass
{
    public virtual string Name { get; set; }
}

public class TestClassTwo : BaseClass
{
    public virtual string Value { get; set; }
    public virtual string Hobby { get; set; }
}

public class TestClassThree : BaseClass
{
    public virtual string Month { get; set; }
    public virtual int Day { get; set; }
}

public class TestClassFour : BaseClass
{
    public virtual string Title { get; set; }
    public virtual string Content { get; set; }
}

Can query the all those classes by reflecting them, then reflecting the properties which are a type of string.

        var session = new SessionFactoryManager().CreateSessionFactory().OpenSession();
        var criteria = session.CreateMultiCriteria();

        //Find classes that inherit from BaseClass
        var classses = Assembly.GetExecutingAssembly().GetTypes().Where(x => x.BaseType == typeof(BaseClass));
        var searchValue = "Test";

        foreach (var classs in classses)
        {
            //Find all the properties that are typeof string
            var properties = classs.GetProperties()
                                   .Where(x => x.PropertyType == typeof(string));

            var query = DetachedCriteria.For(classs);

            foreach (var memberInfo in properties)
            {
                query.Add(Restrictions.InsensitiveLike(memberInfo.Name, searchValue, MatchMode.Anywhere));
            }

            criteria.Add(query);
        }

        var results = criteria.List()
                              .Cast<ArrayList>()
                              .ToList()
                              .SelectMany(x => x.ToArray())
                              .Cast<BaseClass>()
                              .ToList();

        foreach (var result in results)
        {
            Console.WriteLine(result.Id);
        }

Will generate a single query batch like so:

SELECT this_.Id   as Id1_0_,
       this_.Name as Name1_0_
FROM   TestClassOne this_
WHERE  lower(this_.Name) like '%test%' /* @p0 */


SELECT this_.Id    as Id3_0_,
       this_.Value as Value3_0_,
       this_.Hobby as Hobby3_0_
FROM   TestClassTwo this_
WHERE  lower(this_.Value) like '%test%' /* @p1 */
       and lower(this_.Hobby) like '%test%' /* @p2 */


SELECT this_.Id    as Id2_0_,
       this_.Month as Month2_0_,
       this_.Day   as Day2_0_
FROM   TestClassThree this_
WHERE  lower(this_.Month) like '%test%' /* @p3 */


SELECT this_.Id      as Id0_0_,
       this_.Title   as Title0_0_,
       this_.Content as Content0_0_
FROM   TestClassFour this_
WHERE  lower(this_.Title) like '%test%' /* @p4 */
       and lower(this_.Content) like '%test%' /* @p5 */

edited Apr 30, 2011 at 0:38

jjnguy

139k54 gold badges298 silver badges328 bronze badges

answered Apr 29, 2011 at 12:38

Phill

19k7 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jjnguy Over a year ago

This looks promising. I will take a look when I get a chance. Thanks!

jjnguy Over a year ago

Thanks for the help, and this looks like what I want. However I'm getting this error - The driver NHibernate.Driver.OracleClientDriver does not support multiple queries.

Phill Over a year ago

@jjnguy - Oh bugger, this is a feature of SQLServer, Oracle doesn't support query batching :( then the answer to your original question is it isn't possible :(

Collectives™ on Stack Overflow

NHibernate - Query multiple tables with one query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related