1 #region Copyright ©  2003-2008 Richard Beauchamp <rbeauchamp@gmail.com>
   2 
   3 /*
   4 * DomainObjects for .NET
   5 * Copyright ©  2003-2008 Richard Beauchamp
   6 *
   7 * This library is free software; you can redistribute it and/or
   8 * modify it under the terms of the GNU Lesser General Public
   9 * License as published by the Free Software Foundation; either
  10 * version 2.1 of the License, or (at your option) any later version.
  11 *
  12 * This library is distributed in the hope that it will be useful,
  13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15 * Lesser General Public License for more details.
  16 *
  17 * You should have received a copy of the GNU Lesser General Public
  18 * License along with this library; if not, write to the Free Software
  19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA
  20 */
  21 
  22 #endregion
  23 
  24 using System;
  25 using System.Data;
  26 using System.Linq;
  27 using DomainObjects.Diagnostics;
  28 using DomainObjects.Facade.Command;
  29 using DomainObjects.Facade.Command.Expression;
  30 using DomainObjects.Facade.Command.Function;
  31 using DomainObjects.Facade.Test;
  32 using DomainObjects.Test.Domain;
  33 using NUnit.Framework;
  34 
  35 namespace DomainObjects.Test.TestFixture
  36 {
  37   [TestFixture]
  38   public class FunctionTests : DomainObjectsTestFixture
  39   {
  40     #region Case Function Tests
  41 
  42     /// <summary>
  43     /// Tests that the SQL CASE function is correctly
  44     /// generated. Should generate a SELECT statement
  45     /// that looks something like:
  46     ///
  47     /// SELECT
  48     ///         CASE
  49     ///                 WHEN (PERSON01.FIRSTNAME = 'albert') THEN 'Found albert'
  50     ///                 WHEN (PERSON01.FIRSTNAME = 'tom') THEN 'Found tom'
  51     ///                 ELSE 'Did not find tom or albert'
  52     ///         END    AS FirstName
  53     /// FROM    PERSON AS PERSON01
  54     /// WHERE   ((PERSON01.FIRSTNAME   = 'albert'
  55     ///         OR (PERSON01.FIRSTNAME = 'tom')))
  56     /// ORDER BY 1
  57     /// </summary>
  58     [Test]
  59     public void Case()
  60     {
  61       // create an empty query
  62       Query<Person> query = new Query<Person>();
  63 
  64       // create a case instance
  65       Case searchedCase = new Case();
  66 
  67       // define the first case
  68       Criteria caseWhen = new Criteria();
  69       caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert");
  70       // add the WHEN and THEN case
  71       searchedCase.AddCase(caseWhen, "Found albert");
  72 
  73       // define the second case
  74       Criteria caseWhen2 = new Criteria();
  75       caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom");
  76       // add the WHEN and THEN case
  77       searchedCase.AddCase(caseWhen2, "Found tom");
  78 
  79       // add an else
  80       searchedCase.AddElse("Did not find tom or albert");
  81 
  82       // add the case as a select field
  83       query.AddSelectField(searchedCase.As("FirstName"));
  84 
  85       // add a criteria to only return the rows for albert and tom
  86       Criteria whereCriteria = new Criteria();
  87       whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert");
  88       Criteria whereCriteria2 = new Criteria();
  89       whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom");
  90       whereCriteria.AddOrCriteria(whereCriteria2);
  91 
  92       // order by the case function
  93       whereCriteria.AddOrderBy(searchedCase);
  94 
  95       // set the where criteria
  96       query.Where = whereCriteria;
  97 
  98       DataSet dataSet = query.FindDataSet();
  99 
 100       // the case function should have changed the return value
 101       // from 'albert' to 'Found albert' for the first row
 102       Assert.AreEqual("Found albert", dataSet.Tables[0].Rows[0]["FirstName"]);
 103 
 104       // the case function should have changed the return value
 105       // from 'tom' to 'Found tom' for the second row
 106       Assert.AreEqual("Found tom", dataSet.Tables[0].Rows[1]["FirstName"]);
 107     }
 108 
 109     [Test]
 110     public void CaseWithNull()
 111     {
 112       // create an empty query
 113       Query<Person> query = new Query<Person>();
 114 
 115       // create a case instance
 116       Case searchedCase = new Case();
 117 
 118       // define the first case
 119       Criteria caseWhen = new Criteria();
 120       caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert");
 121       // add the WHEN and THEN case
 122       searchedCase.AddCase(caseWhen, Null.Instance);
 123 
 124       // define the second case
 125       Criteria caseWhen2 = new Criteria();
 126       caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom");
 127       // add the WHEN and THEN case
 128       searchedCase.AddCase(caseWhen2, Null.Instance);
 129 
 130       // add an else
 131       searchedCase.AddElse("Did not find tom or albert");
 132 
 133       // add the case as a select field
 134       query.AddSelectField(searchedCase.As("FirstName"));
 135 
 136       // add a criteria to only return the rows for albert and tom
 137       Criteria whereCriteria = new Criteria();
 138       whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert");
 139       Criteria whereCriteria2 = new Criteria();
 140       whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom");
 141       whereCriteria.AddOrCriteria(whereCriteria2);
 142 
 143       // order by the case function
 144       whereCriteria.AddOrderBy(searchedCase);
 145 
 146       // set the where criteria
 147       query.Where = whereCriteria;
 148 
 149       DataSet dataSet = query.FindDataSet();
 150 
 151       // the case function should have changed the return value
 152       // from 'albert' to 'Found albert' for the first row
 153       Assert.AreEqual(Convert.DBNull, dataSet.Tables[0].Rows[0]["FirstName"]);
 154 
 155       // the case function should have changed the return value
 156       // from 'tom' to 'Found tom' for the second row
 157       Assert.AreEqual(Convert.DBNull, dataSet.Tables[0].Rows[1]["FirstName"]);
 158     }
 159 
 160     /// <summary>
 161     /// Validate that an integer value is returned
 162     /// by the case statement when specified in the
 163     /// 'then' parameter of AddCase().
 164     /// </summary>
 165     [Test]
 166     public void CaseWithAnIntegerValueForThen()
 167     {
 168       // create an empty query
 169       Query<Person> query = new Query<Person>();
 170 
 171       // create a case instance
 172       Case searchedCase = new Case();
 173 
 174       // define the first case
 175       Criteria caseWhen = new Criteria();
 176       caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert");
 177       // add the WHEN and THEN case
 178       searchedCase.AddCase(caseWhen, 1);
 179 
 180       // define the second case
 181       Criteria caseWhen2 = new Criteria();
 182       caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom");
 183       // add the WHEN and THEN case
 184       searchedCase.AddCase(caseWhen2, 2);
 185 
 186       // add an else
 187       searchedCase.AddElse(3);
 188 
 189       // add the case as a select field
 190       query.AddSelectField(searchedCase.As("FirstNameToInteger"));
 191 
 192       // add a criteria to only return the rows for albert and tom
 193       Criteria whereCriteria = new Criteria();
 194       whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert");
 195       Criteria whereCriteria2 = new Criteria();
 196       whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom");
 197       whereCriteria.AddOrCriteria(whereCriteria2);
 198 
 199       // order by the case function
 200       whereCriteria.AddOrderBy(searchedCase);
 201 
 202       // set the where criteria
 203       query.Where = whereCriteria;
 204 
 205       DataSet dataSet = query.FindDataSet();
 206 
 207       //Comments: Access returns 1 as string. dkondratiuk
 208 
 209       // the case function should have changed the return value
 210       // from 'albert' to the integer 1 for the first row
 211       Assert.AreEqual("1", dataSet.Tables[0].Rows[0]["FirstNameToInteger"].ToString());
 212 
 213       // the case function should have changed the return value
 214       // from 'tom' to the integer 2 for the second row
 215       Assert.AreEqual("2", dataSet.Tables[0].Rows[1]["FirstNameToInteger"].ToString());
 216     }
 217 
 218     #endregion
 219 
 220     #region Count Function Tests
 221 
 222     /// <summary>
 223     /// Test that the order by is ignored when doing a count.
 224     /// </summary>
 225     [Test]
 226     public void Count_WithAnOrderBy()
 227     {
 228       Criteria criteria = new Criteria();
 229       criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products");
 230       criteria.AddOrderBy(Types.Product.Field._name);
 231 
 232       int count = QueryFacade.GetCount<Product>(criteria);
 233 
 234       Assert.AreEqual(16, count, "The correct count was returned.");
 235     }
 236 
 237     [Test]
 238     public void Count_WithBetweenCriteria()
 239     {
 240       Criteria criteria = new Criteria();
 241       criteria.AddBetween(Types.Person.Field._primaryKey, 1, 5);
 242 
 243       int count = QueryFacade.GetCount<Person>(criteria);
 244       Assert.AreEqual(5, count);
 245     }
 246 
 247     #endregion
 248 
 249     [Test]
 250     public void Average()
 251     {
 252       Criteria criteria = new Criteria();
 253       criteria.AddEqualTo(Types.Person.Field._firstname, "tom");
 254 
 255       DataSet dataSet = QueryFacade.FindDataSet<Person>(criteria, Types.Person.Field._primaryKey.Average().As("AveragePrimaryKey"));
 256 
 257       Assert.AreEqual(6m, dataSet.Tables[0].Rows[0]["AveragePrimaryKey"]);
 258     }
 259 
 260     /// <summary>
 261     /// Validates that a Sum function of a Case function
 262     /// generates the correct SQL statement.
 263     /// Access 2000 does not support the distinct keyword inside a sum function.
 264     /// </summary>
 265     [Test]
 266     [Category(UnitTestCategory.DoesNotWorkWithAccess)]
 267     public void SumOfCaseFunction()
 268     {
 269       // create an empty query
 270       Query<Person> query = new Query<Person>();
 271 
 272       // create a case instance
 273       Case searchedCase = new Case();
 274 
 275       // define the first case
 276       Criteria caseWhen = new Criteria();
 277       caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert");
 278       // add the WHEN and THEN case
 279       searchedCase.AddCase(caseWhen, 1);
 280 
 281       // define the second case
 282       Criteria caseWhen2 = new Criteria();
 283       caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom");
 284       // add the WHEN and THEN case
 285       searchedCase.AddCase(caseWhen2, 2);
 286 
 287       // add an else
 288       searchedCase.AddElse(3);
 289 
 290       // add the sum of the case as a select field
 291       query.AddSelectField(searchedCase.Sum(false).As("FirstNameToSumOfInteger"));
 292 
 293       // add a criteria to only return the rows for albert and tom
 294       Criteria whereCriteria = new Criteria();
 295       whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert");
 296       Criteria whereCriteria2 = new Criteria();
 297       whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom");
 298       whereCriteria.AddOrCriteria(whereCriteria2);
 299 
 300       // group by the first name so that we can sum the case statements
 301       whereCriteria.AddGroupBy(Types.Person.Field._firstname);
 302 
 303       // order by the case function
 304       whereCriteria.AddOrderBy(searchedCase);
 305 
 306       // set the where criteria
 307       query.Where = whereCriteria;
 308 
 309       DataSet dataSet = query.FindDataSet();
 310 
 311       // the case function should have changed the return value
 312       // from 'albert' to the integer 1 for the first row
 313       Assert.AreEqual(1, dataSet.Tables[0].Rows[0]["FirstNameToSumOfInteger"]);
 314 
 315       // the case function should have changed the return value
 316       // from 'tom' to the integer 2 for the second row
 317       Assert.AreEqual(4, dataSet.Tables[0].Rows[1]["FirstNameToSumOfInteger"]);
 318     }
 319 
 320     /// <summary>
 321     /// Validate that the same constant value can be added
 322     /// multiple times to a query without causing an exception.
 323     /// Validates fix for bug [ 1537678 ] Exception while utilizing constant with same value in Case.
 324     /// </summary>
 325     [Test]
 326     public void UtilizeSameConstantValueTwiceInQuery()
 327     {
 328       // create an empty query
 329       Query<Person> query = new Query<Person>();
 330 
 331       // create a case instance
 332       Case searchedCase = new Case();
 333 
 334       // define the first case
 335       Criteria caseWhen = new Criteria();
 336       caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert");
 337       // add the WHEN and THEN case
 338       searchedCase.AddCase(caseWhen, 1);
 339 
 340       // define the second case
 341       Criteria caseWhen2 = new Criteria();
 342       caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom");
 343       // add the WHEN and THEN case
 344       searchedCase.AddCase(caseWhen2, 1);
 345 
 346       // add an else
 347       searchedCase.AddElse(3);
 348 
 349       // add the case as a select field
 350       query.AddSelectField(searchedCase.As("FirstNameToInteger"));
 351 
 352       // add a criteria to only return the rows for albert and tom
 353       Criteria whereCriteria = new Criteria();
 354       whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert");
 355       Criteria whereCriteria2 = new Criteria();
 356       whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom");
 357       whereCriteria.AddOrCriteria(whereCriteria2);
 358 
 359       // order by the case function
 360       whereCriteria.AddOrderBy(searchedCase);
 361 
 362       // set the where criteria
 363       query.Where = whereCriteria;
 364 
 365       DataSet dataSet = query.FindDataSet();
 366 
 367       //Comments: Access returns 1 as string. dkondratiuk
 368 
 369       // the case function should have changed the return value
 370       // from 'albert' to the integer 1 for the first row
 371       Assert.AreEqual("1", dataSet.Tables[0].Rows[0]["FirstNameToInteger"].ToString());
 372 
 373       // the case function should have changed the return value
 374       // from 'tom' to the integer 1 for the second row
 375       Assert.AreEqual("1", dataSet.Tables[0].Rows[1]["FirstNameToInteger"].ToString());
 376     }
 377 
 378     [Test]
 379     [Category(UnitTestCategory.SQLServerSpecific), Category(UnitTestCategory.DoesNotWorkWithOracle)]
 380     public void AddBetweenWithGetDateFunction()
 381     {
 382       Criteria criteria = new Criteria();
 383 
 384       criteria.AddBetween(Types.LockedByVersion.Field._createdDate, DateTime.Now.AddYears(-10), new GetDate());
 385 
 386       QueryFacade.GetCount<LockedByVersion>(criteria);
 387     }
 388 
 389     [Test]
 390     public void Max()
 391     {
 392       Criteria criteria = new Criteria();
 393       criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages");
 394       DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product.Field._pricePerUnit.Max().As("MaxPricePerUnit"));
 395 
 396       Assert.AreEqual(263.50m, dataSet.Tables[0].Rows[0]["MaxPricePerUnit"]);
 397     }
 398 
 399     [Test]
 400     public void Sum()
 401     {
 402       Criteria criteria = new Criteria();
 403       criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products");
 404       DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product.Field._pricePerUnit.Sum().As("SumPricePerUnit"));
 405 
 406       Assert.AreEqual(416.24m, dataSet.Tables[0].Rows[0]["SumPricePerUnit"]);
 407     }
 408 
 409     [Test]
 410     [Category(UnitTestCategory.DoesNotWorkWithOracle)]
 411     public void DateDiff()
 412     {
 413       QueryFacade.FindDataSet<LockedByVersion>(null, new DateDiff(PartOfDateTime.Day, Types.LockedByVersion.Field._createdDate, new GetDate()));
 414     }
 415 
 416     [Test]
 417     [Category(UnitTestCategory.DoesNotWorkWithOracle)]
 418     public void DatePart()
 419     {
 420       // A first test verifying that the month is within the valid interval [1..12]
 421       Criteria criteria = new Criteria();
 422 
 423       criteria.AddEqualTo(Types.LockedByVersion.Field._primaryKey, 1);
 424 
 425       // The timestamp was set to the date and time when the row was actually created
 426       DataSet dataSet = QueryFacade.FindDataSet<LockedByVersion>(criteria, Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month));
 427 
 428       Assert.IsNotNull(dataSet);
 429       Assert.AreEqual(1, dataSet.Tables[0].Rows.Count);
 430 
 431       int month = Convert.ToInt32(dataSet.Tables[0].Rows[0].ItemArray.GetValue(0));
 432       Assert.IsTrue(month >= 1 && month <= 12, "the month is within the valid range");
 433 
 434       // A second test verifying that the month is the expected one
 435       criteria = new Criteria();
 436 
 437       criteria.AddEqualTo(Types.BaseTypeReference.Field._primaryKey, 1002);
 438 
 439       // The timestamp was set to the date and time when the row was actually created
 440       dataSet = QueryFacade.FindDataSet<BaseTypeReference>(criteria, Types.BaseTypeReference.Field._nullableType.DatePart(PartOfDateTime.Month));
 441 
 442       Assert.IsNotNull(dataSet);
 443       Assert.AreEqual(1, dataSet.Tables[0].Rows.Count);
 444 
 445       month = Convert.ToInt32(dataSet.Tables[0].Rows[0].ItemArray.GetValue(0));
 446       Assert.AreEqual(7, month, "the month was the expected one");
 447     }
 448 
 449     [Test]
 450     public void Upper()
 451     {
 452       Criteria criteria = new Criteria();
 453       criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages");
 454       DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Upper().As("Category"));
 455 
 456       Assert.AreEqual("BEVERAGES", dataSet.Tables[0].Rows[0]["Category"]);
 457     }
 458 
 459     [Test]
 460     public void Lower()
 461     {
 462       Criteria criteria = new Criteria();
 463       criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages");
 464       DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Lower().As("Category"));
 465 
 466       Assert.AreEqual("beverages", dataSet.Tables[0].Rows[0]["Category"]);
 467     }
 468 
 469     [Test]
 470     public void UpperInALikeExpr()
 471     {
 472       Criteria criteria = new Criteria();
 473       criteria.AddLike(Types.Product._productCategory.Field._name.Upper(), "BEVERAGES");
 474       DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Upper().As("Category"));
 475 
 476       Assert.AreEqual("BEVERAGES", dataSet.Tables[0].Rows[0]["Category"]);
 477     }
 478 
 479     [Test]
 480     public void LowerInALikeExpr()
 481     {
 482       Criteria criteria = new Criteria();
 483       criteria.AddLike(Types.Product._productCategory.Field._name.Lower(), "beverages");
 484       DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Lower().As("Category"));
 485 
 486       Assert.AreEqual("beverages", dataSet.Tables[0].Rows[0]["Category"]);
 487     }
 488 
 489     /// <summary>
 490     /// Tests ordering by the <see cref="NewId"/> function.
 491     /// </summary>
 492     [Category(UnitTestCategory.DoesNotWorkWithAccess)]
 493     [Category(UnitTestCategory.DoesNotWorkWithOracle)]
 494     [Category(UnitTestCategory.SQLServerSpecific)]
 495     [Test]
 496     public void OrderByNewId()
 497     {
 498       // Capture the SQL statement generated by the query
 499       CommandMonitor commandMonitor = new CommandMonitor();
 500 
 501       // Create a new Query
 502       Query<ProductCategory> query = new Query<ProductCategory>();
 503 
 504       // Order by the NewId function
 505       query.Where.AddOrderBy(NewId.Instance);
 506 
 507       // Execute the query
 508       query.FindObjectSet();
 509 
 510       // Assert that the correct SQL statement was generated
 511       Assert.IsTrue(commandMonitor.DatabaseEventArgList[0].CommandText.Contains("NEWID()"));
 512       Assert.IsTrue(commandMonitor.DatabaseEventArgList[0].CommandText.Contains("ORDER BY 6"));
 513     }
 514 
 515     #region Convert Function Tests
 516 
 517     [Category(UnitTestCategory.DoesNotWorkWithAccess)]
 518     [Category(UnitTestCategory.DoesNotWorkWithOracle)]
 519     [Category(UnitTestCategory.SQLServerSpecific)]
 520     [Test]
 521     public void ConvertTo()
 522     {
 523       Query<Product> productQuery = new Query<Product>();
 524       productQuery.Top = 1;
 525       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, null, Style.MM_dd_yyyy).As("FormattedDate"));
 526       DataSet result = productQuery.FindDataSet();
 527       string convertedValue = (string) result.Tables[0].Rows[0]["FormattedDate"];
 528       Assert.AreEqual(DateTime.Now.ToString("MM/dd/yyyy"), convertedValue, "The date was formatted according to the style Style.MM_dd_yyyy.");
 529 
 530       productQuery = new Query<Product>();
 531       productQuery.Top = 1;
 532       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, null, Style.MM_dd_yy));
 533       productQuery.FindDataSet();
 534 
 535       productQuery = new Query<Product>();
 536       productQuery.Top = 1;
 537       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, 10, Style.MM_dd_yy));
 538       productQuery.FindDataSet();
 539 
 540       // Now test conversions to all other db
 541       productQuery = new Query<Product>();
 542       productQuery.Top = 1;
 543       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.BigInt, null, null));
 544       productQuery.FindDataSet();
 545 
 546       productQuery = new Query<Product>();
 547       productQuery.Top = 1;
 548       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Binary, null, null));
 549       productQuery.FindDataSet();
 550 
 551       productQuery = new Query<Product>();
 552       productQuery.Top = 1;
 553       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Bit, null, null));
 554       productQuery.FindDataSet();
 555 
 556       productQuery = new Query<Product>();
 557       productQuery.Top = 1;
 558       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Char, null, null));
 559       productQuery.FindDataSet();
 560 
 561       productQuery = new Query<Product>();
 562       productQuery.Top = 1;
 563       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.DateTime, null, null));
 564       productQuery.FindDataSet();
 565 
 566       productQuery = new Query<Product>();
 567       productQuery.Top = 1;
 568       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Decimal, null, null));
 569       productQuery.FindDataSet();
 570 
 571       productQuery = new Query<Product>();
 572       productQuery.Top = 1;
 573       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Float, null, null));
 574       productQuery.FindDataSet();
 575 
 576       productQuery = new Query<Product>();
 577       productQuery.Top = 1;
 578       productQuery.AddSelectField(new ConvertTo("VarChar to Image conversion.", SqlDbType.Image, null, null));
 579       productQuery.FindDataSet();
 580 
 581       productQuery = new Query<Product>();
 582       productQuery.Top = 1;
 583       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Int, null, null));
 584       productQuery.FindDataSet();
 585 
 586       productQuery = new Query<Product>();
 587       productQuery.Top = 1;
 588       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Money, null, null));
 589       productQuery.FindDataSet();
 590 
 591       productQuery = new Query<Product>();
 592       productQuery.Top = 1;
 593       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.NChar, null, null));
 594       productQuery.FindDataSet();
 595 
 596       productQuery = new Query<Product>();
 597       productQuery.Top = 1;
 598       productQuery.AddSelectField(new ConvertTo("VarChar to NText conversion", SqlDbType.NText, null, null));
 599       productQuery.FindDataSet();
 600 
 601       productQuery = new Query<Product>();
 602       productQuery.Top = 1;
 603       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.NVarChar, null, null));
 604       productQuery.FindDataSet();
 605 
 606       productQuery = new Query<Product>();
 607       productQuery.Top = 1;
 608       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Real, null, null));
 609       productQuery.FindDataSet();
 610 
 611       productQuery = new Query<Product>();
 612       productQuery.Top = 1;
 613       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.SmallDateTime, null, null));
 614       productQuery.FindDataSet();
 615 
 616       productQuery = new Query<Product>();
 617       productQuery.Top = 1;
 618       productQuery.AddSelectField(new ConvertTo(8, SqlDbType.SmallInt, null, null));
 619       productQuery.FindDataSet();
 620 
 621       productQuery = new Query<Product>();
 622       productQuery.Top = 1;
 623       productQuery.AddSelectField(new ConvertTo(6, SqlDbType.SmallMoney, null, null));
 624       productQuery.FindDataSet();
 625 
 626       productQuery = new Query<Product>();
 627       productQuery.Top = 1;
 628       productQuery.AddSelectField(new ConvertTo("VarChar to Text conversion", SqlDbType.Text, null, null));
 629       productQuery.FindDataSet();
 630 
 631       productQuery = new Query<Product>();
 632       productQuery.Top = 1;
 633       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Timestamp, null, null));
 634       productQuery.FindDataSet();
 635 
 636       productQuery = new Query<Product>();
 637       productQuery.Top = 1;
 638       productQuery.AddSelectField(new ConvertTo(7, SqlDbType.TinyInt, null, null));
 639       productQuery.FindDataSet();
 640 
 641       productQuery = new Query<Product>();
 642       productQuery.Top = 1;
 643       productQuery.AddSelectField(new ConvertTo(Guid.NewGuid().ToString(), SqlDbType.UniqueIdentifier, null, null));
 644       productQuery.FindDataSet();
 645 
 646       productQuery = new Query<Product>();
 647       productQuery.Top = 1;
 648       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarBinary, null, null));
 649       productQuery.FindDataSet();
 650 
 651       productQuery = new Query<Product>();
 652       productQuery.Top = 1;
 653       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, null, null));
 654       productQuery.FindDataSet();
 655 
 656       productQuery = new Query<Product>();
 657       productQuery.Top = 1;
 658       productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Variant, null, null));
 659       productQuery.FindDataSet();
 660 
 661       productQuery = new Query<Product>();
 662       productQuery.Top = 1;
 663       productQuery.AddSelectField(new ConvertTo("VarChar to XML conversion", SqlDbType.Xml, null, null));
 664       productQuery.FindDataSet();
 665     }
 666 
 667     #endregion
 668 
 669     [Test]
 670     public void DateAdd()
 671     {
 672       // Test DateAdd when the interval is an integer
 673       Query<LockedByVersion> dateAddQueryWithInteger = new Query<LockedByVersion>();
 674       dateAddQueryWithInteger.AddSelectField(new DateAdd(PartOfDateTime.Day, 1, new GetDate()).As("NewDate"));
 675 
 676       DateTime newDate = (DateTime) dateAddQueryWithInteger.FindDataSet().Tables[0].Rows[0]["NewDate"];
 677 
 678       Assert.AreEqual(DateTime.Now.AddDays(1).ToShortDateString(), newDate.ToShortDateString(), "Today's date was incremented by one day.");
 679 
 680       // Test DateAdd when the interval is a string
 681       Query<LockedByVersion> dateAddQueryWithString = new Query<LockedByVersion>();
 682       dateAddQueryWithString.AddSelectField(new DateAdd(PartOfDateTime.Day, "1", new GetDate()).As("NewDate"));
 683 
 684       DateTime newDate2 = (DateTime)dateAddQueryWithString.FindDataSet().Tables[0].Rows[0]["NewDate"];
 685 
 686       Assert.AreEqual(DateTime.Now.AddDays(1).ToShortDateString(), newDate2.ToShortDateString(), "Today's date was incremented by one day.");
 687     }
 688 
 689     [Test]
 690     public void DateDiffWithDateAdd()
 691     {
 692       LockedByVersion lockedByVersion = new LockedByVersion(GenerateUniqueString(Types.LockedByVersion.Field._value), DateTime.Now);
 693 
 694       PersistenceFacade.Persist(lockedByVersion);
 695 
 696       Query<LockedByVersion> dateDiffWithDateAdd = new Query<LockedByVersion>();
 697 
 698       DateAdd tomorrow = new DateAdd(PartOfDateTime.Day, 1, new GetDate());
 699 
 700       DateDiff dateDiff = new DateDiff(PartOfDateTime.Day, new GetDate(), tomorrow);
 701 
 702       dateDiffWithDateAdd.Where.AddGreaterThan(dateDiff, 0);
 703       dateDiffWithDateAdd.Where.AddEqualTo(Types.LockedByVersion.Field._primaryKey, lockedByVersion.PrimaryKey[0]);
 704 
 705       Assert.AreEqual(1, dateDiffWithDateAdd.GetObjectSetCount(), "The query was successfully generated when a field that requires a parameter is the first parameter of a criteria.");
 706     }
 707 
 708     [Test]
 709     public void GetCountToStringTest()
 710     {
 711       Assert.AreNotEqual(Types.Person.Field._primaryKey.Count().ToString(), Types.Product.Field._primaryKey.Count().ToString(), "The ToString of the Count function are different");
 712 
 713     }
 714 
 715     [Test]
 716     public void Concat()
 717     {
 718       Query<Product> concatQuery1 = new Query<Product>();
 719       concatQuery1.AddSelectField((new Constant("StringOne") + "StringTwo" + "StringThree").As("ConcatenatedStrings"));
 720       concatQuery1.Top = 1;
 721       Assert.AreEqual("StringOneStringTwoStringThree", concatQuery1.FindDataRows().Single().Field<string>("ConcatenatedStrings"), "The strings should be concatenated.");
 722 
 723       Query<Product> concatQuery2 = new Query<Product>();
 724       concatQuery2.AddSelectField(("StringOne" + new Constant("StringTwo") + "StringThree").As("ConcatenatedStrings"));
 725       concatQuery2.Top = 1;
 726       Assert.AreEqual("StringOneStringTwoStringThree", concatQuery2.FindDataRows().Single().Field<string>("ConcatenatedStrings"), "The strings should be concatenated.");
 727     }
 728 
 729     [Test]
 730     public void Concat2()
 731     {
 732       Query<Product> concatQuery1 = new Query<Product>();
 733       concatQuery1.AddSelectField((new Constant("StringOne") + new Constant("StringTwo")).As("ConcatenatedStrings"));
 734       concatQuery1.Top = 1;
 735       Assert.AreEqual("StringOneStringTwo", concatQuery1.FindDataRows().Single().Field<string>("ConcatenatedStrings"), "The strings should be concatenated.");
 736     }
 737 
 738     [Test]
 739     public void Add()
 740     {
 741       Query<Product> addQuery1 = new Query<Product>();
 742       addQuery1.AddSelectField((new Constant(2) + 1 + 1).As("AddedValues"));
 743       addQuery1.Top = 1;
 744       Assert.AreEqual(4, addQuery1.FindDataRows().Single().Field<int>("AddedValues"), "The numbers should be added.");
 745 
 746       Query<Product> addQuery2 = new Query<Product>();
 747       addQuery2.AddSelectField((1 + new Constant(2) + 1).As("AddedValues"));
 748       addQuery2.Top = 1;
 749       Assert.AreEqual(4, addQuery2.FindDataRows().Single().Field<int>("AddedValues"), "The numbers should be added.");
 750     }
 751 
 752     [Test]
 753     public void Add2()
 754     {
 755       Query<Product> addQuery = new Query<Product>();
 756       // Validate that we get a sum (and not a concat) even if the constant is cast to the type 'object'
 757       addQuery.AddSelectField((new Constant("1") + (object)new Constant(2)).As("AddedValues"));
 758       addQuery.Top = 1;
 759       Assert.AreEqual(3, addQuery.FindDataRows().Single().Field<int>("AddedValues"), "The numbers should be added.");
 760     }
 761 
 762     [Test]
 763     public void Replace()
 764     {
 765       Query<Product> replaceQuery = new Query<Product>();
 766       replaceQuery.AddSelectField(new Constant("XXAA").Replace("X", "A").As("ReplacedString"));
 767       replaceQuery.Top = 1;
 768       Assert.AreEqual("AAAA", replaceQuery.FindDataRows().Single().Field<string>("ReplacedString"), "The Xs should be replaced by As.");
 769     }
 770 
 771     [Test]
 772     public void Min()
 773     {
 774       Query<Product> minQuery = new Query<Product>();
 775       minQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages");
 776       minQuery.AddSelectField(Types.Product.Field._pricePerUnit.Min().As("MinPricePerUnit"));
 777 
 778       Assert.AreEqual(0.45m, minQuery.FindDataRows().Single().Field<decimal>("MinPricePerUnit"), "The minimum value should be selected.");
 779     }
 780 
 781     [Test]
 782     public void OrderByTheSameFieldButDifferentFunctions()
 783     {
 784       //Test using DatePart using the select fields
 785       Criteria criteria = new Criteria();
 786 
 787       criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Year));
 788       criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month));
 789 
 790       Field[] fields = new Field[] { Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Year) ,
 791                                      Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month)};
 792 
 793       QueryFacade.FindDataSet<LockedByVersion>(criteria, fields);
 794 
 795       //Test using DatePart without using the select fields
 796       criteria = new Criteria();
 797 
 798       criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Year));
 799       criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month));
 800 
 801       QueryFacade.FindObject<LockedByVersion>(criteria);
 802 
 803 
 804       //Test using DateDiff
 805       criteria = new Criteria();
 806       DateDiff dateDiff1 = new DateDiff(PartOfDateTime.Day, new GetDate(), new Constant(DateTime.Now.AddDays(2)));
 807       DateDiff dateDiff2 = new DateDiff(PartOfDateTime.Day, new GetDate(), new Constant(DateTime.Now.AddDays(3)));
 808 
 809       criteria.AddOrderBy(dateDiff1);
 810       criteria.AddOrderBy(dateDiff2);
 811 
 812       QueryFacade.FindObject<LockedByVersion>(criteria);
 813 
 814       //Test using DateAdd
 815       criteria = new Criteria();
 816       DateAdd dateAdd1 = new DateAdd(PartOfDateTime.Day, 1, new GetDate());
 817       DateAdd dateAdd2 = new DateAdd(PartOfDateTime.Day, 2, new GetDate());
 818 
 819       criteria.AddOrderBy(dateAdd1);
 820       criteria.AddOrderBy(dateAdd2);
 821 
 822       QueryFacade.FindObject<LockedByVersion>(criteria);
 823     }
 824 
 825     [Test]
 826     public void RowNumber()
 827     {
 828       // Capture the SQL statement generated by the query
 829       CommandMonitor commandMonitor = new CommandMonitor();
 830 
 831       Query<Product> productQuery = new Query<Product>();
 832       productQuery.AddSelectField(Types.Product.Field._name);
 833       productQuery.AddSelectField(new RowNumber(Types.Product.Field._name));
 834       productQuery.FindDataSet();
 835 
 836       // Assert that the correct SQL statement was generated
 837       Assert.IsTrue(commandMonitor.FirstCommandText.Contains("ROW_NUMBER() OVER (  ORDER BY Product02.NAME)"), "The command text should contain a ROW_NUMBER clause.");
 838     }
 839   }
 840 }