2 using System.Collections.Generic;
\r
5 using NUnit.Framework;
\r
10 // test ns Linq_101_Samples
\r
12 namespace Test_NUnit_MySql.Linq_101_Samples
\r
14 namespace Test_NUnit_OracleODP.Linq_101_Samples
\r
16 namespace Test_NUnit_Oracle.Linq_101_Samples
\r
18 namespace Test_NUnit_PostgreSql.Linq_101_Samples
\r
20 namespace Test_NUnit_Sqlite.Linq_101_Samples
\r
22 namespace Test_NUnit_Ingres.Linq_101_Samples
\r
23 #elif MSSQL && MONO_STRICT
\r
24 namespace Test_NUnit_MsSql_Strict.Linq_101_Samples
\r
26 namespace Test_NUnit_MsSql.Linq_101_Samples
\r
28 namespace Test_NUnit_Firebird.Linq_101_Samples
\r
32 /// Source: http://msdn2.microsoft.com/en-us/vbasic/bb737922.aspx
\r
33 /// manually translated from VB into C#.
\r
36 public class Count_Sum_Min_Max_Avg : TestBase
\r
39 public void LinqToSqlCount01()
\r
41 Northwind db = CreateDB();
\r
42 var q = db.Customers.Count();
\r
44 Assert.IsTrue(q > 0, "Expect non-zero count");
\r
48 public void LinqToSqlCount02()
\r
50 Northwind db = CreateDB();
\r
51 #if INGRES && !MONO_STRICT
\r
52 var q = (from p in db.Products where p.Discontinued == 0 select p)
\r
55 var q = (from p in db.Products where !p.Discontinued select p)
\r
59 Assert.IsTrue(q > 0, "Expect non-zero count");
\r
62 [Test(Description = "This sample uses Sum to find the total freight over all Orders.")]
\r
63 public void LinqToSqlCount03()
\r
65 Northwind db = CreateDB();
\r
66 var q = (from o in db.Orders select o.Freight).Sum();
\r
67 Assert.IsTrue(q > 0, "Freight sum must be > 0");
\r
70 [Test(Description = "This sample uses Sum to find the total number of units on order over all Products.")]
\r
71 public void LinqToSqlCount04()
\r
73 Northwind db = CreateDB();
\r
74 var q = (from p in db.Products select (int)p.UnitsOnOrder.Value).Sum();
\r
75 Assert.IsTrue(q > 0, "Freight sum must be > 0");
\r
78 [Test(Description = "This sample uses Min to find the lowest unit price of any Product")]
\r
79 public void LinqToSqlCount05()
\r
81 Northwind db = CreateDB();
\r
82 var q = (from p in db.Products select p.UnitsOnOrder).Min();
\r
83 Assert.IsTrue(q == 0, "Min UnitsOnOrder must be 0");
\r
86 [Test(Description = "This sample uses Min to find the lowest freight of any Order.")]
\r
87 public void LinqToSqlCount06()
\r
89 Northwind db = CreateDB();
\r
90 var q = (from o in db.Orders select o.Freight).Min();
\r
91 Assert.IsTrue(q > 0, "Freight sum must be > 0");
\r
94 [Test(Description = "This sample uses Min to find the Products that have the lowest unit price in each category")]
\r
95 public void LinqToSqlCount07()
\r
97 #region SHOW_MICROSOFT_GENERATED_SQL
\r
99 //the one Linq statement below gets translated into 9 SQL statements
\r
100 SELECT MIN([t0].[UnitPrice]) AS [value], [t0].[CategoryID]
\r
101 FROM [dbo].[Products] AS [t0]
\r
102 GROUP BY [t0].[CategoryID]
\r
104 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
105 FROM [dbo].[Products] AS [t0]
\r
106 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
107 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
\r
108 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [4.5000]
\r
110 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
111 FROM [dbo].[Products] AS [t0]
\r
112 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
113 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
\r
114 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [10.0000]
\r
116 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
117 FROM [dbo].[Products] AS [t0]
\r
118 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
119 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
\r
120 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [9.2000]
\r
122 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
123 FROM [dbo].[Products] AS [t0]
\r
124 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
125 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
\r
126 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [2.5000]
\r
128 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
129 FROM [dbo].[Products] AS [t0]
\r
130 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
131 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [5]
\r
132 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [7.0000]
\r
134 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
135 FROM [dbo].[Products] AS [t0]
\r
136 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
137 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [6]
\r
138 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [7.4500]
\r
140 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
141 FROM [dbo].[Products] AS [t0]
\r
142 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
143 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [7]
\r
144 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [10.0000]
\r
146 SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
\r
147 FROM [dbo].[Products] AS [t0]
\r
148 WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
\r
149 -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [8]
\r
150 -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [6.0000]
\r
154 Northwind db = CreateDB();
\r
155 var categories = (from p in db.Products
\r
156 group p by p.CategoryID into g
\r
159 CategoryID = g.Key,
\r
160 CheapestProducts = from p2 in g
\r
161 where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
\r
165 var list = categories.ToList();
\r
166 Assert.IsTrue(list.Count > 0, "Expected count > 0");
\r
169 [Test(Description = "This sample uses Max to find the latest hire date of any Employee")]
\r
170 public void LinqToSqlCount08()
\r
172 Northwind db = CreateDB();
\r
173 var q = (from e in db.Employees select e.HireDate).Max();
\r
174 Assert.IsTrue(q > new DateTime(1990, 1, 1), "Hire date must be > 2000");
\r
177 [Test(Description = "This sample uses Max to find the most units in stock of any Product")]
\r
178 public void LinqToSqlCount09()
\r
180 Northwind db = CreateDB();
\r
181 var q = (from p in db.Products select p.UnitsInStock).Max();
\r
182 Assert.IsTrue(q > 0, "Max UnitsInStock must be > 0");
\r
185 [Test(Description = "This sample uses Max to find the Products that have the highest unit price in each category")]
\r
186 public void LinqToSqlCount10()
\r
188 //Miscrosoft translates this query into multiple SQL statements
\r
189 Northwind db = CreateDB();
\r
190 var q = from p in db.Products
\r
191 group p by p.CategoryID into g
\r
195 MostExpensiveProducts = from p2 in g
\r
196 where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
\r
199 var list = q.ToList();
\r
200 Assert.IsTrue(list.Count > 0, "Got most expensive items > 0");
\r
205 [Test(Description = "This sample uses Average to find the average freight of all Orders.")]
\r
206 public void LinqToSqlCount11()
\r
208 Northwind db = CreateDB();
\r
209 var q = (from o in db.Orders
\r
210 select o.Freight).Average();
\r
212 Console.WriteLine(q);
\r
213 Assert.IsTrue(q > 0, "Avg orders'freight must be > 0");
\r
216 [Test(Description = "This sample uses Average to find the average unit price of all Products.")]
\r
217 public void LinqToSqlCount12()
\r
219 Northwind db = CreateDB();
\r
220 var q = (from p in db.Products
\r
221 select p.UnitPrice).Average();
\r
223 Console.WriteLine(q);
\r
225 Console.WriteLine(q);
\r
226 Assert.IsTrue(q > 0, "Avg products'unitPrice must be > 0");
\r
230 [Test(Description = "This sample uses Average to find the Products that have unit price higher than the average unit price of the category for each category.")]
\r
231 public void LinqToSqlCount13()
\r
233 Northwind db = CreateDB();
\r
234 var categories = from p in db.Products
\r
235 group p by p.CategoryID into g
\r
239 ExpensiveProducts = from p2 in g
\r
240 where (p2.UnitPrice > g.Average(p3 => p3.UnitPrice))
\r
245 var list = categories.ToList();
\r
246 Assert.IsTrue(list.Count > 0, "Got categorized products > 0");
\r