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
27 using System.Globalization;
\r
28 using System.Collections.Generic;
\r
31 using System.Linq.Expressions;
\r
32 using NUnit.Framework;
\r
38 using System.Data.Linq;
\r
40 using DbLinq.Data.Linq;
\r
45 namespace Test_NUnit_MySql
\r
47 namespace Test_NUnit_OracleODP
\r
49 namespace Test_NUnit_Oracle
\r
51 namespace Test_NUnit_PostgreSql
\r
53 namespace Test_NUnit_Sqlite
\r
55 namespace Test_NUnit_Ingres
\r
56 #elif MSSQL && L2SQL
\r
57 namespace Test_NUnit_MsSql_Strict
\r
59 namespace Test_NUnit_MsSql
\r
61 namespace Test_NUnit_Firebird
\r
65 public class ReadTests_Join : TestBase
\r
68 #if !DEBUG && (SQLITE || (MSSQL && !L2SQL))
\r
71 [Test(Description = "example by Frans Brouma: select all customers that have no orders")]
\r
72 public void LeftJoin_DefaultIfEmpty()
\r
74 //example by Frans Brouma on Matt Warren's site
\r
75 //select all customers that have no orders
\r
76 //http://blogs.msdn.com/mattwar/archive/2007/09/04/linq-building-an-iqueryable-provider-part-vii.aspx
\r
77 //http://weblogs.asp.net/fbouma/archive/2007/11/23/developing-linq-to-llblgen-pro-part-9.aspx
\r
79 Northwind db = CreateDB();
\r
81 var q = from c in db.Customers
\r
82 join o in db.Orders on c.CustomerID equals o.CustomerID into oc
\r
83 from x in oc.DefaultIfEmpty()
\r
84 where x.OrderID == null
\r
87 var list = q.ToList();
\r
88 Assert.IsTrue(list.Count > 0);
\r
89 int countPARIS = list.Count(item => item.CustomerID == "PARIS");
\r
90 Assert.IsTrue(countPARIS == 1);
\r
94 public void LeftOuterJoin_Suppliers()
\r
96 //http://blogs.class-a.nl/blogs/anko/archive/2008/03/14/linq-to-sql-outer-joins.aspx
\r
97 //example by Anko Duizer (NL)
\r
98 Northwind db = CreateDB();
\r
99 var query = from s in db.Suppliers
\r
100 join c in db.Customers on s.City equals c.City into temp
\r
101 from t in temp.DefaultIfEmpty()
\r
104 SupplierName = s.CompanyName,
\r
105 CustomerName = t.CompanyName,
\r
109 var list = query.ToList();
\r
111 bool foundMelb = false, foundNull = false;
\r
112 foreach (var item in list)
\r
114 foundMelb = foundMelb || item.City == "Melbourne";
\r
115 foundNull = foundNull || item.City == null;
\r
117 Assert.IsTrue(foundMelb, "Expected rows with City=Melbourne");
\r
118 Assert.IsFalse(foundNull, "Expected no rows with City=null");
\r
121 // picrap: commented out, it doesn't build because of db.Orderdetails (again, a shared source file...)
\r
123 [Test(Description = "Problem discovered by Laurent")]
\r
124 public void Join_Laurent()
\r
126 Northwind db = CreateDB();
\r
128 var q1 = (from p in db.Products
\r
129 join o in db.OrderDetails on p.ProductID equals o.ProductID
\r
130 where p.ProductID > 1
\r
139 Assert.IsTrue(q1.Count > 0);
\r
142 #if !DEBUG && (SQLITE || MSSQL)
\r
143 // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTests_Join+Northwind1+ExtendedOrder' is not mapped as a Table.
\r
147 public void RetrieveParentAssociationProperty()
\r
149 Northwind dbo = CreateDB();
\r
150 Northwind1 db = new Northwind1(dbo.Connection);
\r
151 var t = db.GetTable<Northwind1.ExtendedOrder>();
\r
152 var q = from order in t
\r
156 order.CustomerShipCity.ContactName
\r
158 var list = q.ToList();
\r
159 Assert.IsTrue(list.Count > 0);
\r
164 #if !DEBUG && (SQLITE || MSSQL)
\r
165 // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTests_Join+Northwind1+ExtendedOrder' is not mapped as a Table.
\r
169 public void DifferentParentAndAssociationPropertyNames()
\r
171 Northwind dbo = CreateDB();
\r
172 Northwind1 db = new Northwind1(dbo.Connection);
\r
173 var query = db.GetTable<Northwind1.ExtendedOrder>() as IQueryable<Northwind1.ExtendedOrder>;
\r
175 var q2 = query.Select(e => new Northwind1.ExtendedOrder
\r
177 OrderID = e.OrderID,
\r
178 ShipAddress = e.CustomerShipCity.ContactName
\r
180 var list = q2.ToList();
\r
181 Assert.IsTrue(list.Count > 0);
\r
184 #if !DEBUG && (SQLITE || MSSQL)
\r
185 // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTests_Join+Northwind1+ExtendedOrder' is not mapped as a Table.
\r
189 public void SelectCustomerContactNameFromOrder()
\r
191 Northwind dbo = CreateDB();
\r
192 Northwind1 db = new Northwind1(dbo.Connection);
\r
193 var t = db.GetTable<Northwind1.ExtendedOrder>();
\r
195 var q = from order in t
\r
198 order.CustomerContactName
\r
200 var list = q.ToList();
\r
201 Assert.AreEqual(db.Orders.Count(), list.Count());
\r
202 foreach (var s in list)
\r
203 Assert.AreEqual("Test", s);
\r
206 public class Northwind1 : Northwind
\r
208 public Northwind1(System.Data.IDbConnection connection)
\r
209 : base(connection) { }
\r
211 // Linq-SQL requires this: [System.Data.Linq.Mapping.Table(Name = "orders")]
\r
212 public class ExtendedOrder : Order
\r
219 .EntityRef<Customer> _x_Customer;
\r
221 [System.Data.Linq.Mapping.Association(Storage = "_x_Customer",
\r
222 ThisKey = "ShipCity", Name =
\r
228 "fk_order_customer"
\r
232 "fk_order_customer"
\r
234 "fk_order_customer"
\r
236 "??" // TODO: correct FK name
\r
238 #error unknown target
\r
241 public Customer CustomerShipCity
\r
243 get { return _x_Customer.Entity; }
\r
244 set { _x_Customer.Entity = value; }
\r
247 public string CustomerContactName
\r
256 public Table<ExtendedOrder> ExtendedOrders
\r
258 get { return base.GetTable<ExtendedOrder>(); }
\r
263 [ExpectedException(typeof(NotSupportedException))]
\r
264 public void WhereBeforeSelect()
\r
266 Northwind db = CreateDB();
\r
267 var t = db.GetTable<Order>();
\r
269 var query = t.Where(o => o.OrderID != 0);
\r
271 query = query.Select(dok => new Order
\r
273 OrderID = dok.OrderID,
\r
274 OrderDate = dok.OrderDate,
\r
275 ShipCity = dok.Customer.ContactName,
\r
276 Freight = dok.Freight
\r
278 var list = query.ToList();
\r
282 /// Reported by pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=66
\r
285 public void OrdersLazyLoad()
\r
287 Northwind db = CreateDB();
\r
290 from c in db.Customers
\r
293 foreach (var c in q)
\r
295 Console.WriteLine(c.Address);
\r
296 foreach (var o in c.Orders)
\r
297 Console.WriteLine(o.OrderID);
\r
303 public void JoinWhere()
\r
305 Northwind db = CreateDB();
\r
307 var custID = "BT___";
\r
309 var custOderInfos = from o in db.Orders
\r
310 join em in db.Employees on o.EmployeeID equals em.EmployeeID
\r
311 where o.CustomerID == custID
\r
312 select new { o, em };
\r
314 var l = custOderInfos.ToList();
\r
317 #if !DEBUG && (SQLITE || (MSSQL && !L2SQL))
\r
321 // submitted by bryan costanich
\r
322 public void ImplicitLeftOuterJoin()
\r
324 var db = CreateDB();
\r
327 (from a in db.Products
\r
328 from b in db.Suppliers
\r
329 where a.SupplierID == b.SupplierID
\r
332 var list = dbItems.ToList();
\r