General strategy for complex multi-stage searches

Question

I have an application with the ability to search for a specific object based on several different criteria (somewhere around 20 different methods in general). I want to be able to combine the results of several searches to create a single result set.

For instance:

results = (entities from search 1 AND entities from search 2) OR (entities from search 3)

Suppose that the search is quite complex in nature, so combining them into one logical query is impossible (due to complex relationships that need to be requested, etc.).

Suppose also that the number of objects involved (probably) makes any strategy in memory impossible.

My initial thoughts were something like:

1) Perform a search separately, get a list of the corresponding "entity identifiers" from each of them, and then perform a search based on the "root-level" based on this data.

For instance:

select * from entity e
where 
(e.Id in (search 1 id list) AND e.Id in(search 2 id list))
OR e.Id in (search 3 id list)

2) Run an external query that selects the object based on the results returned by my (complex) subqueries.

For instance:

select * from entity e
where (e.Id in (select e1.id from entity e1 where ...) AND e.Id in (select e2.id from entity e2 where...))
OR e.Id in (select e3.id from entity e3 where...)

Obviously, these examples are greatly simplified for illustrative purposes; individual requests will be much more active, and their combination will be arbitrary (I just illustrated an example of a representative here).

I would be very interested to hear suggestions about how others have dealt with this situation. Of course, I am open to any opportunities that I have not explored above.

For reference, this is a .NET application that uses NHibernate ORM and is supported by a SQL Server 2008 R2 database.

hql, native sql , ICriteria Linq , , .

Answer 1

, . , .

, TotalNumberOfRowsToSearch * Percent_Not_Matched/RunTimeInSeconds , . .

, .

, .

, NOT IN .

, .

, . , , 55 , 99% , , , 1 1% , , .

0 .

Answer 2

Linq , , .

- :

List<Expression<Func<WorkItem, bool>>> whereExpressions = new List<Expression<Func<WorkItem, bool>>>();
if (!string.IsNullOrEmpty(searchMask))
            {
                whereExpressions.Add(
                                        x =>
                                        (x.Name.ToLower().IndexOf(searchMask.ToLower()) > -1 ||
                                         x.Id.ToString().IndexOf(searchMask) > -1 ||
                                         (x.Description != null &&
                                          x.Description.ToLower().IndexOf(searchMask.ToLower()) > -1)));
            }

whereExpressions.Add(x => (x.Status == status));   

:

IQueryable<WorkItem> result = Session.Linq<WorkItem>();
foreach (Expression<Func<WorkItem, bool>> whereExpression in whereExpressions)
            {
                result = result.Where(whereExpression);
            }

:

IQueryable<WorkItem> items;
            if (ascOrDesc == "asc")
            {
                items = result.OrderBy(DecideSelector(indexer)).Skip(startPoint - 1).Take(numOfrows);
            }
            else
            {
                items = result.OrderByDescending(DecideSelector(indexer)).Skip(startPoint - 1).Take(numOfrows);
            }

DecideSelector :

private Expression<Func<WorkItem, object>> DecideSelector(string fieldCode)
        {
            switch (fieldCode)
            {
                case "Deadline":
                    return item => item.Deadline;
                case "name":
                    return item => item.Name;
                case "WiStatus":
                    return item => item.Status;
                case "WiAssignTo":
                    return item => item.AssignedUser;
                default:
                    return item => item.Id;
            }
        }

Answer 3

ICriteria, . . , .

. , , . ( ), .