I am using the IQuery interface to retreive data from a table that has a composite key. When filtering using a combination of an IN-statement and two date comparisons the query returns an empty result.
I know for sure that matching rows exist in the table, and have verified this by removing either the IN-statement or the date comparisons from the filter and programatically searching the result for items that match the excluded condition (see code bolow).
NHibernate version: 1.2.0.4000
Name and version of the database you are using: MySQL 5.0.45
.Net version: 2.0.50727
Mapping documents:
Code:
<class name="MyStatistics" table="mystatistics">
<composite-id unsaved-value="any">
<key-property name="ItemID" />
<key-property name="PeriodStart" />
<key-property name="PeriodStop" />
</composite-id>
<property name="A"/>
<property name="B"/>
<property name="C"/>
</class>
The following code gives an empty list
Code:
long[] itemIds;
DateTime periodStart;
DateTime periodStop;
ISession sess;
...
string hql = "FROM MyStatistics WHERE ItemID IN (:itemIds) AND PeriodStart = :periodStart AND PeriodStop = :periodStop";
IQuery qry = sess.CreateQuery(hql);
qry.SetParameterList("itemIds", itemIds);
qry.SetDateTime("periodStart", periodStart);
qry.SetDateTime("periodStop", periodStop);
IList<MyStatistics> list = qry.List<MyStatistics>();
The following code finds several matching items
Code:
...
string hql = "FROM MyStatistics WHERE ItemID IN (:itemIds)";
IQuery qry = sess.CreateQuery(hql);
qry.SetParameterList("itemIds", itemIds);
IList<MyStatistics> list = qry.List<MyStatistics>();
int matches = 0;
foreach (MyStatistics stat in list)
{
if (stat.PeriodStart.Equals(periodStart) && stat.PeriodStop.Equals(periodStop))
matches++;
}
The following code also finds matching items
Code:
...
string hql = "FROM MyStatistics WHERE PeriodStart = :periodStart AND PeriodStop = :periodStop";
IQuery qry = sess.CreateQuery(hql);
qry.SetDateTime("periodStart", periodStart);
qry.SetDateTime("periodStop", periodStop);
IList<MyStatistics> list = qry.List<MyStatistics>();
int matches = 0;
foreach (MyStatistics stat in list)
{
if (stat.ItemID == itemIds[0])
matches++;
}
Any suggestions on why I cannot get the expected result in the first example?
Worth mentioning is that the query does work as expected in my local test environment (also MySQL) but not in production.
Login
FAQ
Search