CODEWITHSUNDEEP
c#nhibernatecriteria
jjnguy
jjnguy

Reputation: 138864

NHibernate - Query multiple tables with one query

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?

Upvotes: 1

Views: 1759

Answers (1)

Phill
Phill

Reputation: 18796

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 */

Upvotes: 2

Related Questions