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

   1 #region MIT license\r
   2 // \r
   3 // MIT license\r
   4 //\r
   5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne\r
   6 // \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
  13 // \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
  16 // \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
  23 // THE SOFTWARE.\r
  24 // \r
  25 #endregion\r
  26 using System;\r
  27 using System.Collections.Generic;\r
  28 using System.Linq;\r
  29 using System.Text;\r
  30 using NUnit.Framework;\r
  31 using System.Linq.Dynamic;\r
  32 using Test_NUnit;\r
  33 using System.Linq.Expressions;\r
  34 using System.Reflection;\r
  35 \r
  36 using nwind;\r
  37 \r
  38 // test ns \r
  39 #if MYSQL\r
  40     namespace Test_NUnit_MySql\r
  41 #elif ORACLE && ODP\r
  42     namespace Test_NUnit_OracleODP\r
  43 #elif ORACLE\r
  44     namespace Test_NUnit_Oracle\r
  45 #elif POSTGRES\r
  46     namespace Test_NUnit_PostgreSql\r
  47 #elif SQLITE\r
  48     namespace Test_NUnit_Sqlite\r
  49 #elif INGRES\r
  50     namespace Test_NUnit_Ingres\r
  51 #elif MSSQL && L2SQL\r
  52     namespace Test_NUnit_MsSql_Strict\r
  53 #elif MSSQL\r
  54     namespace Test_NUnit_MsSql\r
  55 #elif FIREBIRD\r
  56     namespace Test_NUnit_Firebird\r
  57 #endif\r
  58 {\r
  59     [TestFixture]\r
  60     public class DynamicLinqTest : TestBase\r
  61     {\r
  62         [Test]\r
  63         public void DL1_Products()\r
  64         {\r
  65             Northwind db = CreateDB();\r
  66 \r
  67             var q = db.Products.Where("SupplierID=1 And UnitsInStock>2")\r
  68                 .OrderBy("ProductID");\r
  69             var list = q.ToList();\r
  70             Assert.IsTrue(list.Count > 0, "Expected results from dynamic query");\r
  71         }\r
  72 \r
  73         [Test]\r
  74         public void DL2_ProductCount()\r
  75         {\r
  76             Northwind db = CreateDB();\r
  77 \r
  78             int numProducts = db.Products.Where("SupplierID=1").Count();\r
  79             Assert.IsTrue(numProducts > 0, "Expected results from dynamic query");\r
  80         }\r
  81 \r
  82         //note:\r
  83         //user Sqlite reports problems with DynamicLinq Count() -\r
  84         //but neither DL2 nor DL3 tests seem to hit the problem.\r
  85 \r
  86         [Test]\r
  87         public void DL3_ProductCount()\r
  88         {\r
  89             Northwind db = CreateDB();\r
  90 \r
  91             int numProducts = db.Products.Count();\r
  92             Assert.IsTrue(numProducts > 0, "Expected results from dynamic query");\r
  93         }\r
  94 \r
  95         [Test]\r
  96         public void DL4_DynamicAssociationProperty()\r
  97         {\r
  98 \r
  99             Northwind db = CreateDB();\r
 100             var orders = db.GetTable<Order>();\r
 101             var res = orders.Select(@"new (OrderID,Customer.ContactName)");\r
 102 \r
 103             List<object> list = new List<object>();\r
 104             foreach (var u in res)\r
 105                 list.Add(u);\r
 106             Assert.IsTrue(list.Count > 0);\r
 107         }\r
 108 \r
 109         #region NestedPropertiesDynamicSelect\r
 110 \r
 111         const string obsoleteError=@"Since beta2 in Linq2Sql to project a new entity (ie: select new Order(3)) is forbidden for coherence reasons, so this tests doesn't mimic the Linq2Sql behavior and it is obsolete and should be modified. If you apply such test cases to Linq2Sql you'll get Test_NUnit_MsSql_Strict.DynamicLinqTest.DL5_NestedObjectSelect:\r
 112         System.NotSupportedException : Explicit construction of entity type 'MsNorthwind.XX' in query is not allowed.\n\nMore Info in: http://linqinaction.net/blogs/roller/archive/2007/11/27/explicit-construction-of-entity-type-in-query-is-not-allowed.aspx";\r
 113         [Test(Description = "dynamic version of F16_NestedObjectSelect")]\r
 114         public void DL5_NestedObjectSelect()\r
 115         {\r
 116             Assert.Ignore(obsoleteError);\r
 117             Northwind db = CreateDB();\r
 118             var orders = db.GetTable<Order>();\r
 119             var res = orders.SelectNested(new string[] { "OrderID", "Customer.ContactName" });\r
 120 \r
 121             List<Order> list = res.ToList();\r
 122             Assert.IsTrue(list.Count > 0);\r
 123         }\r
 124 \r
 125         [Test]\r
 126         public void DL6_StaticVersionOfDynamicAssociatonWithExtensionMethodTest(bool bug_in_dynamic_linq)\r
 127         {\r
 128             Assert.Ignore(obsoleteError);\r
 129 \r
 130             //is this maybe a bug in DynamicLinq?\r
 131             //from DynamicLinq, we receive this query which has ContactName but misses ContactTitle:\r
 132             //MTable.CreateQuery: value(Table`1[Order]).Select(o => new Order() {OrderID = o.OrderID, Customer = new Customer() {ContactName = o.Customer.ContactName}})\r
 133 \r
 134             //Also - the non-dynamic version F17_NestedObjectSelect_Ver2 succeeds.\r
 135 \r
 136             Northwind db = CreateDB();\r
 137             var orders = db.GetTable<Order>().ToArray().AsQueryable();\r
 138 \r
 139             var query = from order in orders\r
 140                         //where order.Customer != null\r
 141                         select new Order\r
 142                         {\r
 143                             OrderID = order.OrderID,\r
 144                             Customer = new Customer\r
 145                             {\r
 146                                 ContactName = order.Customer.ContactName,\r
 147                                 ContactTitle = order.Customer.ContactTitle\r
 148                             }\r
 149                         };\r
 150             var list = query.ToList();\r
 151             Assert.IsTrue(list.Count > 0);\r
 152         }\r
 153 \r
 154         [Test]\r
 155         public void DL7_DynamicAssociatonUsingDoubleProjection(bool bug_in_dynamic_linq)\r
 156         {\r
 157             Assert.Ignore(obsoleteError);\r
 158 \r
 159             //this fails - but not in our code:\r
 160             //A first chance exception of type 'System.NullReferenceException' occurred in Unknown Module.\r
 161             //System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>Test_NUnit_Mysql.vshost.exe</AppDomain><Exception><ExceptionType>System.NullReferenceException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Object reference not set to an instance of an object.</Message><StackTrace>   at lambda_method(ExecutionScope , Order )\r
 162             //   at System.Linq.Enumerable.&amp;lt;SelectIterator&amp;gt;d__d`2.MoveNext()\r
 163             //   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r
 164             //   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r
 165             //   at Test_NUnit_MySql.DynamicLinqTest.DL7_DynamicAssociatonUsingDoubleProjection() in E:\ggprj\dbLinq\dblinq2007\Tests\Test_NUnit\DynamicLinqTest.cs:line 150\r
 166 \r
 167             Northwind db = CreateDB();\r
 168 \r
 169             // Double projection works in Linq-SQL:\r
 170             var orders = db.GetTable<Order>().ToArray().AsQueryable();\r
 171             var query = orders.SelectNested(new string[] { "OrderID", "Customer.ContactName" });\r
 172             var list = query.ToList();\r
 173             Assert.IsTrue(list.Count > 0);\r
 174         }\r
 175 \r
 176         #endregion\r
 177 \r
 178         #region Predicates\r
 179 \r
 180         /// <summary>\r
 181         /// Reported by pwy.mail in issue http://code.google.com/p/dblinq2007/issues/detail?id=68\r
 182         /// </summary>\r
 183         [Test]\r
 184         public void DL8_CountTest2()\r
 185         {\r
 186             Northwind db = CreateDB();\r
 187             Expression<Func<Customer, bool>> predicate = c => c.City == "Paris";\r
 188             int count = db.Customers.Count(predicate);\r
 189             Assert.Greater(count, 0); // Some databases have more than 1 customer in Paris\r
 190         }\r
 191 \r
 192         /// <summary>\r
 193         /// Reported by pwy.mail in issue http://code.google.com/p/dblinq2007/issues/detail?id=69\r
 194         /// </summary>\r
 195         [Test]\r
 196         public void DL9_PredicateBuilderCount()\r
 197         {\r
 198             //2008.May.17: breaks because we are not handling an 'InvocationExpression' in ExpressionTreeParser.\r
 199             //possibily a tree rewrite is needed.\r
 200             Northwind db = CreateDB();\r
 201             var predicate = PredicateBuilder.True<Customer>();\r
 202             predicate = predicate.And(m => m.City == "Paris");\r
 203             int predicateCount  = db.Customers.Count(predicate);\r
 204             int normalCount     = db.Customers.Where(c => c.City == "Paris").Count();\r
 205             Assert.AreEqual(normalCount, predicateCount);\r
 206         }\r
 207 \r
 208 \r
 209         /// <summary>\r
 210         /// Reported by pwy.mail in issue http://code.google.com/p/dblinq2007/issues/detail?id=69\r
 211         /// </summary>\r
 212         [Test]\r
 213         public void DL10_PredicateBuilderWhere()\r
 214         {\r
 215             Northwind db = CreateDB();\r
 216             var predicate = PredicateBuilder.True<Customer>();\r
 217 \r
 218             predicate = predicate.And(m => m.City == "Paris");\r
 219             predicate = predicate.And(n => n.CompanyName == "Around the Horn");\r
 220             IList<Customer> list = db.Customers.AsQueryable().Where(predicate).ToList();\r
 221         }\r
 222 \r
 223         /// <summary>\r
 224         /// Reported by pwy.mail in issue http://code.google.com/p/dblinq2007/issues/detail?id=73\r
 225         /// </summary>\r
 226         [Test]\r
 227         public void DL11_ThenByDescending()\r
 228         {\r
 229             Northwind db = CreateDB();\r
 230             var q = db.Products.Where("SupplierID=1 And UnitsInStock>2")\r
 231                 .OrderBy(" ProductName asc,ProductID desc");\r
 232             var list = q.ToList();\r
 233             Assert.IsTrue(list.Count > 0, "Expected results from dynamic query");\r
 234         }\r
 235 \r
 236         /// <summary>\r
 237         /// Build predicate expressions dynamically.\r
 238         /// </summary>\r
 239         static class PredicateBuilder\r
 240         {\r
 241             public static Expression<Func<T, bool>> True<T>() { return f => true; }\r
 242             public static Expression<Func<T, bool>> False<T>() { return f => false; }\r
 243         }\r
 244 \r
 245         // Test patch from:\r
 246         // http://groups.google.com/group/dblinq/browse_frm/thread/9fa41554044afeaa/4ef1605e020be9fc?lnk=raot#4ef1605e020be9fc\r
 247         [Test]\r
 248         public void DL12_Count_Via_Expression()\r
 249         {\r
 250             var db = CreateDB();\r
 251             IQueryable employees = db.Employees;\r
 252             var employeeCount = (int) employees.Provider.Execute(\r
 253                 Expression.Call(typeof(Queryable), "Count",\r
 254                     new Type[]{employees.ElementType},\r
 255                     employees.Expression));\r
 256             Assert.AreEqual(9, employeeCount);\r
 257         }\r
 258 \r
 259     }\r
 260         #endregion\r
 261 \r
 262     #region ExtensionMethods\r
 263 \r
 264     /// <summary>\r
 265     /// Extension written by Marc Gravell.\r
 266     /// Traverses nested properties\r
 267     /// </summary>\r
 268     static class SelectUsingSingleProjection\r
 269     {\r
 270         internal static IQueryable<T> SelectNested<T>(this IQueryable<T> source, params string[] propertyNames)\r
 271             where T : new()\r
 272         {\r
 273             Type type = typeof(T);\r
 274             var sourceItem = Expression.Parameter(type, "t");\r
 275             Expression exp = CreateAndInit(type, sourceItem, propertyNames);\r
 276             return source.Select(Expression.Lambda<Func<T, T>>(exp, sourceItem));\r
 277         }\r
 278 \r
 279         static Expression CreateAndInit(Type type, Expression source, string[] propertyNames)\r
 280         {\r
 281             if (type == null) throw new ArgumentNullException("type");\r
 282             if (source == null) throw new ArgumentNullException("source");\r
 283             if (propertyNames == null) throw new ArgumentNullException("propertyNames");\r
 284 \r
 285             var newExpr = Expression.New(type.GetConstructor(Type.EmptyTypes));\r
 286             // take "Foo.A", "Bar", "Foo.B" to "Foo" ["A","B"], "Bar" []\r
 287             var groupedNames = from name in propertyNames\r
 288                                let dotIndex = name.IndexOf('.')\r
 289                                let primary = dotIndex < 0 ? name : name.Substring(0, dotIndex)\r
 290                                let aux = dotIndex < 0 ? null : name.Substring(dotIndex + 1)\r
 291                                group aux by primary into grouped\r
 292                                select new\r
 293                                {\r
 294                                    Primary = grouped.Key,\r
 295                                    Aux = grouped.Where(x => x != null).ToArray()\r
 296                                };\r
 297             List<MemberBinding> bindings = new List<MemberBinding>();\r
 298             foreach (var grp in groupedNames)\r
 299             {\r
 300                 PropertyInfo dest = type.GetProperty(grp.Primary);\r
 301                 Expression value, readFrom = Expression.Property(source, grp.Primary);\r
 302                 if (grp.Aux.Length == 0)\r
 303                 {\r
 304                     value = readFrom;\r
 305                 }\r
 306                 else\r
 307                 {\r
 308                     value = CreateAndInit(dest.PropertyType, readFrom, grp.Aux);\r
 309                 }\r
 310                 bindings.Add(Expression.Bind(dest, value));\r
 311             }\r
 312             return Expression.MemberInit(newExpr, bindings);\r
 313         }\r
 314 \r
 315 \r
 316         /// <summary>\r
 317         /// Extension method provided by pwy.mail in issue http://code.google.com/p/dblinq2007/issues/detail?id=69\r
 318         /// </summary>\r
 319         internal static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,\r
 320                                                         Expression<Func<T, bool>> expr2)\r
 321         {\r
 322             var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());\r
 323             return Expression.Lambda<Func<T, bool>>\r
 324                   (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);\r
 325         }\r
 326 \r
 327         internal static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,\r
 328             Expression<Func<T, bool>> expr2)\r
 329         {\r
 330             var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());\r
 331             return Expression.Lambda<Func<T, bool>>\r
 332                   (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);\r
 333         }\r
 334     }\r
 335     #endregion\r
 336 \r
 337 }\r