1 using System; 2 using System.Collections.Generic; 3 using System.Data.Linq; 4 using System.Data.Linq.Provider; 5 using System.Data.Linq.SqlClient; 6 using System.Linq.Expressions; 7 using System.Reflection; 8 using System.Text; 9 using System.Diagnostics; 10 using System.Diagnostics.CodeAnalysis; 11 12 namespace System.Data.Linq.SqlClient { 13 14 internal static class PostBindDotNetConverter { 15 16 internal enum MethodSupport 17 { 18 None, // Unsupported method 19 MethodGroup, // One or more overloads of the method are supported 20 Method // The particular method form specified is supported (stronger) 21 } 22 Convert(SqlNode node, SqlFactory sql, SqlProvider.ProviderMode providerMode)23 internal static SqlNode Convert(SqlNode node, SqlFactory sql, SqlProvider.ProviderMode providerMode) { 24 return new Visitor(sql, providerMode).Visit(node); 25 } 26 CanConvert(SqlNode node)27 internal static bool CanConvert(SqlNode node) { 28 SqlUnary su = node as SqlUnary; 29 if (su != null && IsSupportedUnary(su)) { 30 return true; 31 } 32 SqlNew sn = node as SqlNew; 33 if (sn != null && IsSupportedNew(sn)) { 34 return true; 35 } 36 SqlMember sm = node as SqlMember; 37 if (sm != null && IsSupportedMember(sm)) { 38 return true; 39 } 40 SqlMethodCall mc = node as SqlMethodCall; 41 if (mc != null && (GetMethodSupport(mc) == MethodSupport.Method)) { 42 return true; 43 } 44 return false; 45 } 46 IsSupportedUnary(SqlUnary uo)47 private static bool IsSupportedUnary(SqlUnary uo) { 48 return uo.NodeType == SqlNodeType.Convert && 49 uo.ClrType == typeof(char) || uo.Operand.ClrType == typeof(char); 50 } 51 IsSupportedNew(SqlNew snew)52 private static bool IsSupportedNew(SqlNew snew) { 53 if (snew.ClrType == typeof(string)) { 54 return IsSupportedStringNew(snew); 55 } 56 else if (snew.ClrType == typeof(TimeSpan)) { 57 return IsSupportedTimeSpanNew(snew); 58 } 59 else if (snew.ClrType == typeof(DateTime)) { 60 return IsSupportedDateTimeNew(snew); 61 } 62 return false; 63 } 64 IsSupportedStringNew(SqlNew snew)65 private static bool IsSupportedStringNew(SqlNew snew) { 66 return snew.Args.Count == 2 && snew.Args[0].ClrType == typeof(char) && snew.Args[1].ClrType == typeof(int); 67 } 68 IsSupportedDateTimeNew(SqlNew sox)69 private static bool IsSupportedDateTimeNew(SqlNew sox) { 70 if (sox.ClrType == typeof(DateTime) 71 && sox.Args.Count >= 3 72 && sox.Args[0].ClrType == typeof(int) 73 && sox.Args[1].ClrType == typeof(int) 74 && sox.Args[2].ClrType == typeof(int)) { 75 if (sox.Args.Count == 3) { 76 return true; 77 } 78 if (sox.Args.Count >= 6 && 79 sox.Args[3].ClrType == typeof(int) && sox.Args[4].ClrType == typeof(int) && sox.Args[5].ClrType == typeof(int)) { 80 if (sox.Args.Count == 6) { 81 return true; 82 } 83 if ((sox.Args.Count == 7) && (sox.Args[6].ClrType == typeof(int))) { 84 return true; 85 } 86 } 87 } 88 return false; 89 } 90 IsSupportedTimeSpanNew(SqlNew sox)91 private static bool IsSupportedTimeSpanNew(SqlNew sox) { 92 if (sox.Args.Count == 1) { 93 return true; 94 } 95 else if (sox.Args.Count == 3) { 96 return true; 97 } 98 else { 99 if (sox.Args.Count == 4) { 100 return true; 101 } 102 else if (sox.Args.Count == 5) { 103 return true; 104 } 105 } 106 return false; 107 } 108 GetMethodSupport(SqlMethodCall mc)109 private static MethodSupport GetMethodSupport(SqlMethodCall mc) { 110 // Get support level for each, returning the highest 111 MethodSupport best = MethodSupport.None; 112 MethodSupport ms = GetSqlMethodsMethodSupport(mc); 113 if (ms > best) { 114 best = ms; 115 } 116 ms = GetDateTimeMethodSupport(mc); 117 if (ms > best) { 118 best = ms; 119 } 120 ms = GetDateTimeOffsetMethodSupport(mc); 121 if (ms > best) { 122 best = ms; 123 } 124 ms = GetTimeSpanMethodSupport(mc); 125 if (ms > best) { 126 best = ms; 127 } 128 ms = GetConvertMethodSupport(mc); 129 if (ms > best) { 130 best = ms; 131 } 132 ms = GetDecimalMethodSupport(mc); 133 if (ms > best) { 134 best = ms; 135 } 136 ms = GetMathMethodSupport(mc); 137 if (ms > best) { 138 best = ms; 139 } 140 ms = GetStringMethodSupport(mc); 141 if (ms > best) { 142 best = ms; 143 } 144 ms = GetComparisonMethodSupport(mc); 145 if (ms > best) { 146 best = ms; 147 } 148 ms = GetNullableMethodSupport(mc); 149 if (ms > best) { 150 best = ms; 151 } 152 ms = GetCoercionMethodSupport(mc); 153 if (ms > best) { 154 best = ms; 155 } 156 ms = GetObjectMethodSupport(mc); 157 if (ms > best) { 158 best = ms; 159 } 160 ms = GetVbHelperMethodSupport(mc); 161 if (ms > best) { 162 best = ms; 163 } 164 165 return best; 166 } 167 GetCoercionMethodSupport(SqlMethodCall mc)168 private static MethodSupport GetCoercionMethodSupport(SqlMethodCall mc) { 169 if(mc.Method.IsStatic 170 && mc.SqlType.CanBeColumn 171 && (mc.Method.Name == "op_Implicit" || mc.Method.Name == "op_Explicit")){ 172 return MethodSupport.Method; 173 } 174 return MethodSupport.None; 175 } 176 GetComparisonMethodSupport(SqlMethodCall mc)177 private static MethodSupport GetComparisonMethodSupport(SqlMethodCall mc) { 178 if (mc.Method.IsStatic && mc.Method.Name == "Compare" && mc.Method.ReturnType == typeof(int)) { 179 return MethodSupport.Method; 180 } 181 return MethodSupport.None; 182 } 183 GetObjectMethodSupport(SqlMethodCall mc)184 private static MethodSupport GetObjectMethodSupport(SqlMethodCall mc) { 185 if (!mc.Method.IsStatic) { 186 switch (mc.Method.Name) { 187 case "Equals": 188 return MethodSupport.Method; 189 case "ToString": 190 if (mc.Object.SqlType.CanBeColumn) { 191 return MethodSupport.Method; 192 } 193 return MethodSupport.None; 194 case "GetType": 195 if (mc.Arguments.Count == 0) { 196 return MethodSupport.Method; 197 } 198 return MethodSupport.None; 199 } 200 } 201 return MethodSupport.None; 202 } 203 GetNullableMethodSupport(SqlMethodCall mc)204 private static MethodSupport GetNullableMethodSupport(SqlMethodCall mc) { 205 if (mc.Method.Name == "GetValueOrDefault" && TypeSystem.IsNullableType(mc.Object.ClrType)) { 206 return MethodSupport.Method; 207 } 208 return MethodSupport.None; 209 } 210 211 private static readonly string[] dateParts = { "Year", "Month", "Day", "Hour", "Minute", "Second", 212 "Millisecond", "Microsecond", "Nanosecond" }; 213 GetSqlMethodsMethodSupport(SqlMethodCall mc)214 private static MethodSupport GetSqlMethodsMethodSupport(SqlMethodCall mc) { 215 if (mc.Method.IsStatic && mc.Method.DeclaringType == typeof(SqlMethods)) { 216 if (mc.Method.Name.StartsWith("DateDiff", StringComparison.Ordinal) && mc.Arguments.Count == 2) { 217 foreach (string datePart in dateParts) { 218 if (mc.Method.Name == "DateDiff" + datePart) { 219 if (mc.Arguments.Count == 2) { 220 return MethodSupport.Method; 221 } else { 222 return MethodSupport.MethodGroup; 223 } 224 } 225 } 226 } 227 else if (mc.Method.Name == "Like") { 228 if (mc.Arguments.Count == 2) { 229 return MethodSupport.Method; 230 } 231 else if (mc.Arguments.Count == 3) { 232 return MethodSupport.Method; 233 } 234 return MethodSupport.MethodGroup; 235 } 236 else if (mc.Method.Name == "RawLength") { 237 return MethodSupport.Method; 238 } 239 } 240 return MethodSupport.None; 241 } 242 GetDateTimeMethodSupport(SqlMethodCall mc)243 private static MethodSupport GetDateTimeMethodSupport(SqlMethodCall mc) { 244 if (!mc.Method.IsStatic && mc.Method.DeclaringType == typeof(DateTime)) { 245 switch (mc.Method.Name) { 246 case "CompareTo": 247 case "AddTicks": 248 case "AddMonths": 249 case "AddYears": 250 case "AddMilliseconds": 251 case "AddSeconds": 252 case "AddMinutes": 253 case "AddHours": 254 case "AddDays": 255 return MethodSupport.Method; 256 case "Add": 257 if (mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan)) { 258 return MethodSupport.Method; 259 } else { 260 return MethodSupport.MethodGroup; 261 } 262 } 263 } 264 return MethodSupport.None; 265 } 266 GetDateTimeOffsetMethodSupport(SqlMethodCall mc)267 private static MethodSupport GetDateTimeOffsetMethodSupport(SqlMethodCall mc) { 268 if (!mc.Method.IsStatic && mc.Method.DeclaringType == typeof(DateTimeOffset)) { 269 switch (mc.Method.Name) { 270 case "CompareTo": 271 case "AddTicks": 272 case "AddMonths": 273 case "AddYears": 274 case "AddMilliseconds": 275 case "AddSeconds": 276 case "AddMinutes": 277 case "AddHours": 278 case "AddDays": 279 return MethodSupport.Method; 280 case "Add": 281 if (mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan)) { 282 return MethodSupport.Method; 283 } 284 else { 285 return MethodSupport.MethodGroup; 286 } 287 } 288 } 289 return MethodSupport.None; 290 } 291 GetTimeSpanMethodSupport(SqlMethodCall mc)292 private static MethodSupport GetTimeSpanMethodSupport(SqlMethodCall mc) { 293 if (!mc.Method.IsStatic && mc.Method.DeclaringType == typeof(TimeSpan)) { 294 switch (mc.Method.Name) { 295 case "Add": 296 case "Subtract": 297 case "CompareTo": 298 case "Duration": 299 case "Negate": 300 return MethodSupport.Method; 301 } 302 } 303 return MethodSupport.None; 304 } 305 GetConvertMethodSupport(SqlMethodCall mc)306 private static MethodSupport GetConvertMethodSupport(SqlMethodCall mc) { 307 if (mc.Method.IsStatic && mc.Method.DeclaringType == typeof(Convert) && mc.Arguments.Count == 1) { 308 switch (mc.Method.Name) { 309 case "ToBoolean": 310 case "ToDecimal": 311 case "ToByte": 312 case "ToChar": 313 case "ToDouble": 314 case "ToInt16": 315 case "ToInt32": 316 case "ToInt64": 317 case "ToSingle": 318 case "ToString": 319 return MethodSupport.Method; 320 case "ToDateTime": 321 if (mc.Arguments[0].ClrType == typeof(string) || mc.Arguments[0].ClrType == typeof(DateTime)) { 322 return MethodSupport.Method; 323 } else { 324 return MethodSupport.MethodGroup; 325 } 326 } 327 } 328 return MethodSupport.None; 329 } 330 GetDecimalMethodSupport(SqlMethodCall mc)331 private static MethodSupport GetDecimalMethodSupport(SqlMethodCall mc) { 332 if (mc.Method.IsStatic) { 333 if (mc.Arguments.Count == 2) { 334 switch (mc.Method.Name) { 335 case "Multiply": 336 case "Divide": 337 case "Subtract": 338 case "Add": 339 case "Remainder": 340 case "Round": 341 return MethodSupport.Method; 342 } 343 } 344 else if (mc.Arguments.Count == 1) { 345 switch (mc.Method.Name) { 346 case "Negate": 347 case "Floor": 348 case "Truncate": 349 case "Round": 350 return MethodSupport.Method; 351 default: 352 if (mc.Method.Name.StartsWith("To", StringComparison.Ordinal)) { 353 return MethodSupport.Method; 354 } 355 break; 356 } 357 } 358 } 359 return MethodSupport.None; 360 } 361 GetStringMethodSupport(SqlMethodCall mc)362 private static MethodSupport GetStringMethodSupport(SqlMethodCall mc) { 363 if (mc.Method.DeclaringType == typeof(string)) { 364 if (mc.Method.IsStatic) { 365 if (mc.Method.Name == "Concat") { 366 return MethodSupport.Method; 367 } 368 } 369 else { 370 switch (mc.Method.Name) { 371 case "Contains": 372 case "StartsWith": 373 case "EndsWith": 374 if (mc.Arguments.Count == 1) { 375 return MethodSupport.Method; 376 } 377 return MethodSupport.MethodGroup; 378 case "IndexOf": 379 case "LastIndexOf": 380 if (mc.Arguments.Count == 1 381 || mc.Arguments.Count == 2 382 || mc.Arguments.Count == 3) { 383 return MethodSupport.Method; 384 } 385 return MethodSupport.MethodGroup; 386 case "Insert": 387 if (mc.Arguments.Count == 2) { 388 return MethodSupport.Method; 389 } 390 return MethodSupport.MethodGroup; 391 case "PadLeft": 392 case "PadRight": 393 case "Remove": 394 case "Substring": 395 if(mc.Arguments.Count == 1 396 || mc.Arguments.Count == 2) { 397 return MethodSupport.Method; 398 } 399 return MethodSupport.MethodGroup; 400 case "Replace": 401 return MethodSupport.Method; 402 case "Trim": 403 case "ToLower": 404 case "ToUpper": 405 if (mc.Arguments.Count == 0) { 406 return MethodSupport.Method; 407 } 408 return MethodSupport.MethodGroup; 409 case "get_Chars": 410 case "CompareTo": 411 if (mc.Arguments.Count == 1) { 412 return MethodSupport.Method; 413 } 414 return MethodSupport.MethodGroup; 415 } 416 } 417 } 418 return MethodSupport.None; 419 } 420 GetMathMethodSupport(SqlMethodCall mc)421 private static MethodSupport GetMathMethodSupport(SqlMethodCall mc) { 422 if (mc.Method.IsStatic && mc.Method.DeclaringType == typeof(Math)) { 423 switch (mc.Method.Name) { 424 case "Abs": 425 case "Acos": 426 case "Asin": 427 case "Atan": 428 case "Ceiling": 429 case "Cos": 430 case "Cosh": 431 case "Exp": 432 case "Floor": 433 case "Log10": 434 if(mc.Arguments.Count == 1) { 435 return MethodSupport.Method; 436 } 437 return MethodSupport.MethodGroup; 438 case "Log": 439 if (mc.Arguments.Count == 1 || mc.Arguments.Count == 2) { 440 return MethodSupport.Method; 441 }; 442 return MethodSupport.MethodGroup; 443 case "Max": 444 case "Min": 445 case "Pow": 446 case "Atan2": 447 case "BigMul": 448 if (mc.Arguments.Count == 2) { 449 return MethodSupport.Method; 450 } 451 return MethodSupport.MethodGroup; 452 case "Round": 453 if (mc.Arguments[mc.Arguments.Count - 1].ClrType == typeof(MidpointRounding) 454 && (mc.Arguments.Count == 2 || mc.Arguments.Count == 3)) { 455 return MethodSupport.Method; 456 } 457 return MethodSupport.MethodGroup; 458 case "Sign": 459 case "Sin": 460 case "Sinh": 461 case "Sqrt": 462 case "Tan": 463 case "Tanh": 464 case "Truncate": 465 if (mc.Arguments.Count == 1) { 466 return MethodSupport.Method; 467 } 468 return MethodSupport.MethodGroup; 469 } 470 } 471 return MethodSupport.None; 472 } 473 GetVbHelperMethodSupport(SqlMethodCall mc)474 private static MethodSupport GetVbHelperMethodSupport(SqlMethodCall mc) { 475 if (IsVbConversionMethod(mc) || 476 IsVbCompareString(mc) || 477 IsVbLike(mc)) { 478 return MethodSupport.Method; 479 } 480 return MethodSupport.None; 481 } 482 IsVbCompareString(SqlMethodCall call)483 private static bool IsVbCompareString(SqlMethodCall call) { 484 return call.Method.IsStatic && 485 call.Method.DeclaringType.FullName == "Microsoft.VisualBasic.CompilerServices.Operators" && 486 call.Method.Name == "CompareString"; 487 } 488 IsVbLike(SqlMethodCall mc)489 private static bool IsVbLike(SqlMethodCall mc) { 490 return mc.Method.IsStatic && 491 (mc.Method.DeclaringType.FullName == "Microsoft.VisualBasic.CompilerServices.LikeOperator" && mc.Method.Name == "LikeString") 492 || (mc.Method.DeclaringType.FullName == "Microsoft.VisualBasic.CompilerServices.Operators" && mc.Method.Name == "LikeString"); 493 } 494 IsVbConversionMethod(SqlMethodCall mc)495 private static bool IsVbConversionMethod(SqlMethodCall mc) { 496 if (mc.Method.IsStatic && 497 mc.Method.DeclaringType.FullName == "Microsoft.VisualBasic.CompilerServices.Conversions") { 498 switch (mc.Method.Name) { 499 case "ToBoolean": 500 case "ToSByte": 501 case "ToByte": 502 case "ToChar": 503 case "ToCharArrayRankOne": 504 case "ToDate": 505 case "ToDecimal": 506 case "ToDouble": 507 case "ToInteger": 508 case "ToUInteger": 509 case "ToLong": 510 case "ToULong": 511 case "ToShort": 512 case "ToUShort": 513 case "ToSingle": 514 case "ToString": 515 return true; 516 } 517 } 518 return false; 519 } 520 IsSupportedMember(SqlMember m)521 private static bool IsSupportedMember(SqlMember m) { 522 return IsSupportedStringMember(m) 523 || IsSupportedBinaryMember(m) 524 || IsSupportedDateTimeMember(m) 525 || IsSupportedDateTimeOffsetMember(m) 526 || IsSupportedTimeSpanMember(m); 527 } 528 IsSupportedStringMember(SqlMember m)529 private static bool IsSupportedStringMember(SqlMember m) { 530 return m.Expression.ClrType == typeof(string) && m.Member.Name == "Length"; 531 } 532 IsSupportedBinaryMember(SqlMember m)533 private static bool IsSupportedBinaryMember(SqlMember m) { 534 return m.Expression.ClrType == typeof(Binary) && m.Member.Name == "Length"; 535 } 536 GetDatePart(string memberName)537 private static string GetDatePart(string memberName) { 538 switch (memberName) { 539 case "Year": 540 case "Month": 541 case "Day": 542 case "DayOfYear": 543 case "Hour": 544 case "Minute": 545 case "Second": 546 case "Millisecond": 547 return memberName; 548 default: 549 return null; 550 } 551 } 552 IsSupportedDateTimeMember(SqlMember m)553 private static bool IsSupportedDateTimeMember(SqlMember m) { 554 if (m.Expression.ClrType == typeof(DateTime)) { 555 string datePart = GetDatePart(m.Member.Name); 556 if (datePart != null) { 557 return true; 558 } 559 switch (m.Member.Name) { 560 case "Date": 561 case "TimeOfDay": 562 case "DayOfWeek": 563 return true; 564 } 565 } 566 return false; 567 } 568 569 // 570 // Identical to IsSupportedDateTimeMember(), except for support for 'DateTime' 571 // IsSupportedDateTimeOffsetMember(SqlMember m)572 private static bool IsSupportedDateTimeOffsetMember(SqlMember m) { 573 if (m.Expression.ClrType == typeof(DateTimeOffset)) { 574 string datePart = GetDatePart(m.Member.Name); 575 if (datePart != null) { 576 return true; 577 } 578 switch (m.Member.Name) { 579 case "Date": 580 case "DateTime": 581 case "TimeOfDay": 582 case "DayOfWeek": 583 return true; 584 } 585 } 586 return false; 587 } 588 IsSupportedTimeSpanMember(SqlMember m)589 private static bool IsSupportedTimeSpanMember(SqlMember m) { 590 if (m.Expression.ClrType == typeof(TimeSpan)) { 591 switch (m.Member.Name) { 592 case "Ticks": 593 case "TotalMilliseconds": 594 case "TotalSeconds": 595 case "TotalMinutes": 596 case "TotalHours": 597 case "TotalDays": 598 case "Milliseconds": 599 case "Seconds": 600 case "Minutes": 601 case "Hours": 602 case "Days": 603 return true; 604 } 605 } 606 return false; 607 } 608 609 /// <summary> 610 /// Skips over client portion of selection expression 611 /// </summary> 612 private class SqlSelectionSkipper : SqlVisitor { 613 SqlVisitor parent; SqlSelectionSkipper(SqlVisitor parent)614 internal SqlSelectionSkipper(SqlVisitor parent) { 615 this.parent = parent; 616 } VisitColumn(SqlColumn col)617 internal override SqlExpression VisitColumn(SqlColumn col) { 618 // pass control back to parent 619 return parent.VisitColumn(col); 620 } VisitSubSelect(SqlSubSelect ss)621 internal override SqlExpression VisitSubSelect(SqlSubSelect ss) { 622 // pass control back to parent 623 return this.parent.VisitSubSelect(ss); 624 } VisitClientQuery(SqlClientQuery cq)625 internal override SqlExpression VisitClientQuery(SqlClientQuery cq) { 626 // pass control back to parent 627 return this.parent.VisitClientQuery(cq); 628 } 629 } 630 631 private class Visitor : SqlVisitor { 632 SqlFactory sql; 633 SqlProvider.ProviderMode providerMode; 634 SqlSelectionSkipper skipper; 635 Visitor(SqlFactory sql, SqlProvider.ProviderMode providerMode)636 internal Visitor(SqlFactory sql, SqlProvider.ProviderMode providerMode) { 637 this.sql = sql; 638 this.providerMode = providerMode; 639 this.skipper = new SqlSelectionSkipper(this); 640 } 641 VisitSelect(SqlSelect select)642 internal override SqlSelect VisitSelect(SqlSelect select) { 643 select = this.VisitSelectCore(select); 644 // don't transate frameworks calls on client side of selection 645 select.Selection = this.skipper.VisitExpression(select.Selection); 646 return select; 647 } 648 649 // transform type conversion if necessary VisitUnaryOperator(SqlUnary uo)650 internal override SqlExpression VisitUnaryOperator(SqlUnary uo) { 651 if (uo.NodeType == SqlNodeType.Convert) { 652 Type newType = uo.ClrType; 653 SqlExpression expr = uo.Operand; 654 if (newType == typeof(char) || expr.ClrType == typeof(char)) { 655 expr = this.VisitExpression(uo.Operand); 656 uo.Operand = expr; 657 return sql.ConvertTo(newType, uo.SqlType, expr); 658 } 659 } 660 return base.VisitUnaryOperator(uo); 661 } 662 VisitBinaryOperator(SqlBinary bo)663 internal override SqlExpression VisitBinaryOperator(SqlBinary bo) { 664 bo = (SqlBinary)base.VisitBinaryOperator(bo); 665 Type leftType = TypeSystem.GetNonNullableType(bo.Left.ClrType); 666 if (leftType == typeof(DateTime) || leftType == typeof(DateTimeOffset)) { 667 return this.TranslateDateTimeBinary(bo); 668 } 669 return bo; 670 } 671 672 673 // currently only happens for generated test cases with optimization SimplifyCaseStatements off VisitTypeCase(SqlTypeCase tc)674 internal override SqlExpression VisitTypeCase(SqlTypeCase tc) { 675 tc.Discriminator = base.VisitExpression(tc.Discriminator); 676 List<SqlExpression> matches = new List<SqlExpression>(); 677 List<SqlExpression> values = new List<SqlExpression>(); 678 bool remainsTypeCase = true; 679 foreach (SqlTypeCaseWhen when in tc.Whens) { 680 SqlExpression newMatch = this.VisitExpression(when.Match); 681 SqlExpression newNew = this.VisitExpression(when.TypeBinding); 682 remainsTypeCase = remainsTypeCase && (newNew is SqlNew); 683 matches.Add(newMatch); 684 values.Add(newNew); 685 } 686 if (remainsTypeCase) { 687 for (int i = 0, n = tc.Whens.Count; i < n; i++) { 688 SqlTypeCaseWhen when = tc.Whens[i]; 689 when.Match = matches[i]; 690 when.TypeBinding = (SqlNew)values[i]; 691 } 692 return tc; 693 } 694 else { 695 return sql.Case(tc.ClrType, tc.Discriminator, matches, values, tc.SourceExpression); 696 } 697 } 698 699 // transform constructors if necessary VisitNew(SqlNew sox)700 internal override SqlExpression VisitNew(SqlNew sox) { 701 sox = (SqlNew)base.VisitNew(sox); 702 if (sox.ClrType == typeof(string)) { 703 return TranslateNewString(sox); 704 } 705 else if (sox.ClrType == typeof(TimeSpan)) { 706 return TranslateNewTimeSpan(sox); 707 } 708 else if (sox.ClrType == typeof(DateTime)) { 709 return TranslateNewDateTime(sox); 710 } 711 else if (sox.ClrType == typeof(DateTimeOffset)) { 712 return TranslateNewDateTimeOffset(sox); 713 } 714 return sox; 715 } 716 TranslateNewString(SqlNew sox)717 private SqlExpression TranslateNewString(SqlNew sox) { 718 // string(char c, int i) 719 // --> REPLICATE(@c,@i) 720 if (sox.ClrType == typeof(string) && sox.Args.Count == 2 721 && sox.Args[0].ClrType == typeof(char) && sox.Args[1].ClrType == typeof(int)) { 722 return sql.FunctionCall(typeof(string), "REPLICATE", new SqlExpression[] { sox.Args[0], sox.Args[1] }, sox.SourceExpression); 723 } 724 throw Error.UnsupportedStringConstructorForm(); 725 } 726 TranslateNewDateTime(SqlNew sox)727 private SqlExpression TranslateNewDateTime(SqlNew sox) { 728 Expression source = sox.SourceExpression; 729 730 // DateTime(int year, int month, int day) 731 // --> CONVERT(DATETIME, CONVERT(nchar(2),@month) + '/' + CONVERT(nchar(2),@day) + '/' + CONVERT(nchar(4),@year),101) 732 if (sox.ClrType == typeof(DateTime) && sox.Args.Count >= 3 && 733 sox.Args[0].ClrType == typeof(int) && sox.Args[1].ClrType == typeof(int) && sox.Args[2].ClrType == typeof(int)) { 734 SqlExpression char2 = sql.FunctionCall(typeof(void), "NCHAR", new SqlExpression[1] { sql.ValueFromObject(2, false, source) }, source); 735 SqlExpression char4 = sql.FunctionCall(typeof(void), "NCHAR", new SqlExpression[1] { sql.ValueFromObject(4, false, source) }, source); 736 SqlExpression year = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char4, sox.Args[0] }, source); 737 SqlExpression month = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[1] }, source); 738 SqlExpression day = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[2] }, source); 739 SqlExpression datetime = new SqlVariable(typeof(void), null, "DATETIME", source); 740 if (sox.Args.Count == 3) { 741 SqlExpression date = sql.Concat(month, sql.ValueFromObject("/", false, source), day, sql.ValueFromObject("/", false, source), year); 742 return sql.FunctionCall(typeof(DateTime), "CONVERT", new SqlExpression[3] { datetime, date, sql.ValueFromObject(101, false, source) }, source); 743 } 744 if (sox.Args.Count >= 6 && 745 sox.Args[3].ClrType == typeof(int) && sox.Args[4].ClrType == typeof(int) && sox.Args[5].ClrType == typeof(int)) { 746 // DateTime(year, month, day, hour, minute, second ) 747 // --> CONVERT(DATETIME, CONVERT(nchar(2),@month) + '-' + CONVERT(nchar(2),@day) + '-' + CONVERT(nchar(4),@year) + 748 // ' ' + CONVERT(nchar(2),@hour) + ':' + CONVERT(nchar(2),@minute) + ':' + CONVERT(nchar(2),@second) ,120) 749 SqlExpression hour = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[3] }, source); 750 SqlExpression minute = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[4] }, source); 751 SqlExpression second = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[5] }, source); 752 SqlExpression date = sql.Concat(year, sql.ValueFromObject("-", false, source), month, sql.ValueFromObject("-", false, source), day); 753 SqlExpression time = sql.Concat(hour, sql.ValueFromObject(":", false, source), minute, sql.ValueFromObject(":", false, source), second); 754 SqlExpression dateAndTime = sql.Concat(date, sql.ValueFromObject(' ', false, source), time); 755 if (sox.Args.Count == 6) { 756 return sql.FunctionCall(typeof(DateTime), "CONVERT", new SqlExpression[3] { datetime, dateAndTime, sql.ValueFromObject(120, false, source) }, source); 757 } 758 if ((sox.Args.Count == 7) && (sox.Args[6].ClrType == typeof(int))) { 759 // DateTime(year, month, day, hour, minute, second, millisecond ) 760 // add leading zeros to milliseconds by RIGHT(CONVERT(NCHAR(4),1000+@ms),3) 761 SqlExpression msRaw = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] {char4, 762 sql.Add(sql.ValueFromObject(1000, false, source),sox.Args[6])}, source); 763 SqlExpression ms; 764 if (this.providerMode == SqlProvider.ProviderMode.SqlCE) { 765 //SqlCE doesn't have "RIGHT", so need to use "SUBSTRING" 766 SqlExpression len = sql.FunctionCall(typeof(int), "LEN", new SqlExpression[1] { msRaw }, source); 767 SqlExpression startIndex = sql.Binary(SqlNodeType.Sub, len, sql.ValueFromObject(2, false, source)); 768 ms = sql.FunctionCall(typeof(string), "SUBSTRING", new SqlExpression[3] { msRaw, startIndex, sql.ValueFromObject(3, false, source) }, source); 769 } 770 else { 771 ms = sql.FunctionCall(typeof(string), "RIGHT", new SqlExpression[2] { msRaw, sql.ValueFromObject(3, false, source) }, source); 772 } 773 dateAndTime = sql.Concat(dateAndTime, sql.ValueFromObject('.', false, source), ms); 774 return sql.FunctionCall(typeof(DateTime), "CONVERT", new SqlExpression[3] { datetime, dateAndTime, sql.ValueFromObject(121, false, source) }, source); 775 } 776 } 777 } 778 throw Error.UnsupportedDateTimeConstructorForm(); 779 } 780 TranslateNewDateTimeOffset(SqlNew sox)781 private SqlExpression TranslateNewDateTimeOffset(SqlNew sox) { 782 Expression source = sox.SourceExpression; 783 if (sox.ClrType == typeof(DateTimeOffset)) { 784 // DateTimeOffset(DateTime dateTime) 785 // --> CONVERT(DATETIMEOFFSET, @dateTime) 786 if (sox.Args.Count == 1 && sox.Args[0].ClrType == typeof(DateTime)) { 787 return sql.FunctionCall(typeof(DateTimeOffset), "TODATETIMEOFFSET", 788 new SqlExpression[2] { sox.Args[0], sql.ValueFromObject(0, false, source) }, 789 source); 790 } 791 // DateTimeOffset(DateTime dateTime, TimeSpan timeSpan) 792 // --> DATEADD(DATETIMEOFFSET, @dateTimePart) 793 if (sox.Args.Count == 2 && sox.Args[0].ClrType == typeof(DateTime) && sox.Args[1].ClrType == typeof(TimeSpan)) { 794 return sql.FunctionCall(typeof(DateTimeOffset), "TODATETIMEOFFSET", 795 new SqlExpression[2] 796 { 797 sox.Args[0], 798 sql.ConvertToInt(sql.ConvertToBigint(sql.Divide(sql.ConvertTimeToDouble(sox.Args[1]), TimeSpan.TicksPerMinute))) 799 }, 800 source); 801 } 802 // DateTimeOffset(year, month, day, hour, minute, second, [millisecond,] timeSpan) 803 // 804 if (sox.Args.Count >= 7 && 805 sox.Args[0].ClrType == typeof(int) && sox.Args[1].ClrType == typeof(int) && sox.Args[2].ClrType == typeof(int) && 806 sox.Args[3].ClrType == typeof(int) && sox.Args[4].ClrType == typeof(int) && sox.Args[5].ClrType == typeof(int)) { 807 808 SqlExpression char2 = sql.FunctionCall(typeof(void), "NCHAR", new SqlExpression[1] { sql.ValueFromObject(2, false, source) }, source); 809 SqlExpression char4 = sql.FunctionCall(typeof(void), "NCHAR", new SqlExpression[1] { sql.ValueFromObject(4, false, source) }, source); 810 SqlExpression char5 = sql.FunctionCall(typeof(void), "NCHAR", new SqlExpression[1] { sql.ValueFromObject(5, false, source) }, source); 811 812 // add leading zeros to year by RIGHT(CONVERT(NCHAR(5),10000+@ms),4) 813 SqlExpression yyRaw = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] {char5, 814 sql.Add(sql.ValueFromObject(10000, false, source),sox.Args[0])}, source); 815 SqlExpression year = sql.FunctionCall(typeof(string), "RIGHT", new SqlExpression[2] { yyRaw, sql.ValueFromObject(4, false, source) }, source); 816 817 SqlExpression month = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[1] }, source); 818 SqlExpression day = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[2] }, source); 819 820 SqlExpression hour = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[3] }, source); 821 SqlExpression minute = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[4] }, source); 822 SqlExpression second = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] { char2, sox.Args[5] }, source); 823 SqlExpression date = sql.Concat(year, sql.ValueFromObject("-", false, source), month, sql.ValueFromObject("-", false, source), day); 824 SqlExpression time = sql.Concat(hour, sql.ValueFromObject(":", false, source), minute, sql.ValueFromObject(":", false, source), second); 825 826 SqlExpression datetimeoffset = new SqlVariable(typeof(void), null, "DATETIMEOFFSET", source); 827 SqlExpression result, dateAndTime; 828 int timeSpanIndex; 829 830 if (sox.Args.Count == 7 && sox.Args[6].ClrType == typeof(TimeSpan)) { 831 timeSpanIndex = 6; 832 dateAndTime = sql.Concat(date, sql.ValueFromObject(' ', false, source), time); 833 result = sql.FunctionCall(typeof(DateTimeOffset), "CONVERT", new SqlExpression[3] { datetimeoffset, dateAndTime, sql.ValueFromObject(120, false, source) }, source); 834 } 835 else if (sox.Args.Count == 8 && sox.Args[6].ClrType == typeof(int) && sox.Args[7].ClrType == typeof(TimeSpan)) { 836 timeSpanIndex = 7; 837 // add leading zeros to milliseconds by RIGHT(CONVERT(NCHAR(4),1000+@ms),3) 838 SqlExpression msRaw = sql.FunctionCall(typeof(string), "CONVERT", new SqlExpression[2] {char4, 839 sql.Add(sql.ValueFromObject(1000, false, source),sox.Args[6])}, source); 840 SqlExpression ms = sql.FunctionCall(typeof(string), "RIGHT", new SqlExpression[2] { msRaw, sql.ValueFromObject(3, false, source) }, source); 841 dateAndTime = sql.Concat(date, sql.ValueFromObject(' ', false, source), time, sql.ValueFromObject('.', false, source), ms); 842 result = sql.FunctionCall(typeof(DateTimeOffset), "CONVERT", new SqlExpression[3] { datetimeoffset, dateAndTime, sql.ValueFromObject(121, false, source) }, source); 843 } 844 else { 845 throw Error.UnsupportedDateTimeOffsetConstructorForm(); 846 } 847 848 return sql.FunctionCall(typeof(DateTimeOffset), "TODATETIMEOFFSET", 849 new SqlExpression[2] 850 { 851 result, 852 sql.ConvertToInt(sql.ConvertToBigint(sql.Divide(sql.ConvertTimeToDouble(sox.Args[timeSpanIndex]), TimeSpan.TicksPerMinute))) 853 }, 854 source); 855 } 856 } 857 throw Error.UnsupportedDateTimeOffsetConstructorForm(); 858 } 859 TranslateNewTimeSpan(SqlNew sox)860 private SqlExpression TranslateNewTimeSpan(SqlNew sox) { 861 if (sox.Args.Count == 1) { 862 return sql.ConvertTo(typeof(TimeSpan), sox.Args[0]); 863 } 864 else if (sox.Args.Count == 3) { 865 // TimeSpan(hours, minutes, seconds) 866 SqlExpression hours = sql.ConvertToBigint(sox.Args[0]); 867 SqlExpression minutes = sql.ConvertToBigint(sox.Args[1]); 868 SqlExpression seconds = sql.ConvertToBigint(sox.Args[2]); 869 SqlExpression TicksFromHours = sql.Multiply(hours, TimeSpan.TicksPerHour); 870 SqlExpression TicksFromMinutes = sql.Multiply(minutes, TimeSpan.TicksPerMinute); 871 SqlExpression TicksFromSeconds = sql.Multiply(seconds, TimeSpan.TicksPerSecond); 872 return sql.ConvertTo(typeof(TimeSpan), sql.Add(TicksFromHours, TicksFromMinutes, TicksFromSeconds)); 873 } 874 else { 875 SqlExpression days = sql.ConvertToBigint(sox.Args[0]); 876 SqlExpression hours = sql.ConvertToBigint(sox.Args[1]); 877 SqlExpression minutes = sql.ConvertToBigint(sox.Args[2]); 878 SqlExpression seconds = sql.ConvertToBigint(sox.Args[3]); 879 SqlExpression TicksFromDays = sql.Multiply(days, TimeSpan.TicksPerDay); 880 SqlExpression TicksFromHours = sql.Multiply(hours, TimeSpan.TicksPerHour); 881 SqlExpression TicksFromMinutes = sql.Multiply(minutes, TimeSpan.TicksPerMinute); 882 SqlExpression TicksFromSeconds = sql.Multiply(seconds, TimeSpan.TicksPerSecond); 883 SqlExpression totalTicks = sql.Add(TicksFromDays, TicksFromHours, TicksFromMinutes, TicksFromSeconds); 884 if (sox.Args.Count == 4) { 885 // TimeSpan(days, hours, minutes, seconds) 886 return sql.ConvertTo(typeof(TimeSpan), totalTicks); 887 } 888 else if (sox.Args.Count == 5) { 889 // TimeSpan(days, hours, minutes, seconds, milliseconds) 890 SqlExpression milliseconds = sql.ConvertToBigint(sox.Args[4]); 891 SqlExpression ticksFromMs = sql.Multiply(milliseconds, TimeSpan.TicksPerMillisecond); 892 return sql.ConvertTo(typeof(TimeSpan), sql.Add(totalTicks, ticksFromMs)); 893 } 894 } 895 throw Error.UnsupportedTimeSpanConstructorForm(); 896 } 897 VisitMethodCall(SqlMethodCall mc)898 internal override SqlExpression VisitMethodCall(SqlMethodCall mc) { 899 Type declType = mc.Method.DeclaringType; 900 Expression source = mc.SourceExpression; 901 SqlExpression returnValue = null; 902 mc.Object = this.VisitExpression(mc.Object); 903 for (int i = 0, n = mc.Arguments.Count; i < n; i++) { 904 mc.Arguments[i] = this.VisitExpression(mc.Arguments[i]); 905 } 906 if (mc.Method.IsStatic) { 907 if (mc.Method.Name == "op_Explicit" || mc.Method.Name == "op_Implicit") { 908 if (mc.SqlType.CanBeColumn && mc.Arguments[0].SqlType.CanBeColumn) { 909 returnValue = sql.ConvertTo(mc.ClrType, mc.Arguments[0]); 910 } 911 } 912 else if (mc.Method.Name == "Compare" && mc.Arguments.Count == 2 && mc.Method.ReturnType == typeof(int)) { 913 returnValue = this.CreateComparison(mc.Arguments[0], mc.Arguments[1], mc.SourceExpression); 914 } 915 else if (declType == typeof(System.Math)) { 916 returnValue = TranslateMathMethod(mc); 917 } 918 else if (declType == typeof(System.String)) { 919 returnValue = TranslateStringStaticMethod(mc); 920 } 921 else if (declType == typeof(System.Convert)) { 922 returnValue = TranslateConvertStaticMethod(mc); 923 } 924 else if (declType == typeof(SqlMethods)) { 925 returnValue = TranslateSqlMethodsMethod(mc); 926 } 927 else if (declType == typeof(decimal)) { 928 returnValue = TranslateDecimalMethod(mc); 929 } 930 else if (IsVbConversionMethod(mc)) { 931 return TranslateVbConversionMethod(mc); 932 } 933 else if (IsVbCompareString(mc)) { 934 return TranslateVbCompareString(mc); 935 } 936 else if (IsVbLike(mc)) { 937 return TranslateVbLikeString(mc); 938 } 939 940 //Recognized pattern has set return value so return 941 if (returnValue != null) { 942 // Assert here to verify that actual translation stays in sync with 943 // method support logic 944 Debug.Assert(GetMethodSupport(mc) == MethodSupport.Method); 945 return returnValue; 946 } 947 } 948 else { // not static 949 if (mc.Method.Name == "Equals" && mc.Arguments.Count == 1) { 950 return sql.Binary(SqlNodeType.EQ, mc.Object, mc.Arguments[0]); 951 } 952 else if (mc.Method.Name == "GetValueOrDefault" && mc.Method.DeclaringType.IsGenericType 953 && mc.Method.DeclaringType.GetGenericTypeDefinition() == typeof(Nullable<>)) { 954 return TranslateGetValueOrDefaultMethod(mc); 955 } 956 else if (mc.Method.Name == "ToString" && mc.Arguments.Count == 0) { 957 SqlExpression expr = mc.Object; 958 if (!expr.SqlType.IsRuntimeOnlyType) { 959 return sql.ConvertTo(typeof(string), expr); 960 } 961 throw Error.ToStringOnlySupportedForPrimitiveTypes(); 962 } 963 else if (declType == typeof(string)) { 964 return TranslateStringMethod(mc); 965 } 966 else if (declType == typeof(TimeSpan)) { 967 returnValue = TranslateTimeSpanInstanceMethod(mc); 968 } 969 else if (declType == typeof(DateTime)) { 970 returnValue = TranslateDateTimeInstanceMethod(mc); 971 } 972 else if (declType == typeof(DateTimeOffset)) { 973 returnValue = TranslateDateTimeOffsetInstanceMethod(mc); 974 } 975 if (returnValue != null) { 976 // Assert here to verify that actual translation stays in sync with 977 // method support logic 978 Debug.Assert(GetMethodSupport(mc) == MethodSupport.Method); 979 return returnValue; 980 } 981 } 982 throw GetMethodSupportException(mc); 983 } 984 GetMethodSupportException(SqlMethodCall mc)985 internal static Exception GetMethodSupportException(SqlMethodCall mc) { 986 MethodSupport ms = GetMethodSupport(mc); 987 if (ms == MethodSupport.MethodGroup) { 988 // If the method is supported in some form, we want to give a 989 // different exception message to the user 990 return Error.MethodFormHasNoSupportConversionToSql(mc.Method.Name, mc.Method); 991 } else { 992 // No form of the method is supported 993 return Error.MethodHasNoSupportConversionToSql(mc.Method); 994 } 995 } 996 TranslateGetValueOrDefaultMethod(SqlMethodCall mc)997 private SqlExpression TranslateGetValueOrDefaultMethod(SqlMethodCall mc) { 998 if (mc.Arguments.Count == 0) { 999 //mc.Object.ClrType must be Nullable<T> and T is a value type 1000 System.Type clrType = mc.Object.ClrType.GetGenericArguments()[0]; 1001 //the default value of clrType is obtained by Activator.CreateInstance(clrType) 1002 return sql.Binary(SqlNodeType.Coalesce, mc.Object, 1003 sql.ValueFromObject(Activator.CreateInstance(clrType), mc.SourceExpression)); 1004 } 1005 else { 1006 return sql.Binary(SqlNodeType.Coalesce, mc.Object, mc.Arguments[0]); 1007 } 1008 } 1009 TranslateSqlMethodsMethod(SqlMethodCall mc)1010 private SqlExpression TranslateSqlMethodsMethod(SqlMethodCall mc) { 1011 Expression source = mc.SourceExpression; 1012 SqlExpression returnValue = null; 1013 string name = mc.Method.Name; 1014 if (name.StartsWith("DateDiff", StringComparison.Ordinal) && mc.Arguments.Count == 2) { 1015 foreach (string datePart in dateParts) { 1016 if (mc.Method.Name == "DateDiff" + datePart) { 1017 SqlExpression start = mc.Arguments[0]; 1018 SqlExpression end = mc.Arguments[1]; 1019 SqlExpression unit = new SqlVariable(typeof(void), null, datePart, source); 1020 return sql.FunctionCall(typeof(int), "DATEDIFF", 1021 new SqlExpression[] { unit, start, end }, source); 1022 } 1023 } 1024 } 1025 else if (name == "Like") { 1026 if (mc.Arguments.Count == 2) { 1027 return sql.Like(mc.Arguments[0], mc.Arguments[1], null, source); 1028 } 1029 else if (mc.Arguments.Count == 3) { 1030 return sql.Like(mc.Arguments[0], mc.Arguments[1], sql.ConvertTo(typeof(string), mc.Arguments[2]), source); 1031 } 1032 } 1033 else if (name == "RawLength") { 1034 SqlExpression length = sql.DATALENGTH(mc.Arguments[0]); 1035 return length; 1036 } 1037 1038 return returnValue; 1039 } 1040 CreateComparison(SqlExpression a, SqlExpression b, Expression source)1041 private SqlExpression CreateComparison(SqlExpression a, SqlExpression b, Expression source) { 1042 SqlExpression lower = sql.Binary(SqlNodeType.LT, a, b); 1043 SqlExpression equal = sql.Binary(SqlNodeType.EQ2V, a, b); 1044 return sql.SearchedCase( 1045 new SqlWhen[] { 1046 new SqlWhen(lower, sql.ValueFromObject(-1, false, source)), 1047 new SqlWhen(equal, sql.ValueFromObject(0, false, source)), 1048 }, 1049 sql.ValueFromObject(1, false, source), source 1050 ); 1051 } 1052 TranslateDateTimeInstanceMethod(SqlMethodCall mc)1053 private SqlExpression TranslateDateTimeInstanceMethod(SqlMethodCall mc) { 1054 SqlExpression returnValue = null; 1055 Expression source = mc.SourceExpression; 1056 if (mc.Method.Name == "CompareTo") { 1057 returnValue = CreateComparison(mc.Object, mc.Arguments[0], source); 1058 } 1059 else if ((mc.Method.Name == "Add" && mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan)) 1060 || (mc.Method.Name == "AddTicks")) { 1061 // 1062 SqlExpression sqlTicks = mc.Arguments[0]; 1063 if (SqlFactory.IsSqlTimeType(mc.Arguments[0])) 1064 { 1065 SqlExpression ns = this.sql.DATEPART("NANOSECOND", mc.Arguments[0]); 1066 SqlExpression ss = this.sql.DATEPART("SECOND", mc.Arguments[0]); 1067 SqlExpression mm = this.sql.DATEPART("MINUTE", mc.Arguments[0]); 1068 SqlExpression hh = this.sql.DATEPART("HOUR", mc.Arguments[0]); 1069 sqlTicks = sql.Add( 1070 sql.Divide(ns, 100), 1071 sql.Multiply( 1072 sql.Add( 1073 sql.Multiply(sql.ConvertToBigint(hh), 3600000), 1074 sql.Multiply(sql.ConvertToBigint(mm), 60000), 1075 sql.Multiply(sql.ConvertToBigint(ss), 1000) 1076 ), 1077 10000) 1078 ); 1079 } 1080 return this.CreateDateTimeFromDateAndTicks(mc.Object, sqlTicks, source); 1081 } 1082 else if (mc.Method.Name == "AddMonths") { 1083 // date + m --> DATEADD(month, @m, @date) 1084 returnValue = sql.DATEADD("MONTH", mc.Arguments[0], mc.Object); 1085 } 1086 else if (mc.Method.Name == "AddYears") { 1087 // date + y --> DATEADD(year, @y, @date) 1088 returnValue = sql.DATEADD("YEAR", mc.Arguments[0], mc.Object); 1089 } 1090 else if (mc.Method.Name == "AddMilliseconds") { 1091 // date + ms --> DATEADD(ms, @ms, @date) 1092 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, mc.Arguments[0], source); 1093 } 1094 // The following .Net methods take a double parameter, but the SQL function DATEADD only uses the integral part. 1095 // To make up for this, we compute the number of milliseconds and use DATEADD(ms,...) instead of DATEADD(day,...) etc. 1096 else if (mc.Method.Name == "AddSeconds") { 1097 SqlExpression ms = sql.Multiply(mc.Arguments[0], 1000); 1098 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source); 1099 } 1100 else if (mc.Method.Name == "AddMinutes") { 1101 SqlExpression ms = sql.Multiply(mc.Arguments[0], 60000); 1102 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source); 1103 } 1104 else if (mc.Method.Name == "AddHours") { 1105 SqlExpression ms = sql.Multiply(mc.Arguments[0], 3600000); 1106 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source); 1107 } 1108 else if (mc.Method.Name == "AddDays") { 1109 SqlExpression ms = sql.Multiply(mc.Arguments[0], 86400000); 1110 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source); 1111 } 1112 return returnValue; 1113 } 1114 TranslateDateTimeOffsetInstanceMethod(SqlMethodCall mc)1115 private SqlExpression TranslateDateTimeOffsetInstanceMethod(SqlMethodCall mc) { 1116 SqlExpression returnValue = null; 1117 Expression source = mc.SourceExpression; 1118 if (mc.Method.Name == "CompareTo") { 1119 returnValue = CreateComparison(mc.Object, mc.Arguments[0], source); 1120 } 1121 else if ((mc.Method.Name == "Add" && mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan)) 1122 || (mc.Method.Name == "AddTicks")) { 1123 SqlExpression ns = sql.DATEPART("NANOSECOND", mc.Arguments[0]); 1124 SqlExpression ss = sql.DATEPART("SECOND", mc.Arguments[0]); 1125 SqlExpression mi = sql.DATEPART("MINUTE", mc.Arguments[0]); 1126 SqlExpression hh = sql.DATEPART("HOUR", mc.Arguments[0]); 1127 1128 SqlExpression ticks = sql.Add( 1129 sql.Divide(ns, 100), 1130 sql.Multiply( 1131 sql.Add( 1132 sql.Multiply(sql.ConvertToBigint(hh), 3600000), 1133 sql.Multiply(sql.ConvertToBigint(mi), 60000), 1134 sql.Multiply(sql.ConvertToBigint(ss), 1000) 1135 ), 1136 10000 // 1 millisecond = 10000 ticks 1137 ) 1138 ); 1139 returnValue = this.CreateDateTimeOffsetFromDateAndTicks(mc.Object, ticks, source); 1140 } 1141 else if (mc.Method.Name == "AddMonths") { 1142 // date + m --> DATEADD(month, @m, @date) 1143 returnValue = sql.DATETIMEOFFSETADD("MONTH", mc.Arguments[0], mc.Object); 1144 } 1145 else if (mc.Method.Name == "AddYears") { 1146 // date + y --> DATEADD(year, @y, @date) 1147 returnValue = sql.DATETIMEOFFSETADD("YEAR", mc.Arguments[0], mc.Object); 1148 } 1149 else if (mc.Method.Name == "AddMilliseconds") { 1150 // date + ms --> DATEADD(ms, @ms, @date) 1151 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, mc.Arguments[0], source); 1152 } 1153 // The following .Net methods take a double parameter, but the SQL function DATEADD only uses the integral part. 1154 // To make up for this, we compute the number of milliseconds and use DATEADD(ms,...) instead of DATEADD(day,...) etc. 1155 else if (mc.Method.Name == "AddSeconds") { 1156 SqlExpression ms = sql.Multiply(mc.Arguments[0], 1000); 1157 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source); 1158 } 1159 else if (mc.Method.Name == "AddMinutes") { 1160 SqlExpression ms = sql.Multiply(mc.Arguments[0], 60000); 1161 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source); 1162 } 1163 else if (mc.Method.Name == "AddHours") { 1164 SqlExpression ms = sql.Multiply(mc.Arguments[0], 3600000); 1165 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source); 1166 } 1167 else if (mc.Method.Name == "AddDays") { 1168 SqlExpression ms = sql.Multiply(mc.Arguments[0], 86400000); 1169 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source); 1170 } 1171 return returnValue; 1172 } 1173 TranslateTimeSpanInstanceMethod(SqlMethodCall mc)1174 private SqlExpression TranslateTimeSpanInstanceMethod(SqlMethodCall mc) { 1175 SqlExpression returnValue = null; 1176 Expression source = mc.SourceExpression; 1177 if (mc.Method.Name == "Add") { 1178 returnValue = sql.Add(mc.Object, mc.Arguments[0]); 1179 } 1180 else if (mc.Method.Name == "Subtract") { 1181 returnValue = sql.Subtract(mc.Object, mc.Arguments[0]); 1182 } 1183 else if (mc.Method.Name == "CompareTo") { 1184 returnValue = CreateComparison(mc.Object, mc.Arguments[0], source); 1185 } 1186 else if (mc.Method.Name == "Duration") { 1187 if (SqlFactory.IsSqlTimeType(mc.Object)) 1188 return mc.Object; 1189 1190 returnValue = sql.FunctionCall(typeof(TimeSpan), "ABS", new SqlExpression[] { mc.Object }, source); 1191 } 1192 else if (mc.Method.Name == "Negate") { 1193 returnValue = sql.Unary(SqlNodeType.Negate, mc.Object, source); 1194 } 1195 return returnValue; 1196 } 1197 TranslateConvertStaticMethod(SqlMethodCall mc)1198 private SqlExpression TranslateConvertStaticMethod(SqlMethodCall mc) { 1199 SqlExpression returnValue = null; 1200 if (mc.Arguments.Count == 1) { 1201 SqlExpression expr = mc.Arguments[0]; 1202 Type targetType = null; 1203 ProviderType providerType = null; 1204 switch (mc.Method.Name) { 1205 case "ToBoolean": 1206 targetType = typeof(bool); 1207 break; 1208 case "ToDecimal": 1209 targetType = typeof(decimal); 1210 break; 1211 case "ToByte": 1212 targetType = typeof(byte); 1213 break; 1214 case "ToChar": { 1215 targetType = typeof(char); 1216 if (expr.SqlType.IsChar) { 1217 providerType = sql.TypeProvider.From(targetType, 1); 1218 } 1219 break; 1220 } 1221 case "ToDateTime": 1222 Type nnType = TypeSystem.GetNonNullableType(expr.ClrType); 1223 if (nnType == typeof(string) || nnType == typeof(DateTime)) { 1224 targetType = typeof(DateTime); 1225 } 1226 else { 1227 throw Error.ConvertToDateTimeOnlyForDateTimeOrString(); 1228 } 1229 break; 1230 // not applicable: "ToDateTimeOffset" 1231 case "ToDouble": 1232 targetType = typeof(double); 1233 break; 1234 case "ToInt16": 1235 targetType = typeof(Int16); 1236 break; 1237 case "ToInt32": 1238 targetType = typeof(Int32); 1239 break; 1240 case "ToInt64": 1241 targetType = typeof(Int64); 1242 break; 1243 case "ToSingle": 1244 targetType = typeof(float); 1245 break; 1246 case "ToString": 1247 targetType = typeof(string); 1248 break; 1249 // Unsupported 1250 case "ToSByte": 1251 case "ToUInt16": 1252 case "ToUInt32": 1253 case "ToUInt64": 1254 default: 1255 throw GetMethodSupportException(mc); 1256 } 1257 // Since boolean literals and Int32 both map to the same provider type, we must 1258 // special case boolean types so we don't miss conversions. Below we catch 1259 // conversions from bool->int (Convert.ToInt32(bool)) and ensure the conversion 1260 // remains. 1261 if (sql.TypeProvider.From(targetType) != expr.SqlType || 1262 (expr.ClrType == typeof(bool) && targetType == typeof(int))) { 1263 // do the conversions that would be done for a cast "(<targetType>) expression" 1264 returnValue = sql.ConvertTo(targetType, expr); 1265 } 1266 else if (targetType != null) { 1267 if (sql.TypeProvider.From(targetType) != expr.SqlType) { 1268 // do the conversions that would be done for a cast "(<targetType>) expression" 1269 returnValue = sql.ConvertTo(targetType, expr); 1270 } 1271 else if (targetType != expr.ClrType && 1272 (TypeSystem.GetNonNullableType(targetType) == TypeSystem.GetNonNullableType(expr.ClrType))) { 1273 // types are same except for nullability, so lift the type 1274 returnValue = new SqlLift(targetType, expr, expr.SourceExpression); 1275 } 1276 else { 1277 returnValue = expr; 1278 } 1279 } 1280 } 1281 return returnValue; 1282 } 1283 TranslateDateTimeBinary(SqlBinary bo)1284 private SqlExpression TranslateDateTimeBinary(SqlBinary bo) { 1285 bool resultNullable = TypeSystem.IsNullableType(bo.ClrType); 1286 Type rightType = TypeSystem.GetNonNullableType(bo.Right.ClrType); 1287 switch (bo.NodeType) { 1288 case SqlNodeType.Sub: 1289 if (rightType == typeof(DateTime)) { 1290 // if either of the arguments is nullable, set result type to nullable. 1291 Type resultType = bo.ClrType; 1292 SqlExpression end = bo.Left; 1293 SqlExpression start = bo.Right; 1294 SqlExpression day = new SqlVariable(typeof(void), null, "DAY", bo.SourceExpression); 1295 SqlExpression ms = new SqlVariable(typeof(void), null, "MILLISECOND", bo.SourceExpression); 1296 1297 // DATEDIFF(MILLISECONDS,...) does not work for more then 24 days, since result has to fit int. 1298 // So compute the number of days first, and then find out the number of milliseconds needed in addition to that. 1299 SqlExpression intDays = sql.FunctionCall(typeof(int), "DATEDIFF", 1300 new SqlExpression[] { day, start, end }, bo.SourceExpression); 1301 SqlExpression startPlusDays = sql.FunctionCall( 1302 typeof(DateTime), "DATEADD", new SqlExpression[] { day, intDays, start }, bo.SourceExpression); 1303 SqlExpression intMSec = sql.FunctionCall(typeof(int), "DATEDIFF", 1304 new SqlExpression[] { ms, startPlusDays, end }, bo.SourceExpression); 1305 SqlExpression result = sql.Multiply(sql.Add(sql.Multiply(sql.ConvertToBigint(intDays), 86400000), intMSec), 10000); // 1 millisecond = 10000 ticks 1306 return sql.ConvertTo(resultType, result); 1307 } 1308 if (rightType == typeof(DateTimeOffset)) { 1309 Debug.Assert(TypeSystem.GetNonNullableType(bo.Left.ClrType) == typeof(DateTimeOffset)); 1310 // if either of the arguments is nullable, set result type to nullable. 1311 Type resultType = bo.ClrType; 1312 SqlExpression end = bo.Left; 1313 SqlExpression start = bo.Right; 1314 SqlExpression day = new SqlVariable(typeof(void), null, "DAY", bo.SourceExpression); 1315 SqlExpression ms = new SqlVariable(typeof(void), null, "MILLISECOND", bo.SourceExpression); 1316 SqlExpression us = new SqlVariable(typeof(void), null, "MICROSECOND", bo.SourceExpression); 1317 SqlExpression ns = new SqlVariable(typeof(void), null, "NANOSECOND", bo.SourceExpression); 1318 1319 // compute the number of days first, and then find out the number of milliseconds needed in addition to that. 1320 SqlExpression intDays = sql.FunctionCall(typeof(int), "DATEDIFF", new SqlExpression[] { day, start, end }, bo.SourceExpression); 1321 SqlExpression startPlusDays = sql.FunctionCall(typeof(DateTimeOffset), "DATEADD", new SqlExpression[] { day, intDays, start }, bo.SourceExpression); 1322 SqlExpression intMSec = sql.FunctionCall(typeof(int), "DATEDIFF", new SqlExpression[] { ms, startPlusDays, end }, bo.SourceExpression); 1323 SqlExpression startPlusDaysPlusMsec = sql.FunctionCall(typeof(DateTimeOffset), "DATEADD", new SqlExpression[] { ms, intMSec, startPlusDays }, bo.SourceExpression); 1324 SqlExpression intUSec = sql.FunctionCall(typeof(int), "DATEDIFF", new SqlExpression[] { us, startPlusDaysPlusMsec, end }, bo.SourceExpression); 1325 SqlExpression startPlusDaysPlusMsecPlusUSec = sql.FunctionCall(typeof(DateTimeOffset), "DATEADD", new SqlExpression[] { us, intUSec, startPlusDaysPlusMsec }, bo.SourceExpression); 1326 SqlExpression intNSec = sql.FunctionCall(typeof(int), "DATEDIFF", new SqlExpression[] { ns, startPlusDaysPlusMsecPlusUSec, end }, bo.SourceExpression); 1327 SqlExpression startPlusDaysPlusMsecPlusUSecPlusNSec = sql.FunctionCall(typeof(DateTimeOffset), "DATEADD", new SqlExpression[] { ns, intNSec, startPlusDaysPlusMsecPlusUSec }, bo.SourceExpression); 1328 1329 SqlExpression result = sql.Add( 1330 sql.Divide(intNSec, 100), 1331 sql.Multiply(intUSec, 10), 1332 sql.Multiply( 1333 sql.Add( 1334 sql.Multiply(sql.ConvertToBigint(intDays), 86400000), 1335 intMSec 1336 ), 1337 10000) 1338 ); 1339 1340 return sql.ConvertTo(resultType, result); 1341 } 1342 else if (rightType == typeof(TimeSpan)) { 1343 SqlExpression right = bo.Right; 1344 if (SqlFactory.IsSqlTimeType(bo.Right)) { 1345 SqlExpression ns = sql.DATEPART("NANOSECOND", right); 1346 SqlExpression ss = sql.DATEPART("SECOND", right); 1347 SqlExpression mi = sql.DATEPART("MINUTE", right); 1348 SqlExpression hh = sql.DATEPART("HOUR", right); 1349 1350 right = sql.Add( 1351 sql.Divide(ns, 100), 1352 sql.Multiply( 1353 sql.Add( 1354 sql.Multiply(sql.ConvertToBigint(hh), 3600000), 1355 sql.Multiply(sql.ConvertToBigint(mi), 60000), 1356 sql.Multiply(sql.ConvertToBigint(ss), 1000) 1357 ), 1358 10000 // 1 millisecond = 10000 ticks 1359 ) 1360 ); 1361 } 1362 1363 return TypeSystem.GetNonNullableType(bo.Left.ClrType) == typeof(DateTimeOffset) ? 1364 CreateDateTimeOffsetFromDateAndTicks( 1365 bo.Left, 1366 sql.Unary(SqlNodeType.Negate, right, bo.SourceExpression), 1367 bo.SourceExpression, resultNullable 1368 ) : 1369 CreateDateTimeFromDateAndTicks( 1370 bo.Left, 1371 sql.Unary(SqlNodeType.Negate, right, bo.SourceExpression), 1372 bo.SourceExpression, resultNullable 1373 ); 1374 } 1375 break; 1376 case SqlNodeType.Add: 1377 if (rightType == typeof(TimeSpan)) { 1378 if (SqlFactory.IsSqlTimeType(bo.Right)) { 1379 return sql.AddTimeSpan(bo.Left, bo.Right, resultNullable); 1380 } else if (TypeSystem.GetNonNullableType(bo.Left.ClrType) == typeof(DateTimeOffset)) { 1381 return CreateDateTimeOffsetFromDateAndTicks(bo.Left, bo.Right, bo.SourceExpression, resultNullable); 1382 } 1383 1384 return CreateDateTimeFromDateAndTicks(bo.Left, bo.Right, bo.SourceExpression, resultNullable); 1385 } 1386 break; 1387 } 1388 return bo; 1389 } 1390 TranslateDecimalMethod(SqlMethodCall mc)1391 internal SqlExpression TranslateDecimalMethod(SqlMethodCall mc) { 1392 Expression source = mc.SourceExpression; 1393 if (mc.Method.IsStatic) { 1394 if (mc.Arguments.Count == 2) { 1395 switch (mc.Method.Name) { 1396 case "Multiply": 1397 return sql.Binary(SqlNodeType.Mul, mc.Arguments[0], mc.Arguments[1]); 1398 case "Divide": 1399 return sql.Binary(SqlNodeType.Div, mc.Arguments[0], mc.Arguments[1]); 1400 case "Subtract": 1401 return sql.Binary(SqlNodeType.Sub, mc.Arguments[0], mc.Arguments[1]); 1402 case "Add": 1403 return sql.Binary(SqlNodeType.Add, mc.Arguments[0], mc.Arguments[1]); 1404 case "Remainder": 1405 return sql.Binary(SqlNodeType.Mod, mc.Arguments[0], mc.Arguments[1]); 1406 case "Round": 1407 // ROUND (x, y) 1408 return sql.FunctionCall(mc.Method.ReturnType, "ROUND", mc.Arguments, mc.SourceExpression); 1409 } 1410 } 1411 else if (mc.Arguments.Count == 1) { 1412 switch (mc.Method.Name) { 1413 case "Negate": 1414 return sql.Unary(SqlNodeType.Negate, mc.Arguments[0], source); 1415 case "Floor": 1416 case "Truncate": 1417 // Truncate(x) --> ROUND (x, 0, 1) 1418 return sql.FunctionCall(mc.Method.ReturnType, "ROUND", 1419 new SqlExpression[] { 1420 mc.Arguments[0], 1421 sql.ValueFromObject(0, false, mc.SourceExpression), 1422 sql.ValueFromObject(1, false, mc.SourceExpression) 1423 }, 1424 mc.SourceExpression); 1425 case "Round": 1426 // ROUND (x, 0) 1427 return sql.FunctionCall(mc.Method.ReturnType, "ROUND", 1428 new SqlExpression[] { 1429 mc.Arguments[0], 1430 sql.ValueFromObject(0, false, mc.SourceExpression) 1431 }, 1432 mc.SourceExpression); 1433 default: 1434 if (mc.Method.Name.StartsWith("To", StringComparison.Ordinal)) { 1435 return this.TranslateConvertStaticMethod(mc); 1436 } 1437 break; 1438 } 1439 } 1440 } 1441 throw GetMethodSupportException(mc); 1442 } 1443 TranslateStringStaticMethod(SqlMethodCall mc)1444 private SqlExpression TranslateStringStaticMethod(SqlMethodCall mc) { 1445 SqlExpression returnValue = null; 1446 Expression source = mc.SourceExpression; 1447 #region String Methods 1448 if (mc.Method.Name == "Concat") { 1449 SqlClientArray arr = mc.Arguments[0] as SqlClientArray; 1450 List<SqlExpression> exprs = null; 1451 if (arr != null) { 1452 exprs = arr.Expressions; 1453 } 1454 else { 1455 exprs = mc.Arguments; 1456 } 1457 if (exprs.Count == 0) { 1458 returnValue = sql.ValueFromObject("", false, source); 1459 } 1460 else { 1461 SqlExpression sum; 1462 if (exprs[0].SqlType.IsString || exprs[0].SqlType.IsChar) { 1463 sum = exprs[0]; 1464 } 1465 else { 1466 sum = sql.ConvertTo(typeof(string), exprs[0]); 1467 } 1468 for (int i = 1; i < exprs.Count; i++) { 1469 if (exprs[i].SqlType.IsString || exprs[i].SqlType.IsChar) { 1470 sum = sql.Concat(sum, exprs[i]); 1471 } 1472 else { 1473 sum = sql.Concat(sum, sql.ConvertTo(typeof(string), exprs[i])); 1474 } 1475 } 1476 returnValue = sum; 1477 } 1478 } 1479 else if ((mc.Method.Name == "Equals") && (mc.Arguments.Count == 2)) { 1480 returnValue = sql.Binary(SqlNodeType.EQ2V, mc.Arguments[0], mc.Arguments[1]); 1481 } 1482 else if ((mc.Method.Name == "Compare") && (mc.Arguments.Count == 2)) { 1483 returnValue = CreateComparison(mc.Arguments[0], mc.Arguments[1], source); 1484 } 1485 #endregion 1486 throw GetMethodSupportException(mc); 1487 //return returnValue; 1488 } 1489 1490 //helper functions CreateFunctionCallStatic1(Type type, string functionName, List<SqlExpression> arguments, Expression source)1491 private SqlExpression CreateFunctionCallStatic1(Type type, string functionName, List<SqlExpression> arguments, Expression source) { 1492 return sql.FunctionCall(type, functionName, new SqlExpression[] { arguments[0] }, source); 1493 } 1494 CreateFunctionCallStatic2(Type type, string functionName, List<SqlExpression> arguments, Expression source)1495 private SqlExpression CreateFunctionCallStatic2(Type type, string functionName, List<SqlExpression> arguments, Expression source) { 1496 return sql.FunctionCall(type, functionName, new SqlExpression[] { arguments[0], arguments[1] }, source); 1497 } 1498 TranslateStringMethod(SqlMethodCall mc)1499 private SqlExpression TranslateStringMethod(SqlMethodCall mc) { 1500 Expression source = mc.SourceExpression; 1501 1502 switch (mc.Method.Name) { 1503 case "Contains": 1504 if (mc.Arguments.Count == 1) { 1505 SqlExpression pattern = mc.Arguments[0]; 1506 SqlExpression escape = null; 1507 bool needsEscape = true; 1508 1509 if (pattern.NodeType == SqlNodeType.Value) { 1510 string unescapedText = (string)((SqlValue)pattern).Value; 1511 string patternText = SqlHelpers.GetStringContainsPattern(unescapedText, '~', out needsEscape); 1512 pattern = sql.ValueFromObject(patternText, true, pattern.SourceExpression); 1513 } 1514 else if (pattern.NodeType == SqlNodeType.ClientParameter) { 1515 SqlClientParameter cp = (SqlClientParameter)pattern; 1516 Func<string, char, string> getStringContainsPatternForced = SqlHelpers.GetStringContainsPatternForced; 1517 pattern = new SqlClientParameter( 1518 cp.ClrType, cp.SqlType, 1519 Expression.Lambda( 1520 Expression.Invoke(Expression.Constant(getStringContainsPatternForced), cp.Accessor.Body, Expression.Constant('~')), 1521 cp.Accessor.Parameters[0] 1522 ), 1523 cp.SourceExpression 1524 ); 1525 } 1526 else { 1527 throw Error.NonConstantExpressionsNotSupportedFor("String.Contains"); 1528 } 1529 1530 if (needsEscape) { 1531 escape = sql.ValueFromObject("~", false, source); 1532 } 1533 1534 return sql.Like(mc.Object, pattern, escape, source); 1535 } 1536 break; 1537 case "StartsWith": 1538 if (mc.Arguments.Count == 1) { 1539 SqlExpression pattern = mc.Arguments[0]; 1540 SqlExpression escape = null; 1541 bool needsEscape = true; 1542 1543 if (pattern.NodeType == SqlNodeType.Value) { 1544 string unescapedText = (string)((SqlValue)pattern).Value; 1545 string patternText = SqlHelpers.GetStringStartsWithPattern(unescapedText, '~', out needsEscape); 1546 pattern = sql.ValueFromObject(patternText, true, pattern.SourceExpression); 1547 } 1548 else if (pattern.NodeType == SqlNodeType.ClientParameter) { 1549 SqlClientParameter cp = (SqlClientParameter)pattern; 1550 Func<string, char, string> getStringStartsWithPatternForced = SqlHelpers.GetStringStartsWithPatternForced; 1551 pattern = new SqlClientParameter( 1552 cp.ClrType, cp.SqlType, 1553 Expression.Lambda( 1554 Expression.Invoke(Expression.Constant(getStringStartsWithPatternForced), cp.Accessor.Body, Expression.Constant('~')), 1555 cp.Accessor.Parameters[0] 1556 ), 1557 cp.SourceExpression 1558 ); 1559 } 1560 else { 1561 throw Error.NonConstantExpressionsNotSupportedFor("String.StartsWith"); 1562 } 1563 1564 if (needsEscape) { 1565 escape = sql.ValueFromObject("~", false, source); 1566 } 1567 1568 return sql.Like(mc.Object, pattern, escape, source); 1569 } 1570 break; 1571 case "EndsWith": 1572 if (mc.Arguments.Count == 1) { 1573 SqlExpression pattern = mc.Arguments[0]; 1574 SqlExpression escape = null; 1575 bool needsEscape = true; 1576 1577 if (pattern.NodeType == SqlNodeType.Value) { 1578 string unescapedText = (string)((SqlValue)pattern).Value; 1579 string patternText = SqlHelpers.GetStringEndsWithPattern(unescapedText, '~', out needsEscape); 1580 pattern = sql.ValueFromObject(patternText, true, pattern.SourceExpression); 1581 } 1582 else if (pattern.NodeType == SqlNodeType.ClientParameter) { 1583 SqlClientParameter cp = (SqlClientParameter)pattern; 1584 Func<string, char, string> getStringEndsWithPatternForced = SqlHelpers.GetStringEndsWithPatternForced; 1585 pattern = new SqlClientParameter( 1586 cp.ClrType, cp.SqlType, 1587 Expression.Lambda( 1588 Expression.Invoke(Expression.Constant(getStringEndsWithPatternForced), cp.Accessor.Body, Expression.Constant('~')), 1589 cp.Accessor.Parameters[0] 1590 ), 1591 cp.SourceExpression 1592 ); 1593 } 1594 else { 1595 throw Error.NonConstantExpressionsNotSupportedFor("String.EndsWith"); 1596 } 1597 1598 if (needsEscape) { 1599 escape = sql.ValueFromObject("~", false, source); 1600 } 1601 1602 return sql.Like(mc.Object, pattern, escape, source); 1603 } 1604 break; 1605 case "IndexOf": 1606 if (mc.Arguments.Count == 1) { 1607 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { 1608 throw Error.ArgumentNull("value"); 1609 } 1610 // if the search string is empty, return zero 1611 SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); 1612 SqlWhen when = new SqlWhen(lenZeroExpr, sql.ValueFromObject(0, source)); 1613 SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX", 1614 new SqlExpression[] { 1615 mc.Arguments[0], 1616 mc.Object }, 1617 source), 1); 1618 return sql.SearchedCase(new SqlWhen[] { when }, @else, source); 1619 1620 } 1621 else if (mc.Arguments.Count == 2) { 1622 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { 1623 throw Error.ArgumentNull("value"); 1624 } 1625 if (mc.Arguments[1].ClrType == typeof(StringComparison)) { 1626 throw Error.IndexOfWithStringComparisonArgNotSupported(); 1627 } 1628 // if the search string is empty and the start index is in bounds, 1629 // return the start index 1630 SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); 1631 lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(mc.Object))); 1632 SqlWhen when = new SqlWhen(lenZeroExpr, mc.Arguments[1]); 1633 SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX", 1634 new SqlExpression[] { 1635 mc.Arguments[0], 1636 mc.Object, 1637 sql.Add(mc.Arguments[1], 1) 1638 }, 1639 source), 1); 1640 return sql.SearchedCase(new SqlWhen[] { when }, @else, source); 1641 } 1642 else if (mc.Arguments.Count == 3) { 1643 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { 1644 throw Error.ArgumentNull("value"); 1645 } 1646 if (mc.Arguments[2].ClrType == typeof(StringComparison)) { 1647 throw Error.IndexOfWithStringComparisonArgNotSupported(); 1648 } 1649 1650 // s1.IndexOf(s2, start, count) -> CHARINDEX(@s2, SUBSTRING(@s1, 1, @start + @count), @start + 1) 1651 // if the search string is empty and the start index is in bounds, 1652 // return the start index 1653 SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); 1654 lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(mc.Object))); 1655 SqlWhen when = new SqlWhen(lenZeroExpr, mc.Arguments[1]); 1656 SqlExpression substring = sql.FunctionCall( 1657 typeof(string), "SUBSTRING", 1658 new SqlExpression[] { 1659 mc.Object, 1660 sql.ValueFromObject(1, false, source), 1661 sql.Add(mc.Arguments[1], mc.Arguments[2]) 1662 }, 1663 source); 1664 SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX", 1665 new SqlExpression[] { 1666 mc.Arguments[0], 1667 substring, 1668 sql.Add(mc.Arguments[1], 1) 1669 }, 1670 source), 1); 1671 return sql.SearchedCase(new SqlWhen[] { when }, @else, source); 1672 } 1673 break; 1674 case "LastIndexOf": 1675 if (mc.Arguments.Count == 1) { 1676 // s.LastIndexOf(part) --> 1677 // CASE WHEN CHARINDEX(@part, @s) = 0 THEN -1 1678 // ELSE 1 + CLRLENGTH(@s) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@s)) 1679 // END 1680 SqlExpression exprPart = mc.Arguments[0]; 1681 if (exprPart is SqlValue && ((SqlValue)exprPart).Value == null) { 1682 throw Error.ArgumentNull("value"); 1683 } 1684 SqlExpression exprS = mc.Object; 1685 SqlExpression reverseS = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { exprS }, source); 1686 SqlExpression reversePart = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { exprPart }, source); 1687 SqlExpression charIndex = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { exprPart, exprS }, source); 1688 SqlExpression charIndexOfReverse = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { reversePart, reverseS }, source); 1689 SqlExpression notContained = sql.Binary(SqlNodeType.EQ, charIndex, sql.ValueFromObject(0, false, source)); 1690 SqlExpression len1 = sql.CLRLENGTH(exprS); 1691 SqlExpression len2 = sql.CLRLENGTH(exprPart); 1692 SqlExpression elseCase = sql.Add(sql.ValueFromObject(1, false, source), sql.Subtract(len1, sql.Add(len2, charIndexOfReverse))); 1693 1694 SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source)); 1695 1696 // if the search string is empty, return zero 1697 SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); 1698 SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, sql.Subtract(sql.CLRLENGTH(exprS), 1)); 1699 1700 return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained }, 1701 elseCase, source); 1702 } 1703 else if (mc.Arguments.Count == 2) { 1704 // s.LastIndexOf(part,i) --> 1705 // set @first = LEFT(@s, @i+1) 1706 // CASE WHEN CHARINDEX(@part, @first) = 0 THEN -1 1707 // ELSE 1 + CLRLENGTH(@first) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@first)) 1708 // END 1709 if (mc.Arguments[1].ClrType == typeof(StringComparison)) { 1710 throw Error.LastIndexOfWithStringComparisonArgNotSupported(); 1711 } 1712 SqlExpression s = mc.Object; 1713 SqlExpression part = mc.Arguments[0]; 1714 if (part is SqlValue && ((SqlValue)part).Value == null) { 1715 throw Error.ArgumentNull("value"); 1716 } 1717 SqlExpression i = mc.Arguments[1]; 1718 SqlExpression first = sql.FunctionCall(typeof(string), "LEFT", new SqlExpression[] { s, sql.Add(i, 1) }, source); 1719 SqlExpression reverseFirst = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { first }, source); 1720 SqlExpression reversePart = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { part }, source); 1721 SqlExpression charIndex = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { part, first }, source); 1722 SqlExpression charIndexOfReverse = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { reversePart, reverseFirst }, source); 1723 SqlExpression notContained = sql.Binary(SqlNodeType.EQ, charIndex, sql.ValueFromObject(0, false, source)); 1724 SqlExpression len1 = sql.CLRLENGTH(first); 1725 SqlExpression len2 = sql.CLRLENGTH(part); 1726 SqlExpression elseCase = sql.Add(sql.ValueFromObject(1, false, source), sql.Subtract(len1, sql.Add(len2, charIndexOfReverse))); 1727 1728 SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source)); 1729 1730 // if the search string is empty and the start index is in bounds, 1731 // return the start index 1732 SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); 1733 lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(s))); 1734 SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, mc.Arguments[1]); 1735 1736 return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained }, 1737 elseCase, source); 1738 } 1739 else if (mc.Arguments.Count == 3) { 1740 // s.LastIndexOf(part, i, count) --> 1741 // set @first = LEFT(@s, @i+1) 1742 // CASE WHEN (CHARINDEX(@part, @first) = 0) OR (1 + CLRLENGTH(@first) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@first))) < (@i - @count) THEN -1 1743 // ELSE 1 + CLRLENGTH(@first) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@first)) 1744 // END 1745 if (mc.Arguments[2].ClrType == typeof(StringComparison)) { 1746 throw Error.LastIndexOfWithStringComparisonArgNotSupported(); 1747 } 1748 SqlExpression s = mc.Object; 1749 SqlExpression part = mc.Arguments[0]; 1750 if (part is SqlValue && ((SqlValue)part).Value == null) { 1751 throw Error.ArgumentNull("value"); 1752 } 1753 SqlExpression i = mc.Arguments[1]; 1754 SqlExpression count = mc.Arguments[2]; 1755 SqlExpression first = sql.FunctionCall(typeof(string), "LEFT", new SqlExpression[] { s, sql.Add(i, 1) }, source); 1756 SqlExpression reverseFirst = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { first }, source); 1757 SqlExpression reversePart = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { part }, source); 1758 SqlExpression charIndex = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { part, first }, source); 1759 SqlExpression charIndexOfReverse = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { reversePart, reverseFirst }, source); 1760 SqlExpression len1 = sql.CLRLENGTH(first); 1761 SqlExpression len2 = sql.CLRLENGTH(part); 1762 SqlExpression elseCase = sql.Add(sql.ValueFromObject(1, false, source), sql.Subtract(len1, sql.Add(len2, charIndexOfReverse))); 1763 SqlExpression notContained = sql.Binary(SqlNodeType.EQ, charIndex, sql.ValueFromObject(0, false, source)); 1764 notContained = sql.OrAccumulate(notContained, sql.Binary(SqlNodeType.LE, elseCase, sql.Subtract(i, count))); 1765 1766 SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source)); 1767 1768 // if the search string is empty and the start index is in bounds, 1769 // return the start index 1770 SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); 1771 lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(s))); 1772 SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, mc.Arguments[1]); 1773 1774 return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained }, 1775 elseCase, source); 1776 } 1777 break; 1778 case "Insert": 1779 // Create STUFF(str, insertPos + 1, 0, strToInsert) 1780 if (mc.Arguments.Count == 2) { 1781 if (mc.Arguments[1] is SqlValue && ((SqlValue)mc.Arguments[1]).Value == null) { 1782 throw Error.ArgumentNull("value"); 1783 } 1784 SqlFunctionCall stuffCall = sql.FunctionCall( 1785 typeof(string), "STUFF", 1786 new SqlExpression[] { 1787 mc.Object, 1788 sql.Add(mc.Arguments[0], 1), 1789 sql.ValueFromObject(0, false, source), 1790 mc.Arguments[1] 1791 }, 1792 source); 1793 // We construct SQL to handle the special case of when the length of the string 1794 // to modify is equal to the insert position. This occurs if the string is empty and 1795 // the insert pos is 0, or when the string is not empty, and the insert pos indicates 1796 // the end of the string. 1797 // CASE WHEN (CLRLENGTH(str) = insertPos) THEN str + strToInsert ELSE STUFF(...) 1798 SqlExpression insertingAtEnd = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Object), mc.Arguments[0]); 1799 SqlExpression stringConcat = sql.Concat(mc.Object, mc.Arguments[1]); 1800 1801 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(insertingAtEnd, stringConcat) }, stuffCall, source); 1802 } 1803 break; 1804 case "PadLeft": 1805 if (mc.Arguments.Count == 1) { 1806 // s.PadLeft(i) --> 1807 // CASE WHEN CLRLENGTH(@s)>= @i THEN @s 1808 // ELSE SPACE(@i-CLRLENGTH(@s)) + @s 1809 // END 1810 SqlExpression exprS = mc.Object; 1811 SqlExpression exprI = mc.Arguments[0]; 1812 SqlExpression len2 = sql.CLRLENGTH(exprS); 1813 SqlExpression dontChange = sql.Binary(SqlNodeType.GE, len2, exprI); 1814 SqlExpression numSpaces = sql.Subtract(exprI, len2); 1815 SqlExpression padding = sql.FunctionCall(typeof(string), "SPACE", new SqlExpression[] { numSpaces }, source); 1816 SqlExpression elseCase = sql.Concat(padding, exprS); 1817 1818 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); 1819 } 1820 else if (mc.Arguments.Count == 2) { 1821 // s.PadLeft(i,c) --> 1822 // CASE WHEN CLRLENGTH(@s) >= @i THEN @s 1823 // ELSE REPLICATE(@c, @i - CLRLENGTH(@s)) + @s 1824 // END 1825 SqlExpression exprS = mc.Object; 1826 SqlExpression exprI = mc.Arguments[0]; 1827 SqlExpression exprC = mc.Arguments[1]; 1828 SqlExpression dontChange = sql.Binary(SqlNodeType.GE, sql.CLRLENGTH(exprS), exprI); 1829 SqlExpression len2 = sql.CLRLENGTH(exprS); 1830 SqlExpression numSpaces = sql.Subtract(exprI, len2); 1831 SqlExpression padding = sql.FunctionCall(typeof(string), "REPLICATE", new SqlExpression[] { exprC, numSpaces }, source); 1832 SqlExpression elseCase = sql.Concat(padding, exprS); 1833 1834 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); 1835 } 1836 break; 1837 case "PadRight": 1838 if (mc.Arguments.Count == 1) { 1839 // s.PadRight(i) --> 1840 // CASE WHEN CLRLENGTH(@s) >= @i THEN @s 1841 // ELSE @s + SPACE(@i - CLRLENGTH(@s)) 1842 // END 1843 SqlExpression exprS = mc.Object; 1844 SqlExpression exprI = mc.Arguments[0]; 1845 SqlExpression dontChange = sql.Binary(SqlNodeType.GE, sql.CLRLENGTH(exprS), exprI); 1846 SqlExpression len2 = sql.CLRLENGTH(exprS); 1847 SqlExpression numSpaces = sql.Subtract(exprI, len2); 1848 SqlExpression padding = sql.FunctionCall(typeof(string), "SPACE", new SqlExpression[] { numSpaces }, source); 1849 SqlExpression elseCase = sql.Concat(exprS, padding); 1850 1851 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); 1852 } 1853 else if (mc.Arguments.Count == 2) { 1854 // s.PadRight(i,c) --> 1855 // CASE WHEN CLRLENGTH(@s) >= @i THEN @s 1856 // ELSE @s + REPLICATE(@c, @i - CLRLENGTH(@s)) 1857 // END 1858 SqlExpression exprS = mc.Object; 1859 SqlExpression exprI = mc.Arguments[0]; 1860 SqlExpression exprC = mc.Arguments[1]; 1861 SqlExpression dontChange = sql.Binary(SqlNodeType.GE, sql.CLRLENGTH(exprS), exprI); 1862 SqlExpression len2 = sql.CLRLENGTH(exprS); 1863 SqlExpression numSpaces = sql.Subtract(exprI, len2); 1864 SqlExpression padding = sql.FunctionCall(typeof(string), "REPLICATE", new SqlExpression[] { exprC, numSpaces }, source); 1865 SqlExpression elseCase = sql.Concat(exprS, padding); 1866 1867 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); 1868 } 1869 break; 1870 1871 case "Remove": 1872 if (mc.Arguments.Count == 1) { 1873 return sql.FunctionCall( 1874 typeof(string), "STUFF", 1875 new SqlExpression[] { 1876 mc.Object, 1877 sql.Add(mc.Arguments[0], 1), 1878 sql.CLRLENGTH(mc.Object), 1879 sql.ValueFromObject("", false, source) 1880 }, 1881 source); 1882 } 1883 else if (mc.Arguments.Count == 2) { 1884 return sql.FunctionCall( 1885 typeof(string), "STUFF", 1886 new SqlExpression[] { 1887 mc.Object, 1888 sql.Add(mc.Arguments[0], 1), 1889 mc.Arguments[1], 1890 sql.ValueFromObject("", false, source) 1891 }, 1892 source); 1893 } 1894 break; 1895 case "Replace": 1896 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { 1897 throw Error.ArgumentNull("old"); 1898 } 1899 if (mc.Arguments[1] is SqlValue && ((SqlValue)mc.Arguments[1]).Value == null) { 1900 throw Error.ArgumentNull("new"); 1901 } 1902 return sql.FunctionCall( 1903 typeof(string), "REPLACE", 1904 new SqlExpression[] { 1905 mc.Object, 1906 mc.Arguments[0], 1907 mc.Arguments[1] 1908 }, 1909 source); 1910 case "Substring": 1911 if (mc.Arguments.Count == 1) { 1912 return sql.FunctionCall( 1913 typeof(string), "SUBSTRING", 1914 new SqlExpression[] { 1915 mc.Object, 1916 sql.Add(mc.Arguments[0], 1), 1917 sql.CLRLENGTH(mc.Object) 1918 }, 1919 source); 1920 } 1921 else if (mc.Arguments.Count == 2) { 1922 return sql.FunctionCall( 1923 typeof(string), "SUBSTRING", 1924 new SqlExpression[] { 1925 mc.Object, 1926 sql.Add(mc.Arguments[0], 1), 1927 mc.Arguments[1] 1928 }, 1929 source); 1930 } 1931 break; 1932 case "Trim": 1933 if (mc.Arguments.Count == 0) { 1934 return sql.FunctionCall( 1935 typeof(string), "LTRIM", 1936 new SqlExpression[] { 1937 sql.FunctionCall(typeof(string), "RTRIM", new SqlExpression[] { mc.Object }, source) 1938 }, 1939 source); 1940 } 1941 break; 1942 case "ToLower": 1943 if (mc.Arguments.Count == 0) { 1944 return sql.FunctionCall(typeof(string), "LOWER", new SqlExpression[] { mc.Object }, source); 1945 } 1946 break; 1947 case "ToUpper": 1948 if (mc.Arguments.Count == 0) { 1949 return sql.FunctionCall(typeof(string), "UPPER", new SqlExpression[] { mc.Object }, source); 1950 } 1951 break; 1952 case "get_Chars": 1953 // s[i] --> SUBSTRING(@s, @i+1, 1) 1954 if (mc.Arguments.Count == 1) { 1955 return sql.FunctionCall(typeof(char), "SUBSTRING", new SqlExpression[] 1956 {mc.Object, 1957 sql.Add( mc.Arguments[0], 1), 1958 sql.ValueFromObject(1, false, source) 1959 }, source); 1960 } 1961 break; 1962 case "CompareTo": 1963 if (mc.Arguments.Count == 1) { 1964 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { 1965 throw Error.ArgumentNull("value"); 1966 } 1967 return CreateComparison(mc.Object, mc.Arguments[0], source); 1968 } 1969 break; 1970 } 1971 throw GetMethodSupportException(mc); 1972 } 1973 TranslateMathMethod(SqlMethodCall mc)1974 private SqlExpression TranslateMathMethod(SqlMethodCall mc) { 1975 Expression source = mc.SourceExpression; 1976 switch (mc.Method.Name) { 1977 case "Abs": 1978 if (mc.Arguments.Count == 1) { 1979 return sql.FunctionCall(mc.Arguments[0].ClrType, "ABS", new SqlExpression[] { mc.Arguments[0] }, source); 1980 } 1981 break; 1982 case "Acos": 1983 if (mc.Arguments.Count == 1) { 1984 return this.CreateFunctionCallStatic1(typeof(double), "ACOS", mc.Arguments, source); 1985 } 1986 break; 1987 case "Asin": 1988 if (mc.Arguments.Count == 1) { 1989 return this.CreateFunctionCallStatic1(typeof(double), "ASIN", mc.Arguments, source); 1990 } 1991 break; 1992 case "Atan": 1993 if (mc.Arguments.Count == 1) { 1994 return this.CreateFunctionCallStatic1(typeof(double), "ATAN", mc.Arguments, source); 1995 } 1996 break; 1997 case "Atan2": 1998 if (mc.Arguments.Count == 2) { 1999 return this.CreateFunctionCallStatic2(typeof(double), "ATN2", mc.Arguments, source); 2000 } 2001 break; 2002 case "BigMul": 2003 if (mc.Arguments.Count == 2) { 2004 return sql.Multiply(sql.ConvertToBigint(mc.Arguments[0]), sql.ConvertToBigint(mc.Arguments[1])); 2005 } 2006 break; 2007 case "Ceiling": 2008 if (mc.Arguments.Count == 1) { 2009 return this.CreateFunctionCallStatic1(mc.Arguments[0].ClrType, "CEILING", mc.Arguments, source); 2010 } 2011 break; 2012 case "Cos": 2013 if (mc.Arguments.Count == 1) { 2014 return this.CreateFunctionCallStatic1(typeof(double), "COS", mc.Arguments, source); 2015 } 2016 break; 2017 case "Cosh": 2018 if (mc.Arguments.Count == 1) { 2019 SqlExpression x = mc.Arguments[0]; 2020 SqlExpression expX = sql.FunctionCall(typeof(double), "EXP", new SqlExpression[] { x }, source); 2021 SqlExpression minusX = sql.Unary(SqlNodeType.Negate, x, source); 2022 SqlExpression expMinusX = sql.FunctionCall(typeof(double), "EXP", new SqlExpression[] { minusX }, source); 2023 return sql.Divide(sql.Add(expX, expMinusX), 2); 2024 } 2025 break; 2026 // DivRem has out parameter 2027 case "Exp": 2028 if (mc.Arguments.Count == 1) { 2029 return this.CreateFunctionCallStatic1(typeof(double), "EXP", mc.Arguments, source); 2030 } 2031 break; 2032 case "Floor": 2033 if (mc.Arguments.Count == 1) { 2034 return this.CreateFunctionCallStatic1(mc.Arguments[0].ClrType, "FLOOR", mc.Arguments, source); 2035 } 2036 break; 2037 // Math.IEEERemainder - difficult to implement correctly since SQL rounds differently 2038 case "Log": 2039 if (mc.Arguments.Count == 1) { 2040 return this.CreateFunctionCallStatic1(typeof(double), "LOG", mc.Arguments, source); 2041 } 2042 else if (mc.Arguments.Count == 2) { 2043 // Math.Log(x,y) --> LOG(@x) / LOG(@y) 2044 SqlExpression log1 = sql.FunctionCall(typeof(double), "LOG", new SqlExpression[] { mc.Arguments[0] }, source); 2045 SqlExpression log2 = sql.FunctionCall(typeof(double), "LOG", new SqlExpression[] { mc.Arguments[1] }, source); 2046 return sql.Divide(log1, log2); 2047 } 2048 break; 2049 case "Log10": 2050 if (mc.Arguments.Count == 1) { 2051 return this.CreateFunctionCallStatic1(typeof(double), "LOG10", mc.Arguments, source); 2052 } 2053 break; 2054 case "Max": 2055 if (mc.Arguments.Count == 2) { 2056 // Max(a,b) --> CASE WHEN @a<@b THEN @b ELSE @a 2057 SqlExpression a = mc.Arguments[0]; 2058 SqlExpression b = mc.Arguments[1]; 2059 SqlExpression aLower = sql.Binary(SqlNodeType.LT, a, b); 2060 return new SqlSearchedCase(mc.Method.ReturnType, new SqlWhen[] { new SqlWhen(aLower, b) }, a, source); 2061 } 2062 break; 2063 case "Min": 2064 if (mc.Arguments.Count == 2) { 2065 // Min(a,b) --> CASE WHEN @a<@b THEN @a ELSE @b 2066 SqlExpression a = mc.Arguments[0]; 2067 SqlExpression b = mc.Arguments[1]; 2068 SqlExpression aLower = sql.Binary(SqlNodeType.LT, a, b); 2069 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(aLower, a) }, b, source); 2070 } 2071 break; 2072 case "Pow": 2073 if (mc.Arguments.Count == 2) { 2074 return this.CreateFunctionCallStatic2(mc.ClrType, "POWER", mc.Arguments, source); 2075 } 2076 break; 2077 case "Round": 2078 int nParams = mc.Arguments.Count; 2079 if ((mc.Arguments[nParams - 1].ClrType != typeof(MidpointRounding))) { 2080 throw Error.MathRoundNotSupported(); 2081 } 2082 else { 2083 SqlExpression x = mc.Arguments[0]; 2084 SqlExpression i = null; 2085 if (nParams == 2) { 2086 i = sql.ValueFromObject(0, false, source); 2087 } 2088 else { 2089 i = mc.Arguments[1]; 2090 } 2091 SqlExpression roundingMethod = mc.Arguments[nParams - 1]; 2092 if (roundingMethod.NodeType != SqlNodeType.Value) { 2093 throw Error.NonConstantExpressionsNotSupportedForRounding(); 2094 } 2095 if ((MidpointRounding)this.Eval(roundingMethod) == MidpointRounding.AwayFromZero) { 2096 // round(x) --> round(@x,0) 2097 return sql.FunctionCall(x.ClrType, "round", new SqlExpression[] { x, i }, source); 2098 } 2099 else { 2100 // CASE WHEN 2*@x = ROUND(2*@x, @i) AND @x <> ROUND(@x, @i) 2101 // THEN 2 * ROUND(@x/2, @i) 2102 // ELSE ROUND(@x, @i) 2103 // END 2104 Type type = x.ClrType; 2105 SqlExpression roundX = sql.FunctionCall(type, "round", new SqlExpression[] { x, i }, source); 2106 SqlExpression twiceX = sql.Multiply(x, 2); 2107 SqlExpression round2X = sql.FunctionCall(type, "round", new SqlExpression[] { twiceX, i }, source); 2108 SqlExpression condition = sql.AndAccumulate(sql.Binary(SqlNodeType.EQ, twiceX, round2X), sql.Binary(SqlNodeType.NE, x, roundX)); 2109 SqlExpression specialCase = sql.Multiply(sql.FunctionCall(type, "round", new SqlExpression[] { sql.Divide(x, 2), i }, source), 2); 2110 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(condition, specialCase) }, roundX, source); 2111 } 2112 } 2113 case "Sign": 2114 if (mc.Arguments.Count == 1) { 2115 return sql.FunctionCall(typeof(int), "SIGN", new SqlExpression[] { mc.Arguments[0] }, source); 2116 } 2117 break; 2118 case "Sin": 2119 if (mc.Arguments.Count == 1) { 2120 return this.CreateFunctionCallStatic1(typeof(double), "SIN", mc.Arguments, source); 2121 } 2122 break; 2123 case "Sinh": 2124 if (mc.Arguments.Count == 1) { 2125 SqlExpression x = mc.Arguments[0]; 2126 SqlExpression exp = sql.FunctionCall(typeof(double), "EXP", new SqlExpression[] { x }, source); 2127 SqlExpression minusX = sql.Unary(SqlNodeType.Negate, x, source); 2128 SqlExpression expMinus = sql.FunctionCall(typeof(double), "EXP", new SqlExpression[] { minusX }, source); 2129 return sql.Divide(sql.Subtract(exp, expMinus), 2); 2130 } 2131 break; 2132 case "Sqrt": 2133 if (mc.Arguments.Count == 1) { 2134 return this.CreateFunctionCallStatic1(typeof(double), "SQRT", mc.Arguments, source); 2135 } 2136 break; 2137 case "Tan": 2138 if (mc.Arguments.Count == 1) { 2139 return this.CreateFunctionCallStatic1(typeof(double), "TAN", mc.Arguments, source); 2140 } 2141 break; 2142 case "Tanh": 2143 if (mc.Arguments.Count == 1) { 2144 SqlExpression x = mc.Arguments[0]; 2145 SqlExpression expX = sql.FunctionCall(typeof(double), "EXP", new SqlExpression[] { x }, source); 2146 SqlExpression minusX = sql.Unary(SqlNodeType.Negate, x, source); 2147 SqlExpression expMinusX = sql.FunctionCall(typeof(double), "EXP", new SqlExpression[] { minusX }, source); 2148 return sql.Divide(sql.Subtract(expX, expMinusX), sql.Add(expX, expMinusX)); 2149 } 2150 break; 2151 case "Truncate": 2152 if (mc.Arguments.Count == 1) { 2153 // Truncate(x) --> ROUND (x, 0, 1) 2154 SqlExpression x = mc.Arguments[0]; 2155 return sql.FunctionCall(mc.Method.ReturnType, "ROUND", new SqlExpression[] { x, sql.ValueFromObject(0, false, source), sql.ValueFromObject(1, false, source) }, source); 2156 } 2157 break; 2158 } 2159 throw GetMethodSupportException(mc); 2160 } 2161 2162 [SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity", Justification = "Microsoft: These issues are related to our use of if-then and case statements for node types, which adds to the complexity count however when reviewed they are easy to navigate and understand.")] VisitMember(SqlMember m)2163 internal override SqlNode VisitMember(SqlMember m) { 2164 SqlExpression exp = this.VisitExpression(m.Expression); 2165 MemberInfo member = m.Member; 2166 Expression source = m.SourceExpression; 2167 2168 Type baseClrTypeOfExpr = TypeSystem.GetNonNullableType(exp.ClrType); 2169 if (baseClrTypeOfExpr == typeof(string) && member.Name == "Length") { 2170 // This gives a different result than .Net would if the string ends in spaces. 2171 // We decided not to fix this up (e.g. LEN(@s+'#') - 1) since it would incur a performance hit and 2172 // people may actually expect that it translates to the SQL LEN function. 2173 return sql.LEN(exp); 2174 } 2175 else if (baseClrTypeOfExpr == typeof(Binary) && member.Name == "Length") { 2176 return sql.DATALENGTH(exp); 2177 } 2178 else if (baseClrTypeOfExpr == typeof(DateTime) || baseClrTypeOfExpr == typeof(DateTimeOffset)) { 2179 string datePart = GetDatePart(member.Name); 2180 if (datePart != null) { 2181 return sql.DATEPART(datePart, exp); 2182 } 2183 else if (member.Name == "Date") { 2184 if (this.providerMode == SqlProvider.ProviderMode.Sql2008) { 2185 SqlExpression date = new SqlVariable(typeof(void), null, "DATE", source); 2186 return sql.FunctionCall(typeof(DateTime), "CONVERT", new SqlExpression[2] { date, exp }, source); 2187 } 2188 // date --> dateadd(hh, -(datepart(hh, @date)), 2189 // dateadd(mi, -(datepart(mi, @date)), 2190 // dateadd(ss, -(datepart(ss, @date)), 2191 // dateadd(ms, -(datepart(ms, @date)), 2192 // @date)))) 2193 2194 SqlExpression ms = sql.DATEPART("MILLISECOND", exp); 2195 SqlExpression ss = sql.DATEPART("SECOND", exp); 2196 SqlExpression mi = sql.DATEPART("MINUTE", exp); 2197 SqlExpression hh = sql.DATEPART("HOUR", exp); 2198 2199 SqlExpression result = exp; 2200 2201 result = sql.DATEADD("MILLISECOND", sql.Unary(SqlNodeType.Negate, ms), result); 2202 result = sql.DATEADD("SECOND", sql.Unary(SqlNodeType.Negate, ss), result); 2203 result = sql.DATEADD("MINUTE", sql.Unary(SqlNodeType.Negate, mi), result); 2204 result = sql.DATEADD("HOUR", sql.Unary(SqlNodeType.Negate, hh), result); 2205 2206 return result; 2207 } 2208 else if (member.Name == "DateTime") { 2209 Debug.Assert(baseClrTypeOfExpr == typeof(DateTimeOffset), "'DateTime' property supported only for instances of DateTimeOffset."); 2210 SqlExpression datetime = new SqlVariable(typeof(void), null, "DATETIME", source); 2211 return sql.FunctionCall(typeof(DateTime), "CONVERT", new SqlExpression[2] { datetime, exp }, source); 2212 } 2213 else if (member.Name == "TimeOfDay") { 2214 SqlExpression hours = sql.DATEPART("HOUR", exp); 2215 SqlExpression minutes = sql.DATEPART("MINUTE", exp); 2216 SqlExpression seconds = sql.DATEPART("SECOND", exp); 2217 SqlExpression milliseconds = sql.DATEPART("MILLISECOND", exp); 2218 2219 SqlExpression ticksFromHour = sql.Multiply(sql.ConvertToBigint(hours), TimeSpan.TicksPerHour); 2220 SqlExpression ticksFromMinutes = sql.Multiply(sql.ConvertToBigint(minutes), TimeSpan.TicksPerMinute); 2221 SqlExpression ticksFromSeconds = sql.Multiply(sql.ConvertToBigint(seconds), TimeSpan.TicksPerSecond); 2222 SqlExpression ticksFromMs = sql.Multiply(sql.ConvertToBigint(milliseconds), TimeSpan.TicksPerMillisecond); 2223 return sql.ConvertTo(typeof(TimeSpan), sql.Add(ticksFromHour, ticksFromMinutes, ticksFromSeconds, ticksFromMs)); 2224 } 2225 else if (member.Name == "DayOfWeek") { 2226 //(DATEPART(dw,@date) + @@Datefirst + 6) % 7 to make it independent from SQL settings 2227 SqlExpression sqlDay = sql.DATEPART("dw", exp); 2228 2229 // 2230 // .DayOfWeek returns a System.DayOfWeek, so ConvertTo that enum. 2231 return sql.ConvertTo(typeof(DayOfWeek), 2232 sql.Mod( 2233 sql.Add(sqlDay, 2234 sql.Add(new SqlVariable(typeof(int), sql.Default(typeof(int)), "@@DATEFIRST", source), 6) 2235 ) 2236 , 7)); 2237 } 2238 } 2239 else if (baseClrTypeOfExpr == typeof(System.TimeSpan)) { 2240 switch (member.Name) { 2241 case "Ticks": 2242 if (SqlFactory.IsSqlTimeType(exp)) { 2243 return this.sql.Divide( 2244 sql.ConvertToBigint( 2245 sql.Add( 2246 this.sql.Multiply(sql.ConvertToBigint(sql.DATEPART("HOUR", exp)), 3600000000000), 2247 this.sql.Multiply(sql.ConvertToBigint(sql.DATEPART("MINUTE", exp)), 60000000000), 2248 this.sql.Multiply(sql.ConvertToBigint(sql.DATEPART("SECOND", exp)), 1000000000), 2249 sql.DATEPART("NANOSECOND", exp)) 2250 ), 2251 100 2252 ); 2253 } 2254 return sql.ConvertToBigint(exp); 2255 case "TotalMilliseconds": 2256 if (SqlFactory.IsSqlTimeType(exp)) { 2257 return this.sql.Add( 2258 this.sql.Multiply(sql.DATEPART("HOUR", exp), 3600000), 2259 this.sql.Multiply(sql.DATEPART("MINUTE", exp), 60000), 2260 this.sql.Multiply(sql.DATEPART("SECOND", exp), 1000), 2261 this.sql.Divide(sql.ConvertToDouble(sql.ConvertToBigint(sql.DATEPART("NANOSECOND", exp))), 1000000) 2262 ); 2263 } 2264 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerMillisecond); 2265 case "TotalSeconds": 2266 if (SqlFactory.IsSqlTimeType(exp)) { 2267 return this.sql.Add( 2268 this.sql.Multiply(sql.DATEPART("HOUR", exp), 3600), 2269 this.sql.Multiply(sql.DATEPART("MINUTE", exp), 60), 2270 this.sql.DATEPART("SECOND", exp), 2271 this.sql.Divide(sql.ConvertToDouble(sql.ConvertToBigint(sql.DATEPART("NANOSECOND", exp))), 1000000000) 2272 ); 2273 } 2274 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerSecond); 2275 case "TotalMinutes": 2276 if (SqlFactory.IsSqlTimeType(exp)) { 2277 return this.sql.Add( 2278 this.sql.Multiply(sql.DATEPART("HOUR", exp), 60), 2279 this.sql.DATEPART("MINUTE", exp), 2280 this.sql.Divide(sql.ConvertToDouble(sql.DATEPART("SECOND", exp)), 60), 2281 this.sql.Divide(sql.ConvertToDouble(sql.ConvertToBigint(sql.DATEPART("NANOSECOND", exp))), 60000000000) 2282 ); 2283 } 2284 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerMinute); 2285 case "TotalHours": 2286 if (SqlFactory.IsSqlTimeType(exp)) { 2287 return this.sql.Add( 2288 this.sql.DATEPART("HOUR", exp), 2289 this.sql.Divide(sql.ConvertToDouble(sql.DATEPART("MINUTE", exp)), 60), 2290 this.sql.Divide(sql.ConvertToDouble(sql.DATEPART("SECOND", exp)), 3600), 2291 this.sql.Divide(sql.ConvertToDouble(sql.ConvertToBigint(sql.DATEPART("NANOSECOND", exp))), 3600000000000) 2292 ); 2293 } 2294 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerHour); 2295 case "TotalDays": 2296 if (SqlFactory.IsSqlTimeType(exp)) { 2297 return this.sql.Divide( 2298 this.sql.Add( 2299 this.sql.DATEPART("HOUR", exp), 2300 this.sql.Divide(sql.ConvertToDouble(sql.DATEPART("MINUTE", exp)), 60), 2301 this.sql.Divide(sql.ConvertToDouble(sql.DATEPART("SECOND", exp)), 3600), 2302 this.sql.Divide(sql.ConvertToDouble(sql.ConvertToBigint(sql.DATEPART("NANOSECOND", exp))), 3600000000000)), 2303 24 2304 ); 2305 } 2306 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerDay); 2307 case "Milliseconds": 2308 if (SqlFactory.IsSqlTimeType(exp)) { 2309 return this.sql.DATEPART("MILLISECOND", exp); 2310 } 2311 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerMillisecond)), 1000)); 2312 case "Seconds": 2313 if (SqlFactory.IsSqlTimeType(exp)) { 2314 return this.sql.DATEPART("SECOND", exp); 2315 } 2316 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerSecond)), 60)); 2317 case "Minutes": 2318 if (SqlFactory.IsSqlTimeType(exp)) { 2319 return this.sql.DATEPART("MINUTE", exp); 2320 } 2321 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerMinute)), 60)); 2322 case "Hours": 2323 if (SqlFactory.IsSqlTimeType(exp)) { 2324 return this.sql.DATEPART("HOUR", exp); 2325 } 2326 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerHour)), 24)); 2327 case "Days": 2328 if (SqlFactory.IsSqlTimeType(exp)) { 2329 return this.sql.ValueFromObject(0, false, exp.SourceExpression); 2330 } 2331 return sql.ConvertToInt(sql.Divide(exp, TimeSpan.TicksPerDay)); 2332 default: 2333 throw Error.MemberCannotBeTranslated(member.DeclaringType, member.Name); 2334 } 2335 } 2336 throw Error.MemberCannotBeTranslated(member.DeclaringType, member.Name); 2337 } 2338 2339 // date + timespan --> DATEADD( day, @timespan/864000000000, DATEADD(ms,(@timespan/1000) % 86400000 , @date)) CreateDateTimeFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source)2340 private SqlExpression CreateDateTimeFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source) { 2341 return CreateDateTimeFromDateAndTicks(sqlDate, sqlTicks, source, false); 2342 } 2343 CreateDateTimeFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source, bool asNullable)2344 private SqlExpression CreateDateTimeFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source, bool asNullable) { 2345 SqlExpression daysAdded = sql.DATEADD("day", sql.Divide(sqlTicks, TimeSpan.TicksPerDay), sqlDate, source, asNullable); 2346 return sql.DATEADD("ms", sql.Mod(sql.Divide(sqlTicks, TimeSpan.TicksPerMillisecond), 86400000), daysAdded, source, asNullable); 2347 } 2348 2349 // date + ms --> DATEADD( day, @ms/86400000, DATEADD(ms, @ms % 86400000 , @date)) CreateDateTimeFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source)2350 private SqlExpression CreateDateTimeFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source) { 2351 return CreateDateTimeFromDateAndMs(sqlDate, ms, source, false); 2352 } 2353 CreateDateTimeFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source, bool asNullable)2354 private SqlExpression CreateDateTimeFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source, bool asNullable) { 2355 SqlExpression msBigint = sql.ConvertToBigint(ms); 2356 SqlExpression daysAdded = sql.DATEADD("day", sql.Divide(msBigint, 86400000), sqlDate, source, asNullable); 2357 return sql.DATEADD("ms", sql.Mod(msBigint, 86400000), daysAdded, source, asNullable); 2358 } 2359 2360 // date + timespan --> DATEADD( day, @timespan/864000000000, DATEADD(ms,(@timespan/1000) % 86400000 , @date)) CreateDateTimeOffsetFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source)2361 private SqlExpression CreateDateTimeOffsetFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source) { 2362 return CreateDateTimeOffsetFromDateAndTicks(sqlDate, sqlTicks, source, false); 2363 } 2364 CreateDateTimeOffsetFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source, bool asNullable)2365 private SqlExpression CreateDateTimeOffsetFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source, bool asNullable) { 2366 SqlExpression daysAdded = sql.DATETIMEOFFSETADD("day", sql.Divide(sqlTicks, TimeSpan.TicksPerDay), sqlDate, source, asNullable); 2367 return sql.DATETIMEOFFSETADD("ms", sql.Mod(sql.Divide(sqlTicks, TimeSpan.TicksPerMillisecond), 86400000), daysAdded, source, asNullable); 2368 } 2369 2370 // date + ms --> DATEADD( day, @ms/86400000, DATEADD(ms, @ms % 86400000 , @date)) CreateDateTimeOffsetFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source)2371 private SqlExpression CreateDateTimeOffsetFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source) { 2372 return CreateDateTimeOffsetFromDateAndMs(sqlDate, ms, source, false); 2373 } 2374 CreateDateTimeOffsetFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source, bool asNullable)2375 private SqlExpression CreateDateTimeOffsetFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source, bool asNullable) { 2376 SqlExpression msBigint = sql.ConvertToBigint(ms); 2377 SqlExpression daysAdded = sql.DATETIMEOFFSETADD("day", sql.Divide(msBigint, 86400000), sqlDate, source, asNullable); 2378 return sql.DATETIMEOFFSETADD("ms", sql.Mod(msBigint, 86400000), daysAdded, source, asNullable); 2379 } 2380 TranslateVbConversionMethod(SqlMethodCall mc)2381 private SqlExpression TranslateVbConversionMethod(SqlMethodCall mc) { 2382 Expression source = mc.SourceExpression; 2383 if (mc.Arguments.Count == 1) { 2384 SqlExpression expr = mc.Arguments[0]; 2385 Type targetType = null; 2386 switch (mc.Method.Name) { 2387 case "ToBoolean": 2388 targetType = typeof(bool); 2389 break; 2390 case "ToSByte": 2391 targetType = typeof(sbyte); 2392 break; 2393 case "ToByte": 2394 targetType = typeof(byte); 2395 break; 2396 case "ToChar": 2397 targetType = typeof(char); 2398 break; 2399 case "ToCharArrayRankOne": 2400 targetType = typeof(char[]); 2401 break; 2402 case "ToDate": 2403 targetType = typeof(DateTime); 2404 break; 2405 case "ToDecimal": 2406 targetType = typeof(decimal); 2407 break; 2408 case "ToDouble": 2409 targetType = typeof(double); 2410 break; 2411 case "ToInteger": 2412 targetType = typeof(Int32); 2413 break; 2414 case "ToUInteger": 2415 targetType = typeof(UInt32); 2416 break; 2417 case "ToLong": 2418 targetType = typeof(Int64); 2419 break; 2420 case "ToULong": 2421 targetType = typeof(UInt64); 2422 break; 2423 case "ToShort": 2424 targetType = typeof(Int16); 2425 break; 2426 case "ToUShort": 2427 targetType = typeof(UInt16); 2428 break; 2429 case "ToSingle": 2430 targetType = typeof(float); 2431 break; 2432 case "ToString": 2433 targetType = typeof(string); 2434 break; 2435 } 2436 if (targetType != null) { 2437 if ((targetType == typeof(int) || targetType == typeof(Single)) && expr.ClrType == typeof(bool)) { 2438 List<SqlExpression> matchesList = new List<SqlExpression>(); 2439 List<SqlExpression> valuesList = new List<SqlExpression>(); 2440 2441 matchesList.Add(sql.ValueFromObject(true, false, source)); 2442 valuesList.Add(sql.ValueFromObject(-1, false, source)); 2443 matchesList.Add(sql.ValueFromObject(false, false, source)); 2444 valuesList.Add(sql.ValueFromObject(0, false, source)); 2445 2446 return sql.Case(targetType, expr, matchesList, valuesList, source); 2447 } 2448 else if (mc.ClrType != mc.Arguments[0].ClrType) { 2449 // do the conversions that would be done for a cast "(<targetType>) expression" 2450 return sql.ConvertTo(targetType, expr); 2451 } 2452 else { 2453 return mc.Arguments[0]; 2454 } 2455 } 2456 } 2457 throw GetMethodSupportException(mc); 2458 } 2459 TranslateVbCompareString(SqlMethodCall mc)2460 private SqlExpression TranslateVbCompareString(SqlMethodCall mc) { 2461 if (mc.Arguments.Count >= 2) { 2462 return CreateComparison(mc.Arguments[0], mc.Arguments[1], mc.SourceExpression); 2463 } 2464 throw GetMethodSupportException(mc); 2465 } 2466 TranslateVbLikeString(SqlMethodCall mc)2467 private SqlExpression TranslateVbLikeString(SqlMethodCall mc) { 2468 // these should be true per the method signature 2469 Debug.Assert(mc.Arguments.Count == 3); 2470 Debug.Assert(mc.Arguments[0].ClrType == typeof(string)); 2471 Debug.Assert(mc.Arguments[1].ClrType == typeof(string)); 2472 bool needsEscape = true; 2473 2474 Expression source = mc.SourceExpression; 2475 SqlExpression pattern = mc.Arguments[1]; 2476 if (pattern.NodeType == SqlNodeType.Value) { 2477 string unescapedText = (string)((SqlValue)pattern).Value; 2478 string patternText = SqlHelpers.TranslateVBLikePattern(unescapedText, '~'); 2479 pattern = sql.ValueFromObject(patternText, typeof(string), true, source); 2480 needsEscape = unescapedText != patternText; 2481 } 2482 else if (pattern.NodeType == SqlNodeType.ClientParameter) { 2483 SqlClientParameter cp = (SqlClientParameter)pattern; 2484 pattern = new SqlClientParameter( 2485 cp.ClrType, cp.SqlType, 2486 Expression.Lambda( 2487 Expression.Call(typeof(SqlHelpers), "TranslateVBLikePattern", Type.EmptyTypes, cp.Accessor.Body, Expression.Constant('~')), 2488 cp.Accessor.Parameters[0] 2489 ), 2490 cp.SourceExpression 2491 ); 2492 } 2493 else { 2494 throw Error.NonConstantExpressionsNotSupportedFor("LIKE"); 2495 } 2496 SqlExpression escape = needsEscape ? sql.ValueFromObject("~", false, mc.SourceExpression) : null; 2497 return sql.Like(mc.Arguments[0], pattern, escape, source); 2498 } 2499 } 2500 } 2501 } 2502