Hi everyone!
I've turned my head around for the last few days trying to get a
searchfield up and running using NHibernate. But the thing is that it
doesn't work very well.
First things first, I can't change the data model, due to other
applications and components that relies on it.
This project is a kind of Asset management system, which has
information about computers in stock, and what status they have, like
"in production", "completed", and "sorting".
The main table has about 30 columns, which has foreign keys to other
tables. like MainTable.CategoryID -> CategoryTable.ID. This applies to
userid, supplierid, etc. This isnt really my problem, the problem is
when i try to fetch data based on a keyword. Fetching the data by
using a supplierid works like it should, and the same when i search by
a date or between two dates.
Lets say the user inputs "Seagate" to see how many harddrives
manufactured by Seagate a supplier has in shipped to CompanyX between
2000-01-01 and 2011-01-01.
The search results returns none, even though I know that there are
many items that would match that criteria.
Heres a breakdown of the code i've been trying out. It works when i
only have one or two aliases, but when I add more, the results aren't
as expected.
if (Request.Form["SearchText"] != null &&
Request.Form["SearchText"].Trim().Length > 0)
{
String TextToSearchFor =
this.ValidateUserInput(Request.Form["SearchText"]); // sanitize string
searchCriteria.CreateAlias("Category", "c",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Manufacturer", "m",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Model", "mo",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Supplier", "sup",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Licence", "lic",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Memory", "me",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("MemorySize", "mems",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Screen", "s",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("HDD1Size", "hd1s",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("HDD2Size", "hd2s",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("Processortype", "p",
NHibernate.SqlCommand.JoinType.InnerJoin)
.CreateAlias("ProcessorCPUSpeed",
"pcpu", NHibernate.SqlCommand.JoinType.InnerJoin)
//
add search text disjunction.
.Add(Restrictions.Disjunction()
.Add(Restrictions.Like("c.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("m.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("mo.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("sup.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("lic.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("me.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("mems.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("s.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("hd1s.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("hd2s.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("p.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("pcpu.Name",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("Batch",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("EquipmentNumber",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("SerialNumber",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("Other",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("TrackingNumber",
TextToSearchFor, MatchMode.Anywhere))
.Add(Restrictions.Like("OrderNumber",
TextToSearchFor, MatchMode.Anywhere))
);
}
The last 6 Disjunction().Add rows data is present in the maintable.
Each of the Aliases has been mapped, and works well in other aspects
of the application. Am I doing something terribly wrong that I just
don't see?
In SQL I would have used something like this (psuedo):
SELECT <fields to search in> FROM <perform joins and unions and such
here> WHERE (c.Name LIKE %TextToSearchFor% OR m.Name LIKE
%TextToSearchFor% OR etc..)
If I just fetch data based on a supplierID, like this:
IList<main_table> mainTableList = null;
using (var session =
Library.SessionFactory.CreateSessionFactory().OpenSession())
{
ICriteria searchCriteria = session.CreateCriteria<main_table>();
mainTableList = searchCriteria.Add(Restrictions.Eq("SupplierID",
1000)).List<main_table>();
}
Then I would do some other operations on the list before passing it to
the View. This works out fine, and I get Category, Manufacturer, etc
objects in each main_table object.
So my question is: Can someone either propose a solution or atleast
point me in the right direction for the search problem i'm having? At
this point I can't see any other solution than going back to using
regular SQL or doing heavy list looping and string.Contains(). :-/
Any help would be greatly appreciated!
(PS: Sorry for any typos/misspelling and such, English isn't my native
language).
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
