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     }\r
 246         #endregion\r
 247 \r
 248     #region ExtensionMethods\r
 249 \r
 250     /// <summary>\r
 251     /// Extension written by Marc Gravell.\r
 252     /// Traverses nested properties\r
 253     /// </summary>\r
 254     static class SelectUsingSingleProjection\r
 255     {\r
 256         internal static IQueryable<T> SelectNested<T>(this IQueryable<T> source, params string[] propertyNames)\r
 257             where T : new()\r
 258         {\r
 259             Type type = typeof(T);\r
 260             var sourceItem = Expression.Parameter(type, "t");\r
 261             Expression exp = CreateAndInit(type, sourceItem, propertyNames);\r
 262             return source.Select(Expression.Lambda<Func<T, T>>(exp, sourceItem));\r
 263         }\r
 264 \r
 265         static Expression CreateAndInit(Type type, Expression source, string[] propertyNames)\r
 266         {\r
 267             if (type == null) throw new ArgumentNullException("type");\r
 268             if (source == null) throw new ArgumentNullException("source");\r
 269             if (propertyNames == null) throw new ArgumentNullException("propertyNames");\r
 270 \r
 271             var newExpr = Expression.New(type.GetConstructor(Type.EmptyTypes));\r
 272             // take "Foo.A", "Bar", "Foo.B" to "Foo" ["A","B"], "Bar" []\r
 273             var groupedNames = from name in propertyNames\r
 274                                let dotIndex = name.IndexOf('.')\r
 275                                let primary = dotIndex < 0 ? name : name.Substring(0, dotIndex)\r
 276                                let aux = dotIndex < 0 ? null : name.Substring(dotIndex + 1)\r
 277                                group aux by primary into grouped\r
 278                                select new\r
 279                                {\r
 280                                    Primary = grouped.Key,\r
 281                                    Aux = grouped.Where(x => x != null).ToArray()\r
 282                                };\r
 283             List<MemberBinding> bindings = new List<MemberBinding>();\r
 284             foreach (var grp in groupedNames)\r
 285             {\r
 286                 PropertyInfo dest = type.GetProperty(grp.Primary);\r
 287                 Expression value, readFrom = Expression.Property(source, grp.Primary);\r
 288                 if (grp.Aux.Length == 0)\r
 289                 {\r
 290                     value = readFrom;\r
 291                 }\r
 292                 else\r
 293                 {\r
 294                     value = CreateAndInit(dest.PropertyType, readFrom, grp.Aux);\r
 295                 }\r
 296                 bindings.Add(Expression.Bind(dest, value));\r
 297             }\r
 298             return Expression.MemberInit(newExpr, bindings);\r
 299         }\r
 300 \r
 301 \r
 302         /// <summary>\r
 303         /// Extension method provided by pwy.mail in issue http://code.google.com/p/dblinq2007/issues/detail?id=69\r
 304         /// </summary>\r
 305         internal static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,\r
 306                                                         Expression<Func<T, bool>> expr2)\r
 307         {\r
 308             var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());\r
 309             return Expression.Lambda<Func<T, bool>>\r
 310                   (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);\r
 311         }\r
 312 \r
 313         internal static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,\r
 314             Expression<Func<T, bool>> expr2)\r
 315         {\r
 316             var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());\r
 317             return Expression.Lambda<Func<T, bool>>\r
 318                   (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);\r
 319         }\r
 320     }\r
 321     #endregion\r
 322 \r
 323 }\r