C# Hql.Animal类代码示例

		public void AggregateCount()
		{
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("a1", 20);
				Animal a2 = new Animal("a2", 10);
				s.Save(a1);
				s.Save(a2);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				// Count in select
				object result = s.CreateQuery("select count(distinct a.id) from Animal a").UniqueResult();
				Assert.AreEqual(typeof(long), result.GetType());
				Assert.AreEqual(2, result);

				result = s.CreateQuery("select count(*) from Animal").UniqueResult();
				Assert.AreEqual(typeof(long), result.GetType());
				Assert.AreEqual(2, result);

				// Count in where
                if (TestDialect.SupportsHavingWithoutGroupBy)
                {
                    result = s.CreateQuery("select count(a.id) from Animal a having count(a.id)>1").UniqueResult();
                    Assert.AreEqual(typeof (long), result.GetType());
                    Assert.AreEqual(2, result);
                }
			}
		}

		public void Lower()
		{
			IgnoreIfNotSupported("lower");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("ABCDEF", 1f);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql = "select lower(an.Description) from Animal an";
				IList lresult = s.CreateQuery(hql).List();
				Assert.AreEqual("abcdef", lresult[0]);

				hql = "from Animal an where lower(an.Description)='abcdef'";
				Animal result = (Animal)s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("ABCDEF", result.Description);

				//test only parser
				hql = "select lower(an.Description) from Animal an group by lower(an.Description) having lower(an.Description)='abcdef'";
				lresult = s.CreateQuery(hql).List();
			}
		}

		public void Cast()
		{
			const double magicResult = 7 + 123 - 5*1.3d;

			IgnoreIfNotSupported("cast");
			// The cast is used to test various cases of a function render
			// Cast was selected because represent a special case for:
			// 1) Has more then 1 argument
			// 2) The argument separator is "as" (for the other function is ',' or ' ')
			// 3) The ReturnType is not fixed (depend on a column type)
			// 4) The 2th argument is parsed by NH and traslated for a specific Dialect (can't be interpreted directly by RDBMS)
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 1.3f);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql;
				IList l;
				Animal result;
				// Rendered in SELECT using a property 
				hql = "select cast(a.BodyWeight as Double) from Animal a";
				l = s.CreateQuery(hql).List();
				Assert.AreEqual(1, l.Count);
				Assert.That(l[0], Is.TypeOf(typeof (double)));

				// Rendered in SELECT using a property in an operation with costant 
				hql = "select cast(7+123-5*a.BodyWeight as Double) from Animal a";
				l = s.CreateQuery(hql).List();
				Assert.AreEqual(1, l.Count);
				Assert.AreEqual(magicResult, (double)l[0], 0.00001);

				// Rendered in SELECT using a property and nested functions
				if (!(Dialect is Oracle8iDialect))
				{
					hql = "select cast(cast(a.BodyWeight as string) as Double) from Animal a";
					l = s.CreateQuery(hql).List();
					Assert.AreEqual(1, l.Count);
					Assert.That(l[0], Is.TypeOf(typeof(double)));
				}

				// TODO: Rendered in SELECT using string costant assigned with critic chars (separators)

				// Rendered in WHERE using a property 
				if (!(Dialect is Oracle8iDialect))
				{
					hql = "from Animal a where cast(a.BodyWeight as string) like '1.%'";
					result = (Animal) s.CreateQuery(hql).UniqueResult();
					Assert.AreEqual("abcdef", result.Description);
				}

				// Rendered in WHERE using a property in an operation with costants
				hql = "from Animal a where cast(7+123-2*a.BodyWeight as Double)>0";
				result = (Animal)s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				// Rendered in WHERE using a property and named param
				hql = "from Animal a where cast(:aParam+a.BodyWeight as Double)>0";
				result = (Animal)s.CreateQuery(hql)
					.SetDouble("aParam", 2D)
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				// Rendered in WHERE using a property and nested functions
				if (!(Dialect is Oracle8iDialect))
				{
					hql = "from Animal a where cast(cast(cast(a.BodyWeight as string) as double) as int) = 1";
					result = (Animal) s.CreateQuery(hql).UniqueResult();
					Assert.AreEqual("abcdef", result.Description);
				}

				// Rendered in GROUP BY using a property 
				hql = "select cast(a.BodyWeight as Double) from Animal a group by cast(a.BodyWeight as Double)";
				l = s.CreateQuery(hql).List();
				Assert.AreEqual(1, l.Count);
				Assert.That(l[0], Is.TypeOf(typeof(double)));

				// Rendered in GROUP BY using a property in an operation with costant 
				hql = "select cast(7+123-5*a.BodyWeight as Double) from Animal a group by cast(7+123-5*a.BodyWeight as Double)";
				l = s.CreateQuery(hql).List();
				Assert.AreEqual(1, l.Count);
				Assert.AreEqual(magicResult, (double)l[0], 0.00001);

				// Rendered in GROUP BY using a property and nested functions
				if (!(Dialect is Oracle8iDialect))
				{
					hql =
						"select cast(cast(a.BodyWeight as string) as Double) from Animal a group by cast(cast(a.BodyWeight as string) as Double)";
					l = s.CreateQuery(hql).List();
					Assert.AreEqual(1, l.Count);
					Assert.That(l[0], Is.TypeOf(typeof(double)));
				}

				// Rendered in HAVING using a property 
				hql = "select cast(a.BodyWeight as Double) from Animal a group by cast(a.BodyWeight as Double) having cast(a.BodyWeight as Double)>0";
				l = s.CreateQuery(hql).List();
				Assert.AreEqual(1, l.Count);
				Assert.That(l[0], Is.TypeOf(typeof(double)));
//.........这里部分代码省略.........

		public void AggregateAvg()
		{
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("a1", 20);
				Animal a2 = new Animal("a2", 10);
				s.Save(a1);
				s.Save(a2);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				// In Select
				object result = s.CreateQuery("select avg(a.BodyWeight) from Animal a").UniqueResult();
				Assert.AreEqual(typeof(double), result.GetType());
				Assert.AreEqual(15D, result);

				// In where
                if (TestDialect.SupportsHavingWithoutGroupBy)
                {
                    result = s.CreateQuery("select avg(a.BodyWeight) from Animal a having avg(a.BodyWeight)>0").UniqueResult();
                    Assert.AreEqual(typeof(double), result.GetType());
                    Assert.AreEqual(15D, result);
                }
			}
		}

		public void AggregateSum()
		{
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("a1", 20);
				Animal a2 = new Animal("a2", 10);
				s.Save(a1);
				s.Save(a2);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				object result = s.CreateQuery("select sum(a.BodyWeight) from Animal a").UniqueResult();
				Assert.AreEqual(typeof(double), result.GetType());
				Assert.AreEqual(30D, result);

				result = s.CreateQuery("select sum(a.BodyWeight) from Animal a having sum(a.BodyWeight)>0").UniqueResult();
				Assert.AreEqual(typeof(double), result.GetType());
				Assert.AreEqual(30D, result);
			}
		}

		public void Current_TimeStamp()
		{
			IgnoreIfNotSupported("current_timestamp");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 1.3f);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql = "select current_timestamp() from Animal";
				IList result = s.CreateQuery(hql).List();
			}
		}

		public void Concat()
		{
			IgnoreIfNotSupported("concat");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 1f);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql = "select concat(a.Description,'zzz') from Animal a";
				IList lresult = s.CreateQuery(hql).List();
				Assert.AreEqual("abcdefzzz", lresult[0]);

				// MS SQL doesn't support || operator
				if (!(Dialect is MsSql2000Dialect))
				{
					hql = "from Animal a where a.Description = concat('a', concat('b','c'), 'd'||'e')||'f'";
					Animal result = (Animal) s.CreateQuery(hql).UniqueResult();
					Assert.AreEqual("abcdef", result.Description);
				}
			}
		}

		public void Locate()
		{
			IgnoreIfNotSupported("locate");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 20);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql = "select locate('bc', a.Description, 2) from Animal a";
				IList lresult = s.CreateQuery(hql).List();
				Assert.AreEqual(2, lresult[0]);

				hql = "from Animal a where locate('bc', a.Description) = 2";
				Animal result = (Animal)s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("abcdef", result.Description);
			}
		}

		public void Trim()
		{
			IgnoreIfNotSupported("trim");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abc   ", 1);
				Animal a2 = new Animal("   def", 2);
				Animal a3 = new Animal("___def__", 3);
				s.Save(a1);
				s.Save(a2);
				s.Save(a3);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql = "select trim(a.Description) from Animal a where a.Description='   def'";
				IList lresult = s.CreateQuery(hql).List();
				Assert.AreEqual("def", lresult[0]);

				hql = "select trim('_' from a.Description) from Animal a where a.Description='___def__'";
				lresult = s.CreateQuery(hql).List();
				Assert.AreEqual("def", lresult[0]);

				hql = "select trim(trailing from a.Description) from Animal a where a.Description= 'abc   '";
				lresult = s.CreateQuery(hql).List();
				Assert.AreEqual("abc", lresult[0]);

				hql = "select trim(leading from a.Description) from Animal a where a.Description='   def'";
				lresult = s.CreateQuery(hql).List();
				Assert.AreEqual("def", lresult[0]);

				// where
				hql = "from Animal a where trim(a.Description) = 'abc'";
				lresult = s.CreateQuery(hql).List();
				Assert.AreEqual(1, lresult.Count);

				hql = "from Animal a where trim('_' from a.Description) = 'def'";
				Animal result = (Animal) s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("___def__", result.Description);

				hql = "from Animal a where trim(trailing from a.Description) = 'abc'";
				result = (Animal) s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual(1, result.BodyWeight); //Firebird auto rtrim VARCHAR

				hql = "from Animal a where trim(leading from a.Description) = 'def'";
				result = (Animal) s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("   def", result.Description);

				Animal a = new Animal("   abc", 20);
				s.Save(a);
				s.Flush();
				hql = "from Animal a where trim(both from a.Description) = 'abc'";
				lresult = s.CreateQuery(hql).List();
				Assert.AreEqual(2, lresult.Count);
			}
		}

		public void SubStringTwoParameters()
		{
			// All dialects that support the substring function should support
			// the two-parameter overload - emulating it by generating the 
			// third parameter (length) if the database requires three parameters.

			IgnoreIfNotSupported("substring");

			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 20);
				s.Save(a1);
				s.Flush();
			}

			using (ISession s = OpenSession())
			{
				string hql;

				// In the select clause.
				hql = "select substring(a.Description, 3) from Animal a";
				IList lresult = s.CreateQuery(hql).List();
				Assert.AreEqual(1, lresult.Count);
				Assert.AreEqual("cdef", lresult[0]);

				// In the where clause.
				hql = "from Animal a where substring(a.Description, 4) = 'def'";
				var result = (Animal)s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				// With parameters and nested function calls.
				if (!(Dialect is FirebirdDialect))  // Firebird only supports integer literals for start (and length).
				{
					hql = "from Animal a where substring(concat(a.Description, ?), :start) = 'deffoo'";
					result = (Animal) s.CreateQuery(hql)
					                  	.SetParameter(0, "foo")
					                  	.SetParameter("start", 4)
					                  	.UniqueResult();
					Assert.AreEqual("abcdef", result.Description);
				}
			}
		}

		public void SubString()
		{
			IgnoreIfNotSupported("substring");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 20);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql;

				hql = "from Animal a where substring(a.Description, 2, 3) = 'bcd'";
				Animal result = (Animal) s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				hql = "from Animal a where substring(a.Description, 2, 3) = ?";
				result = (Animal)s.CreateQuery(hql)
					.SetParameter(0, "bcd")
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);


				if (Dialect is FirebirdDialect)
				{
					// Firebird only supports integer literals for start (and length).
					return;
				}

				// Following tests verify that parameters can be used.

				hql = "from Animal a where substring(a.Description, 2, ?) = 'bcd'";
				result = (Animal)s.CreateQuery(hql)
					.SetParameter(0, 3)
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);


				hql = "from Animal a where substring(a.Description, ?, ?) = ?";
				result = (Animal)s.CreateQuery(hql)
					.SetParameter(0, 2)
					.SetParameter(1, 3)
					.SetParameter(2, "bcd")
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				hql = "select substring(a.Description, ?, ?) from Animal a";
				IList results = s.CreateQuery(hql)
					.SetParameter(0, 2)
					.SetParameter(1, 3)
					.List();
				Assert.AreEqual(1, results.Count);
				Assert.AreEqual("bcd", results[0]);
			}
		}

		public void AggregateSumNH1100()
		{
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("a1", 20);
				Animal a2 = new Animal("a1", 10);
				s.Save(a1);
				s.Save(a2);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				Assert.Throws<QueryException>(() => s.CreateQuery("select distinct new SummaryItem(a.Description, sum(BodyWeight)) from Animal a").List<SummaryItem>());
			}
		}

		public void AggregateMin()
		{
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("a1", 20);
				Animal a2 = new Animal("a2", 10);
				s.Save(a1);
				s.Save(a2);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				object result = s.CreateQuery("select min(a.BodyWeight) from Animal a").UniqueResult();
				Assert.AreEqual(typeof(float), result.GetType()); //use column type
				Assert.AreEqual(10F, result);

                if (TestDialect.SupportsHavingWithoutGroupBy)
                {
                    result = s.CreateQuery("select min(a.BodyWeight) from Animal a having min(a.BodyWeight)>0").UniqueResult();
                    Assert.AreEqual(typeof(float), result.GetType()); //use column type
                    Assert.AreEqual(10F, result);
                }
			}
		}

		public void SubString()
		{
			IgnoreIfNotSupported("substring");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 20);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				string hql;

				bool twoArgSubstringSupported = Dialect.Functions["substring"] is AnsiSubstringFunction;

				if (twoArgSubstringSupported)
				{
					hql = "select substring(a.Description, 3) from Animal a";
					IList lresult = s.CreateQuery(hql).List();
					Assert.AreEqual(1, lresult.Count);
					Assert.AreEqual("cdef", lresult[0]);
				}

				hql = "from Animal a where substring(a.Description, 2, 3) = 'bcd'";
				Animal result = (Animal) s.CreateQuery(hql).UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				hql = "from Animal a where substring(a.Description, 2, 3) = ?";
				result = (Animal)s.CreateQuery(hql)
					.SetParameter(0, "bcd")
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);


				hql = "from Animal a where substring(a.Description, 2, ?) = 'bcd'";
				result = (Animal)s.CreateQuery(hql)
					.SetParameter(0, 3)
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);


				hql = "from Animal a where substring(a.Description, ?, ?) = ?";
				result = (Animal)s.CreateQuery(hql)
					.SetParameter(0, 2)
					.SetParameter(1, 3)
					.SetParameter(2, "bcd")
					.UniqueResult();
				Assert.AreEqual("abcdef", result.Description);

				hql = "select substring(a.Description, ?, ?) from Animal a";
				IList results = s.CreateQuery(hql)
					.SetParameter(0, 2)
					.SetParameter(1, 3)
					.List();
				Assert.AreEqual(1, results.Count);
				Assert.AreEqual("bcd", results[0]);

				if (twoArgSubstringSupported)
				{
					hql = "from Animal a where substring(a.Description, 4) = 'def'";
					result = (Animal) s.CreateQuery(hql).UniqueResult();
					Assert.AreEqual("abcdef", result.Description);
				}
			}
		}

		public void CastNH1446()
		{
			IgnoreIfNotSupported("cast");
			using (ISession s = OpenSession())
			{
				Animal a1 = new Animal("abcdef", 1.3f);
				s.Save(a1);
				s.Flush();
			}
			using (ISession s = OpenSession())
			{
				// Rendered in SELECT using a property 
				string hql = "select cast(a.BodyWeight As Double) from Animal a";
				IList l = s.CreateQuery(hql).List();
				Assert.AreEqual(1, l.Count);
				Assert.AreEqual(1.3f, (double)l[0], 0.00001);
			}
		}