PageRenderTime 55ms CodeModel.GetById 25ms RepoModel.GetById 0ms app.codeStats 0ms

/Simple.Data.SqlTest/QueryTest.cs

https://github.com/kppullin/Simple.Data
C# | 320 lines | 289 code | 31 blank | 0 comment | 4 complexity | 57614f845b949902c89cd88d84943889 MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using NUnit.Framework;
  6. namespace Simple.Data.SqlTest
  7. {
  8. [TestFixture]
  9. public class QueryTest
  10. {
  11. [TestFixtureSetUp]
  12. public void Setup()
  13. {
  14. DatabaseHelper.Reset();
  15. }
  16. [Test]
  17. public void CountWithNoCriteriaShouldSelectThree()
  18. {
  19. var db = DatabaseHelper.Open();
  20. var count = db.Users.GetCount();
  21. Assert.AreEqual(3, count);
  22. }
  23. [Test]
  24. public void CountWithCriteriaShouldSelectTwo()
  25. {
  26. var db = DatabaseHelper.Open();
  27. int count = db.Users.GetCount(db.Users.Age > 30);
  28. Assert.AreEqual(2, count);
  29. }
  30. [Test]
  31. public void CountByShouldSelectOne()
  32. {
  33. var db = DatabaseHelper.Open();
  34. Assert.AreEqual(1, db.Users.GetCountByName("Bob"));
  35. }
  36. [Test]
  37. public void ExistsWithNoCriteriaShouldReturnTrue()
  38. {
  39. var db = DatabaseHelper.Open();
  40. Assert.AreEqual(true, db.Users.Exists());
  41. }
  42. [Test]
  43. public void ExistsWithValidCriteriaShouldReturnTrue()
  44. {
  45. var db = DatabaseHelper.Open();
  46. Assert.AreEqual(true, db.Users.Exists(db.Users.Age > 30));
  47. }
  48. [Test]
  49. public void ExistsWithInvalidCriteriaShouldReturnFalse()
  50. {
  51. var db = DatabaseHelper.Open();
  52. Assert.AreEqual(false, db.Users.Exists(db.Users.Age == -1));
  53. }
  54. [Test]
  55. public void ExistsByValidValueShouldReturnTrue()
  56. {
  57. var db = DatabaseHelper.Open();
  58. Assert.AreEqual(true, db.Users.ExistsByName("Bob"));
  59. }
  60. [Test]
  61. public void ExistsByInvalidValueShouldReturnFalse()
  62. {
  63. var db = DatabaseHelper.Open();
  64. Assert.AreEqual(false, db.Users.ExistsByName("Peter Kay"));
  65. }
  66. [Test]
  67. public void ColumnAliasShouldChangeDynamicPropertyName()
  68. {
  69. var db = DatabaseHelper.Open();
  70. var actual = db.Users.QueryById(1).Select(db.Users.Name.As("Alias")).First();
  71. Assert.AreEqual("Bob", actual.Alias);
  72. }
  73. [Test]
  74. public void ShouldSelectFromOneToTen()
  75. {
  76. var db = DatabaseHelper.Open();
  77. var query = db.PagingTest.QueryById(1.to(100)).Take(10);
  78. int index = 1;
  79. foreach (var row in query)
  80. {
  81. Assert.AreEqual(index, row.Id);
  82. index++;
  83. }
  84. }
  85. [Test]
  86. public void ShouldSelectFromElevenToTwenty()
  87. {
  88. var db = DatabaseHelper.Open();
  89. var query = db.PagingTest.QueryById(1.to(100)).Skip(10).Take(10);
  90. int index = 11;
  91. foreach (var row in query)
  92. {
  93. Assert.AreEqual(index, row.Id);
  94. index++;
  95. }
  96. }
  97. [Test]
  98. public void ShouldSelectFromOneHundredToNinetyOne()
  99. {
  100. var db = DatabaseHelper.Open();
  101. var query = db.PagingTest.QueryById(1.to(100)).OrderByDescending(db.PagingTest.Id).Skip(0).Take(10);
  102. int index = 100;
  103. foreach (var row in query)
  104. {
  105. Assert.AreEqual(index, row.Id);
  106. index--;
  107. }
  108. }
  109. [Test]
  110. public void WithTotalCountShouldGiveCount()
  111. {
  112. Promise<int> count;
  113. var db = DatabaseHelper.Open();
  114. var list = db.PagingTest.QueryById(1.to(50))
  115. .Take(10)
  116. .WithTotalCount(out count)
  117. .ToList();
  118. Assert.AreEqual(10, list.Count);
  119. Assert.IsTrue(count.HasValue);
  120. Assert.AreEqual(50, count);
  121. }
  122. [Test]
  123. public void WithTotalCountWithExplicitSelectShouldGiveCount()
  124. {
  125. Promise<int> count;
  126. var db = DatabaseHelper.Open();
  127. List<dynamic> list = db.PagingTest.QueryById(1.to(50))
  128. .Select(db.PagingTest.Id)
  129. .WithTotalCount(out count)
  130. .Take(10)
  131. .ToList();
  132. Assert.IsTrue(count.HasValue);
  133. Assert.AreEqual(50, count);
  134. Assert.AreEqual(10, list.Count);
  135. foreach (var dictionary in list.Cast<IDictionary<string,object>>())
  136. {
  137. Assert.AreEqual(1, dictionary.Count);
  138. }
  139. }
  140. [Test]
  141. public void WithTotalCountWithExplicitSelectAndOrderByShouldGiveCount()
  142. {
  143. Promise<int> count;
  144. var db = DatabaseHelper.Open();
  145. List<dynamic> list = db.PagingTest.QueryById(1.to(50))
  146. .Select(db.PagingTest.Id)
  147. .OrderByDescending(db.PagingTest.Id)
  148. .WithTotalCount(out count)
  149. .Take(10)
  150. .ToList();
  151. Assert.IsTrue(count.HasValue);
  152. Assert.AreEqual(50, count);
  153. Assert.AreEqual(10, list.Count);
  154. foreach (var dictionary in list.Cast<IDictionary<string, object>>())
  155. {
  156. Assert.AreEqual(1, dictionary.Count);
  157. }
  158. }
  159. [Test]
  160. public void WithTotalCountShouldGiveCount_ObsoleteFutureVersion()
  161. {
  162. Future<int> count;
  163. var db = DatabaseHelper.Open();
  164. var list = db.PagingTest.QueryById(1.to(50))
  165. .WithTotalCount(out count)
  166. .Take(10)
  167. .ToList();
  168. Assert.AreEqual(10, list.Count);
  169. Assert.IsTrue(count.HasValue);
  170. Assert.AreEqual(50, count);
  171. }
  172. [Test]
  173. public void ShouldDirectlyQueryDetailTable()
  174. {
  175. var db = DatabaseHelper.Open();
  176. var order = db.Customers.QueryByNameAndAddress("Test", "100 Road").Orders.FirstOrDefault();
  177. Assert.IsNotNull(order);
  178. Assert.AreEqual(1, order.OrderId);
  179. }
  180. [Test]
  181. public void ShouldReturnNullWhenNoRowFound()
  182. {
  183. var db = DatabaseHelper.Open();
  184. string name = db.Customers
  185. .Query()
  186. .Select(db.Customers.Name)
  187. .Where(db.Customers.CustomerId == 0) // There is no CustomerId 0
  188. .OrderByName()
  189. .Take(1) // Should return only one record no matter what
  190. .ToScalarOrDefault<string>();
  191. Assert.IsNull(name);
  192. }
  193. [Test]
  194. public void ToScalarListShouldReturnStringList()
  195. {
  196. var db = DatabaseHelper.Open();
  197. List<string> name = db.Customers
  198. .Query()
  199. .Select(db.Customers.Name)
  200. .OrderByName()
  201. .ToScalarList<string>();
  202. Assert.IsNotNull(name);
  203. Assert.AreNotEqual(0, name.Count);
  204. }
  205. [Test]
  206. public void ToScalarArrayShouldReturnStringArray()
  207. {
  208. var db = DatabaseHelper.Open();
  209. string[] name = db.Customers
  210. .Query()
  211. .Select(db.Customers.Name)
  212. .OrderByName()
  213. .ToScalarArray<string>();
  214. Assert.IsNotNull(name);
  215. Assert.AreNotEqual(0, name.Length);
  216. }
  217. [Test]
  218. public void HavingWithMinDateShouldReturnCorrectRow()
  219. {
  220. var db = DatabaseHelper.Open();
  221. var row =
  222. db.GroupTestMaster.Query().Having(db.GroupTestMaster.GroupTestDetail.Date.Min() >=
  223. new DateTime(2000, 1, 1))
  224. .FirstOrDefault();
  225. Assert.IsNotNull(row);
  226. Assert.AreEqual("Two", row.Name);
  227. }
  228. [Test]
  229. public void HavingWithMaxDateShouldReturnCorrectRow()
  230. {
  231. var db = DatabaseHelper.Open();
  232. var row =
  233. db.GroupTestMaster.Query().Having(db.GroupTestMaster.GroupTestDetail.Date.Max() <
  234. new DateTime(2009, 1, 1))
  235. .FirstOrDefault();
  236. Assert.IsNotNull(row);
  237. Assert.AreEqual("One", row.Name);
  238. }
  239. [Test]
  240. public void HavingWithCountShouldReturnCorrectRow()
  241. {
  242. var db = DatabaseHelper.Open();
  243. var row = db.GroupTestMaster.Query()
  244. .Having(db.GroupTestMaster.GroupTestDetail.Id.Count() == 2)
  245. .FirstOrDefault();
  246. Assert.IsNotNull(row);
  247. Assert.AreEqual("Two", row.Name);
  248. }
  249. [Test]
  250. public void HavingWithAverageShouldReturnCorrectRow()
  251. {
  252. var db = DatabaseHelper.Open();
  253. var row = db.GroupTestMaster.Query()
  254. .Having(db.GroupTestMaster.GroupTestDetail.Number.Average() == 2)
  255. .FirstOrDefault();
  256. Assert.IsNotNull(row);
  257. Assert.AreEqual("One", row.Name);
  258. }
  259. [Test]
  260. public void ToScalarOrDefault()
  261. {
  262. var db = DatabaseHelper.Open();
  263. int max = db.Users.FindAllByName("ZXCVBNM").Select(db.Users.Age.Max()).ToScalarOrDefault<int>();
  264. Assert.AreEqual(0, max);
  265. }
  266. [Test]
  267. public void QueryWithSchemaQualifiedTableName()
  268. {
  269. var db = DatabaseHelper.Open();
  270. var result = db.test.SchemaTable.QueryById(2)
  271. .Select(db.test.SchemaTable.Id,
  272. db.test.SchemaTable.Description)
  273. .Single();
  274. Assert.AreEqual(2, result.Id);
  275. Assert.AreEqual("Pass", result.Description);
  276. }
  277. [Test]
  278. public void QueryWithSchemaQualifiedTableNameAndAliases()
  279. {
  280. var db = DatabaseHelper.Open();
  281. var result = db.test.SchemaTable.QueryById(2)
  282. .Select(db.test.SchemaTable.Id.As("This"),
  283. db.test.SchemaTable.Description.As("That"))
  284. .Single();
  285. Assert.AreEqual(2, result.This);
  286. Assert.AreEqual("Pass", result.That);
  287. }
  288. }
  289. }