5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
28 using System.Collections.Generic;
\r
30 using System.Linq.Expressions;
\r
32 using NUnit.Framework;
\r
34 using Test_NUnit.Linq_101_Samples;
\r
38 // test ns Linq_101_Samples
\r
40 namespace Test_NUnit_MySql.Linq_101_Samples
\r
42 namespace Test_NUnit_OracleODP.Linq_101_Samples
\r
44 namespace Test_NUnit_Oracle.Linq_101_Samples
\r
46 namespace Test_NUnit_PostgreSql.Linq_101_Samples
\r
48 namespace Test_NUnit_Sqlite.Linq_101_Samples
\r
50 namespace Test_NUnit_Ingres.Linq_101_Samples
\r
51 #elif MSSQL && L2SQL
\r
52 namespace Test_NUnit_MsSql_Strict.Linq_101_Samples
\r
54 namespace Test_NUnit_MsSql.Linq_101_Samples
\r
56 namespace Test_NUnit_Firebird.Linq_101_Samples
\r
60 /// Source: http://msdn2.microsoft.com/en-us/vbasic/bb737920.aspx
\r
61 /// manually translated from VB into C#.
\r
64 public class AdvancedTest : TestBase
\r
66 #if !DEBUG && POSTGRES
\r
69 [Test(Description = "This sample builds a query dynamically to return the contact name of each customer.")]
\r
70 public void LinqToSqlAdvanced01()
\r
72 Northwind db = CreateDB();
\r
74 ParameterExpression param = Expression.Parameter(typeof(Customer), "c");
\r
75 Expression selector = Expression.Property(param, typeof(Customer).GetProperty("ContactName"));
\r
76 var pred = Expression.Lambda(selector, param);
\r
78 var custs = db.Customers;
\r
79 var expr = Expression.Call(typeof(Queryable), "Select"
\r
80 , new Type[] { typeof(Customer), typeof(string) }, Expression.Constant(custs), pred);
\r
81 var query = db.Customers.AsQueryable().Provider.CreateQuery<string>(expr);
\r
83 var list = query.ToList();
\r
84 Assert.IsTrue(list.Count > 0);
\r
89 #if !DEBUG && POSTGRES
\r
92 [Test(Description = "This sample builds a query dynamically to filter for Customers in London.")]
\r
93 public void LinqToSqlAdvanced02()
\r
95 Northwind db = CreateDB();
\r
97 var custs = db.Customers;
\r
98 var param = Expression.Parameter(typeof(Customer), "c");
\r
99 var right = Expression.Constant("London");
\r
100 var left = Expression.Property(param, typeof(Customer).GetProperty("City"));
\r
101 var filter = Expression.Equal(left, right);
\r
102 var pred = Expression.Lambda(filter, param);
\r
104 var expr = Expression.Call(typeof(Queryable), "Where", new Type[] { typeof(Customer) }, Expression.Constant(custs), pred);
\r
105 var query = db.Customers.AsQueryable().Provider.CreateQuery<Customer>(expr);
\r
107 var list = query.ToList();
\r
108 Assert.IsTrue(list.Count > 0, "Got London citiens > 0");
\r
111 #if !DEBUG && POSTGRES
\r
114 [Test(Description = "This sample builds a query dynamically to filter for Customers in London and order them by ContactName.")]
\r
115 public void LinqToSqlAdvanced03()
\r
117 Northwind db = CreateDB();
\r
119 var param = Expression.Parameter(typeof(Customer), "c");
\r
121 var left = Expression.Property(param, typeof(Customer).GetProperty("City"));
\r
122 var right = Expression.Constant("London");
\r
123 var filter = Expression.Equal(left, right);
\r
124 var pred = Expression.Lambda(filter, param);
\r
126 var selector = Expression.Property(param, typeof(Customer).GetProperty("ContactName"));
\r
127 IQueryable custs = db.Customers;
\r
128 var expr = Expression.Call(typeof(Queryable), "Where", new Type[] { typeof(Customer) }, Expression.Constant(custs), pred);
\r
129 expr = Expression.Call(typeof(Queryable), "OrderBy", new Type[] { typeof(Customer), typeof(String) }, custs.Expression, Expression.Lambda(Expression.Property(param, "ContactName"), param));
\r
130 var query = db.Customers.AsQueryable().Provider.CreateQuery<Customer>(expr);
\r
132 var list = query.ToList();
\r
133 Assert.IsTrue(list.Count > 0);
\r
136 #if !DEBUG && POSTGRES
\r
139 [Test(Description = "This sample dynamically builds a Union to return a sequence of all countries where either a customer or an employee live.")]
\r
140 public void LinqToSqlAdvanced04()
\r
142 Northwind db = CreateDB();
\r
144 var custs = db.Customers;
\r
145 var param1 = Expression.Parameter(typeof(Customer), "e");
\r
146 var left1 = Expression.Property(param1, typeof(Customer).GetProperty("City"));
\r
147 var pred1 = Expression.Lambda(left1, param1);
\r
149 var employees = db.Employees;
\r
150 var param2 = Expression.Parameter(typeof(Employee), "c");
\r
151 var left2 = Expression.Property(param2, typeof(Employee).GetProperty("City"));
\r
152 var pred2 = Expression.Lambda(left2, param2);
\r
154 var expr1 = Expression.Call(typeof(Queryable), "Select", new Type[] { typeof(Customer), typeof(String) }, Expression.Constant(custs), pred1);
\r
155 var expr2 = Expression.Call(typeof(Queryable), "Select", new Type[] { typeof(Employee), typeof(String) }, Expression.Constant(employees), pred2);
\r
157 var q1 = db.Customers.AsQueryable().Provider.CreateQuery<String>(expr1);
\r
158 var q2 = db.Employees.AsQueryable().Provider.CreateQuery<String>(expr2);
\r
160 var q3 = q1.Union(q2);
\r
162 AssertHelper.Greater(q1.Count(), 0);
\r
163 Assert.IsTrue(q1.Count() + q2.Count() >= q3.Count());
\r
168 [Linq101SamplesModified("Replaced Contact by Customer")]
\r
169 [Test(Description="This sample demonstrates how we insert a new Contact and retrieve the newly assigned ContactID from the database.")]
\r
170 public void LinqToSqlAdvanced05()
\r
172 Northwind db = CreateDB();
\r
174 //PK Column should be autogenerated
\r
175 var con = new Category() { CategoryName = "New Era", Description= "(123)-456-7890" };
\r
176 db.Categories.InsertOnSubmit(con);
\r
179 db.SubmitChanges();
\r
181 Console.WriteLine();
\r
182 Console.WriteLine("The Category of the new record is {0}", con.CategoryID);
\r
184 Category customerReloaded=db.Categories.First(c=>c.CategoryID==con.CategoryID);
\r
185 Assert.AreEqual(customerReloaded.CategoryName, con.CategoryName);
\r
186 Assert.AreEqual(customerReloaded.Description, con.Description);
\r
189 db.Categories.DeleteOnSubmit(con);
\r
190 db.SubmitChanges();
\r
194 #if !DEBUG && (MSSQL && !L2SQL)
\r
197 [Test(Description = "This sample uses orderbyDescending and Take to return the discontinued products of the top 10 most expensive products")]
\r
198 public void LinqToSqlAdvanced06()
\r
200 Northwind db = CreateDB();
\r
202 var prods = from p in db.Products.OrderByDescending(p=> p.UnitPrice).Take(10)
\r
203 where p.Discontinued == "Y" select p;
\r
205 var prods = from p in db.Products.OrderByDescending(p => p.UnitPrice).Take(10)
\r
206 where !p.Discontinued
\r
210 var list = prods.ToList();
\r
211 Assert.IsTrue(list.Count > 0);
\r