PageRenderTime 19ms CodeModel.GetById 1ms app.highlight 14ms RepoModel.GetById 1ms app.codeStats 0ms

/mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/ReadTests_Join.cs

https://github.com/ekovalenko-softheme/mono
C# | 336 lines | 256 code | 41 blank | 39 comment | 28 complexity | 9f53ce86da75f9ab3bd53c7215f37ae4 MD5 | raw file
  1#region MIT license
  2// 
  3// MIT license
  4//
  5// Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
  6// 
  7// Permission is hereby granted, free of charge, to any person obtaining a copy
  8// of this software and associated documentation files (the "Software"), to deal
  9// in the Software without restriction, including without limitation the rights
 10// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 11// copies of the Software, and to permit persons to whom the Software is
 12// furnished to do so, subject to the following conditions:
 13// 
 14// The above copyright notice and this permission notice shall be included in
 15// all copies or substantial portions of the Software.
 16// 
 17// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 18// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 19// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 20// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 21// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 22// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 23// THE SOFTWARE.
 24// 
 25#endregion
 26using System;
 27using System.Globalization;
 28using System.Collections.Generic;
 29using System.Text;
 30using System.Linq;
 31using System.Linq.Expressions;
 32using NUnit.Framework;
 33using Test_NUnit;
 34
 35using nwind;
 36
 37#if MONO_STRICT
 38using System.Data.Linq;
 39#else
 40using DbLinq.Data.Linq;
 41#endif
 42
 43// test ns 
 44#if MYSQL
 45    namespace Test_NUnit_MySql
 46#elif ORACLE && ODP
 47    namespace Test_NUnit_OracleODP
 48#elif ORACLE
 49    namespace Test_NUnit_Oracle
 50#elif POSTGRES
 51    namespace Test_NUnit_PostgreSql
 52#elif SQLITE
 53    namespace Test_NUnit_Sqlite
 54#elif INGRES
 55    namespace Test_NUnit_Ingres
 56#elif MSSQL && L2SQL
 57    namespace Test_NUnit_MsSql_Strict
 58#elif MSSQL
 59    namespace Test_NUnit_MsSql
 60#elif FIREBIRD
 61    namespace Test_NUnit_Firebird
 62#endif
 63{
 64    [TestFixture]
 65    public class ReadTests_Join : TestBase
 66    {
 67
 68#if !DEBUG && (SQLITE || (MSSQL && !L2SQL))
 69        [Explicit]
 70#endif
 71        [Test(Description = "example by Frans Brouma: select all customers that have no orders")]
 72        public void LeftJoin_DefaultIfEmpty()
 73        {
 74            //example by Frans Brouma on Matt Warren's site
 75            //select all customers that have no orders
 76            //http://blogs.msdn.com/mattwar/archive/2007/09/04/linq-building-an-iqueryable-provider-part-vii.aspx
 77            //http://weblogs.asp.net/fbouma/archive/2007/11/23/developing-linq-to-llblgen-pro-part-9.aspx
 78
 79            Northwind db = CreateDB();
 80
 81            var q = from c in db.Customers
 82                    join o in db.Orders on c.CustomerID equals o.CustomerID into oc
 83                    from x in oc.DefaultIfEmpty()
 84                    where x.OrderID == null
 85                    select c;
 86
 87            var list = q.ToList();
 88            Assert.IsTrue(list.Count > 0);
 89            int countPARIS = list.Count(item => item.CustomerID == "PARIS");
 90            Assert.IsTrue(countPARIS == 1);
 91        }
 92
 93        [Test]
 94        public void LeftOuterJoin_Suppliers()
 95        {
 96            //http://blogs.class-a.nl/blogs/anko/archive/2008/03/14/linq-to-sql-outer-joins.aspx
 97            //example by Anko Duizer (NL)
 98            Northwind db = CreateDB();
 99            var query = from s in db.Suppliers
100                        join c in db.Customers on s.City equals c.City into temp
101                        from t in temp.DefaultIfEmpty()
102                        select new
103                        {
104                            SupplierName = s.CompanyName,
105                            CustomerName = t.CompanyName,
106                            City = s.City
107                        };
108
109            var list = query.ToList();
110
111            bool foundMelb = false, foundNull = false;
112            foreach (var item in list)
113            {
114                foundMelb = foundMelb || item.City == "Melbourne";
115                foundNull = foundNull || item.City == null;
116            }
117            Assert.IsTrue(foundMelb, "Expected rows with City=Melbourne");
118            Assert.IsFalse(foundNull, "Expected no rows with City=null");
119        }
120
121        // picrap: commented out, it doesn't build because of db.Orderdetails (again, a shared source file...)
122
123        [Test(Description = "Problem discovered by Laurent")]
124        public void Join_Laurent()
125        {
126            Northwind db = CreateDB();
127
128            var q1 = (from p in db.Products
129                      join o in db.OrderDetails on p.ProductID equals o.ProductID
130                      where p.ProductID > 1
131                      select new
132                      {
133                          p.ProductName,
134                          o.OrderID,
135                          o.ProductID,
136                      }
137                      ).ToList();
138
139            Assert.IsTrue(q1.Count > 0);
140        }
141
142#if !DEBUG && (SQLITE || POSTGRES || MSSQL)
143        // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTests_Join+Northwind1+ExtendedOrder' is not mapped as a Table.
144        [Explicit]
145#endif
146        [Test]
147        public void RetrieveParentAssociationProperty()
148        {
149            Northwind dbo = CreateDB();
150            Northwind1 db = new Northwind1(dbo.Connection);
151            var t = db.GetTable<Northwind1.ExtendedOrder>();
152            var q = from order in t
153                    select new
154                    {
155                        order.OrderID,
156                        order.CustomerShipCity.ContactName
157                    };
158            var list = q.ToList();
159            Assert.IsTrue(list.Count > 0);
160        }
161
162
163
164#if !DEBUG && (SQLITE || POSTGRES || MSSQL)
165        // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTests_Join+Northwind1+ExtendedOrder' is not mapped as a Table.
166        [Explicit]
167#endif
168        [Test]
169        public void DifferentParentAndAssociationPropertyNames()
170        {
171            Northwind dbo = CreateDB();
172            Northwind1 db = new Northwind1(dbo.Connection);
173            var query = db.GetTable<Northwind1.ExtendedOrder>() as IQueryable<Northwind1.ExtendedOrder>;
174
175            var q2 = query.Select(e => new Northwind1.ExtendedOrder
176            {
177                OrderID = e.OrderID,
178                ShipAddress = e.CustomerShipCity.ContactName
179            });
180            var list = q2.ToList();
181            Assert.IsTrue(list.Count > 0);
182        }
183
184#if !DEBUG && (SQLITE || POSTGRES || MSSQL)
185        // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTests_Join+Northwind1+ExtendedOrder' is not mapped as a Table.
186        [Explicit]
187#endif
188        [Test]
189        public void SelectCustomerContactNameFromOrder()
190        {
191            Northwind dbo = CreateDB();
192            Northwind1 db = new Northwind1(dbo.Connection);
193            var t = db.GetTable<Northwind1.ExtendedOrder>();
194
195            var q = from order in t
196                    select new
197                    {
198                        order.CustomerContactName
199                    };
200            var list = q.ToList();
201            Assert.AreEqual(db.Orders.Count(), list.Count());
202            foreach (var s in list)
203                Assert.AreEqual("Test", s);
204        }
205
206        public class Northwind1 : Northwind
207        {
208            public Northwind1(System.Data.IDbConnection connection)
209                : base(connection) { }
210
211            // Linq-SQL requires this: [System.Data.Linq.Mapping.Table(Name = "orders")]
212            public class ExtendedOrder : Order
213            {
214#if MONO_STRICT
215                System.Data.Linq
216#else
217                DbLinq.Data.Linq
218#endif
219.EntityRef<Customer> _x_Customer;
220
221                [System.Data.Linq.Mapping.Association(Storage = "_x_Customer",
222                    ThisKey = "ShipCity", Name =
223#if MYSQL
224 "orders_ibfk_1"
225#elif ORACLE
226 "SYS_C004742"
227#elif POSTGRES
228 "fk_order_customer"
229#elif SQLITE
230 "fk_Orders_1"
231#elif INGRES
232 "fk_order_customer"
233#elif MSSQL
234 "fk_order_customer"
235#elif FIREBIRD
236 "??" // TODO: correct FK name
237#else
238#error unknown target
239#endif
240)]
241                public Customer CustomerShipCity
242                {
243                    get { return _x_Customer.Entity; }
244                    set { _x_Customer.Entity = value; }
245                }
246
247                public string CustomerContactName
248                {
249                    get
250                    {
251                        return "Test";
252                    }
253                }
254            }
255
256            public Table<ExtendedOrder> ExtendedOrders
257            {
258                get { return base.GetTable<ExtendedOrder>(); }
259            }
260        }
261
262        [Test]
263        [ExpectedException(typeof(NotSupportedException))]
264        public void WhereBeforeSelect()
265        {
266            Northwind db = CreateDB();
267            var t = db.GetTable<Order>();
268
269            var query = t.Where(o => o.OrderID != 0);
270
271            query = query.Select(dok => new Order
272            {
273                OrderID = dok.OrderID,
274                OrderDate = dok.OrderDate,
275                ShipCity = dok.Customer.ContactName,
276                Freight = dok.Freight
277            });
278            var list = query.ToList();
279        }
280
281        /// <summary>
282        /// Reported by  pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=66
283        /// </summary>
284        [Test]
285        public void OrdersLazyLoad()
286        {
287            Northwind db = CreateDB();
288
289            var q =
290              from c in db.Customers
291              select c;
292
293            foreach (var c in q)
294            {
295                Console.WriteLine(c.Address);
296                foreach (var o in c.Orders)
297                    Console.WriteLine(o.OrderID);
298            }
299
300        }
301
302        [Test]
303        public void JoinWhere()
304        {
305            Northwind db = CreateDB();
306
307            var custID = "BT___";
308
309            var custOderInfos = from o in db.Orders
310                                join em in db.Employees on o.EmployeeID equals em.EmployeeID
311                                where o.CustomerID == custID
312                                select new { o, em };
313
314            var l = custOderInfos.ToList();
315        }
316
317#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
318        [Explicit]
319#endif
320        [Test]
321        // submitted by bryan costanich
322        public void ImplicitLeftOuterJoin()
323        {
324            var db = CreateDB();
325
326            var dbItems =
327                    (from a in db.Products
328                     from b in db.Suppliers
329                     where a.SupplierID == b.SupplierID
330                     select a);
331
332            var list = dbItems.ToList();
333        }
334    }
335
336}