public IList<User> GetUsersInRole(Role role)
{
IDetachedQuery dq = new DetachedQuery("from User u where :role in elements(u.Roles) and u.Site = :site and u.IsLogicallyDeleted = 0");
dq.SetEntity("role", role);
dq.SetEntity("site", role.Site);
using (ISession session = Session){
IQuery query = dq.GetExecutableQuery(session);
return query.List<User>();
}
}
public void ExecutableQuery()
{
// Simply fetch
IDetachedQuery dq = new DetachedQuery("from Foo");
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
IList l = q.List();
Assert.AreEqual(totalFoo, l.Count);
}
// With Typed Parameters
dq = new DetachedQuery("from Foo f where f.Name=:pn and f.Description=:pd");
dq.SetString("pn", "N2").SetString("pd", "D2");
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
IList<Foo> l = q.List<Foo>();
Assert.AreEqual(1, l.Count);
Assert.AreEqual("N2", l[0].Name);
Assert.AreEqual("D2", l[0].Description);
}
// With UnTyped Parameters
dq = new DetachedQuery("from Foo f where f.Name=:pn and f.Description=:pd");
dq.SetParameter("pn", "N2").SetParameter("pd", "D2");
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
IList<Foo> l = q.List<Foo>();
Assert.AreEqual(1, l.Count);
Assert.AreEqual("N2", l[0].Name);
Assert.AreEqual("D2", l[0].Description);
}
// With UnTyped Parameter List
dq = new DetachedQuery("from Foo f where f.IntValue in (:pn)");
dq.SetParameterList("pn", new int[] { 2, 3 });
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
IList<Foo> l = q.List<Foo>();
Assert.That(l.Count, Is.EqualTo(2));
Assert.That(l.Contains(new Foo("N2", "D2")), Is.True);
Assert.That(l.Contains(new Foo("N3", "D3")), Is.True);
}
// Pagination
dq = new DetachedQuery("from Foo f order by f.IntValue");
dq.SetFirstResult(0).SetMaxResults(2);
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
IList<Foo> l = q.List<Foo>();
Assert.AreEqual(2, l.Count);
Assert.AreEqual("N0", l[0].Name);
Assert.AreEqual("N1", l[1].Name);
}
dq.SetFirstResult(2).SetMaxResults(1);
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
IList<Foo> l = q.List<Foo>();
Assert.AreEqual(1, l.Count);
Assert.AreEqual("N2", l[0].Name);
}
}
public void PerformanceDiffSimplyQuery()
{
DateTime sDQStart = DateTime.Now;
DetachedQuery dq = new DetachedQuery("from Foo f where f.Name=:pn and f.Description=:pd");
dq.SetString("pn", "N2").SetString("pd", "D2");
using (ISession s = OpenSession())
{
IQuery q = dq.GetExecutableQuery(s);
}
DateTime sDQStop = DateTime.Now;
DateTime sQStart = DateTime.Now;
using (ISession s = OpenSession())
{
IQuery q = s.CreateQuery("from Foo f where f.Name=:pn and f.Description=:pd").SetString("pn", "N2").SetString("pd", "D2");
}
DateTime sQStop = DateTime.Now;
Console.WriteLine("DetachedQueryCycle={0} QueryCycl={1} Diff={2}", sDQStop - sDQStart, sQStop - sQStart,
(sDQStop - sDQStart) - (sQStop - sQStart));
}
/// <summary>
///
/// </summary>
/// <param name="searchParameter"></param>
/// <returns></returns>
public IList ExecuteDetachedQuery(string searchParameter)
{
using (ITransaction transaction = Session.BeginTransaction())
{
try
{
string hqlQuery = "select Builder, Model, Price, Id" +
" from Inventory " +
" where Model like :search " +
" order by Builder";
IDetachedQuery detachedQuery = new DetachedQuery(hqlQuery)
.SetString("search", searchParameter);
IQuery executableQuery = detachedQuery.GetExecutableQuery(Session);
return executableQuery.List();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}