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