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;
9 using System.Diagnostics;
10 using System.Diagnostics.CodeAnalysis;
12 namespace System.Data.Linq.SqlClient {
14 internal static class PostBindDotNetConverter {
16 internal enum MethodSupport
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)
23 internal static SqlNode Convert(SqlNode node, SqlFactory sql, SqlProvider.ProviderMode providerMode) {
24 return new Visitor(sql, providerMode).Visit(node);
27 internal static bool CanConvert(SqlNode node) {
28 SqlUnary su = node as SqlUnary;
29 if (su != null && IsSupportedUnary(su)) {
32 SqlNew sn = node as SqlNew;
33 if (sn != null && IsSupportedNew(sn)) {
36 SqlMember sm = node as SqlMember;
37 if (sm != null && IsSupportedMember(sm)) {
40 SqlMethodCall mc = node as SqlMethodCall;
41 if (mc != null && (GetMethodSupport(mc) == MethodSupport.Method)) {
47 private static bool IsSupportedUnary(SqlUnary uo) {
48 return uo.NodeType == SqlNodeType.Convert &&
49 uo.ClrType == typeof(char) || uo.Operand.ClrType == typeof(char);
52 private static bool IsSupportedNew(SqlNew snew) {
53 if (snew.ClrType == typeof(string)) {
54 return IsSupportedStringNew(snew);
56 else if (snew.ClrType == typeof(TimeSpan)) {
57 return IsSupportedTimeSpanNew(snew);
59 else if (snew.ClrType == typeof(DateTime)) {
60 return IsSupportedDateTimeNew(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);
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) {
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) {
83 if ((sox.Args.Count == 7) && (sox.Args[6].ClrType == typeof(int))) {
91 private static bool IsSupportedTimeSpanNew(SqlNew sox) {
92 if (sox.Args.Count == 1) {
95 else if (sox.Args.Count == 3) {
99 if (sox.Args.Count == 4) {
102 else if (sox.Args.Count == 5) {
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);
116 ms = GetDateTimeMethodSupport(mc);
120 ms = GetDateTimeOffsetMethodSupport(mc);
124 ms = GetTimeSpanMethodSupport(mc);
128 ms = GetConvertMethodSupport(mc);
132 ms = GetDecimalMethodSupport(mc);
136 ms = GetMathMethodSupport(mc);
140 ms = GetStringMethodSupport(mc);
144 ms = GetComparisonMethodSupport(mc);
148 ms = GetNullableMethodSupport(mc);
152 ms = GetCoercionMethodSupport(mc);
156 ms = GetObjectMethodSupport(mc);
160 ms = GetVbHelperMethodSupport(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;
174 return MethodSupport.None;
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;
181 return MethodSupport.None;
184 private static MethodSupport GetObjectMethodSupport(SqlMethodCall mc) {
185 if (!mc.Method.IsStatic) {
186 switch (mc.Method.Name) {
188 return MethodSupport.Method;
190 if (mc.Object.SqlType.CanBeColumn) {
191 return MethodSupport.Method;
193 return MethodSupport.None;
195 if (mc.Arguments.Count == 0) {
196 return MethodSupport.Method;
198 return MethodSupport.None;
201 return MethodSupport.None;
204 private static MethodSupport GetNullableMethodSupport(SqlMethodCall mc) {
205 if (mc.Method.Name == "GetValueOrDefault" && TypeSystem.IsNullableType(mc.Object.ClrType)) {
206 return MethodSupport.Method;
208 return MethodSupport.None;
211 private static readonly string[] dateParts = { "Year", "Month", "Day", "Hour", "Minute", "Second",
212 "Millisecond", "Microsecond", "Nanosecond" };
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;
222 return MethodSupport.MethodGroup;
227 else if (mc.Method.Name == "Like") {
228 if (mc.Arguments.Count == 2) {
229 return MethodSupport.Method;
231 else if (mc.Arguments.Count == 3) {
232 return MethodSupport.Method;
234 return MethodSupport.MethodGroup;
236 else if (mc.Method.Name == "RawLength") {
237 return MethodSupport.Method;
240 return MethodSupport.None;
243 private static MethodSupport GetDateTimeMethodSupport(SqlMethodCall mc) {
244 if (!mc.Method.IsStatic && mc.Method.DeclaringType == typeof(DateTime)) {
245 switch (mc.Method.Name) {
250 case "AddMilliseconds":
255 return MethodSupport.Method;
257 if (mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan)) {
258 return MethodSupport.Method;
260 return MethodSupport.MethodGroup;
264 return MethodSupport.None;
267 private static MethodSupport GetDateTimeOffsetMethodSupport(SqlMethodCall mc) {
268 if (!mc.Method.IsStatic && mc.Method.DeclaringType == typeof(DateTimeOffset)) {
269 switch (mc.Method.Name) {
274 case "AddMilliseconds":
279 return MethodSupport.Method;
281 if (mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan)) {
282 return MethodSupport.Method;
285 return MethodSupport.MethodGroup;
289 return MethodSupport.None;
292 private static MethodSupport GetTimeSpanMethodSupport(SqlMethodCall mc) {
293 if (!mc.Method.IsStatic && mc.Method.DeclaringType == typeof(TimeSpan)) {
294 switch (mc.Method.Name) {
300 return MethodSupport.Method;
303 return MethodSupport.None;
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) {
319 return MethodSupport.Method;
321 if (mc.Arguments[0].ClrType == typeof(string) || mc.Arguments[0].ClrType == typeof(DateTime)) {
322 return MethodSupport.Method;
324 return MethodSupport.MethodGroup;
328 return MethodSupport.None;
331 private static MethodSupport GetDecimalMethodSupport(SqlMethodCall mc) {
332 if (mc.Method.IsStatic) {
333 if (mc.Arguments.Count == 2) {
334 switch (mc.Method.Name) {
341 return MethodSupport.Method;
344 else if (mc.Arguments.Count == 1) {
345 switch (mc.Method.Name) {
350 return MethodSupport.Method;
352 if (mc.Method.Name.StartsWith("To", StringComparison.Ordinal)) {
353 return MethodSupport.Method;
359 return MethodSupport.None;
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;
370 switch (mc.Method.Name) {
374 if (mc.Arguments.Count == 1) {
375 return MethodSupport.Method;
377 return MethodSupport.MethodGroup;
380 if (mc.Arguments.Count == 1
381 || mc.Arguments.Count == 2
382 || mc.Arguments.Count == 3) {
383 return MethodSupport.Method;
385 return MethodSupport.MethodGroup;
387 if (mc.Arguments.Count == 2) {
388 return MethodSupport.Method;
390 return MethodSupport.MethodGroup;
395 if(mc.Arguments.Count == 1
396 || mc.Arguments.Count == 2) {
397 return MethodSupport.Method;
399 return MethodSupport.MethodGroup;
401 return MethodSupport.Method;
405 if (mc.Arguments.Count == 0) {
406 return MethodSupport.Method;
408 return MethodSupport.MethodGroup;
411 if (mc.Arguments.Count == 1) {
412 return MethodSupport.Method;
414 return MethodSupport.MethodGroup;
418 return MethodSupport.None;
421 private static MethodSupport GetMathMethodSupport(SqlMethodCall mc) {
422 if (mc.Method.IsStatic && mc.Method.DeclaringType == typeof(Math)) {
423 switch (mc.Method.Name) {
434 if(mc.Arguments.Count == 1) {
435 return MethodSupport.Method;
437 return MethodSupport.MethodGroup;
439 if (mc.Arguments.Count == 1 || mc.Arguments.Count == 2) {
440 return MethodSupport.Method;
442 return MethodSupport.MethodGroup;
448 if (mc.Arguments.Count == 2) {
449 return MethodSupport.Method;
451 return MethodSupport.MethodGroup;
453 if (mc.Arguments[mc.Arguments.Count - 1].ClrType == typeof(MidpointRounding)
454 && (mc.Arguments.Count == 2 || mc.Arguments.Count == 3)) {
455 return MethodSupport.Method;
457 return MethodSupport.MethodGroup;
465 if (mc.Arguments.Count == 1) {
466 return MethodSupport.Method;
468 return MethodSupport.MethodGroup;
471 return MethodSupport.None;
474 private static MethodSupport GetVbHelperMethodSupport(SqlMethodCall mc) {
475 if (IsVbConversionMethod(mc) ||
476 IsVbCompareString(mc) ||
478 return MethodSupport.Method;
480 return MethodSupport.None;
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";
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");
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) {
503 case "ToCharArrayRankOne":
521 private static bool IsSupportedMember(SqlMember m) {
522 return IsSupportedStringMember(m)
523 || IsSupportedBinaryMember(m)
524 || IsSupportedDateTimeMember(m)
525 || IsSupportedDateTimeOffsetMember(m)
526 || IsSupportedTimeSpanMember(m);
529 private static bool IsSupportedStringMember(SqlMember m) {
530 return m.Expression.ClrType == typeof(string) && m.Member.Name == "Length";
533 private static bool IsSupportedBinaryMember(SqlMember m) {
534 return m.Expression.ClrType == typeof(Binary) && m.Member.Name == "Length";
537 private static string GetDatePart(string memberName) {
538 switch (memberName) {
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) {
559 switch (m.Member.Name) {
570 // Identical to IsSupportedDateTimeMember(), except for support for 'DateTime'
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) {
578 switch (m.Member.Name) {
589 private static bool IsSupportedTimeSpanMember(SqlMember m) {
590 if (m.Expression.ClrType == typeof(TimeSpan)) {
591 switch (m.Member.Name) {
593 case "TotalMilliseconds":
610 /// Skips over client portion of selection expression
612 private class SqlSelectionSkipper : SqlVisitor {
614 internal SqlSelectionSkipper(SqlVisitor parent) {
615 this.parent = parent;
617 internal override SqlExpression VisitColumn(SqlColumn col) {
618 // pass control back to parent
619 return parent.VisitColumn(col);
621 internal override SqlExpression VisitSubSelect(SqlSubSelect ss) {
622 // pass control back to parent
623 return this.parent.VisitSubSelect(ss);
625 internal override SqlExpression VisitClientQuery(SqlClientQuery cq) {
626 // pass control back to parent
627 return this.parent.VisitClientQuery(cq);
631 private class Visitor : SqlVisitor {
633 SqlProvider.ProviderMode providerMode;
634 SqlSelectionSkipper skipper;
636 internal Visitor(SqlFactory sql, SqlProvider.ProviderMode providerMode) {
638 this.providerMode = providerMode;
639 this.skipper = new SqlSelectionSkipper(this);
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);
649 // transform type conversion if necessary
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);
657 return sql.ConvertTo(newType, uo.SqlType, expr);
660 return base.VisitUnaryOperator(uo);
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);
673 // currently only happens for generated test cases with optimization SimplifyCaseStatements off
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);
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];
695 return sql.Case(tc.ClrType, tc.Discriminator, matches, values, tc.SourceExpression);
699 // transform constructors if necessary
700 internal override SqlExpression VisitNew(SqlNew sox) {
701 sox = (SqlNew)base.VisitNew(sox);
702 if (sox.ClrType == typeof(string)) {
703 return TranslateNewString(sox);
705 else if (sox.ClrType == typeof(TimeSpan)) {
706 return TranslateNewTimeSpan(sox);
708 else if (sox.ClrType == typeof(DateTime)) {
709 return TranslateNewDateTime(sox);
711 else if (sox.ClrType == typeof(DateTimeOffset)) {
712 return TranslateNewDateTimeOffset(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);
724 throw Error.UnsupportedStringConstructorForm();
727 private SqlExpression TranslateNewDateTime(SqlNew sox) {
728 Expression source = sox.SourceExpression;
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);
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);
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);
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);
771 ms = sql.FunctionCall(typeof(string), "RIGHT", new SqlExpression[2] { msRaw, sql.ValueFromObject(3, false, source) }, source);
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);
778 throw Error.UnsupportedDateTimeConstructorForm();
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) },
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",
798 sql.ConvertToInt(sql.ConvertToBigint(sql.Divide(sql.ConvertTimeToDouble(sox.Args[1]), TimeSpan.TicksPerMinute)))
802 // DateTimeOffset(year, month, day, hour, minute, second, [millisecond,] timeSpan)
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)) {
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);
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);
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);
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);
826 SqlExpression datetimeoffset = new SqlVariable(typeof(void), null, "DATETIMEOFFSET", source);
827 SqlExpression result, dateAndTime;
830 if (sox.Args.Count == 7 && sox.Args[6].ClrType == typeof(TimeSpan)) {
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);
835 else if (sox.Args.Count == 8 && sox.Args[6].ClrType == typeof(int) && sox.Args[7].ClrType == typeof(TimeSpan)) {
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);
845 throw Error.UnsupportedDateTimeOffsetConstructorForm();
848 return sql.FunctionCall(typeof(DateTimeOffset), "TODATETIMEOFFSET",
852 sql.ConvertToInt(sql.ConvertToBigint(sql.Divide(sql.ConvertTimeToDouble(sox.Args[timeSpanIndex]), TimeSpan.TicksPerMinute)))
857 throw Error.UnsupportedDateTimeOffsetConstructorForm();
860 private SqlExpression TranslateNewTimeSpan(SqlNew sox) {
861 if (sox.Args.Count == 1) {
862 return sql.ConvertTo(typeof(TimeSpan), sox.Args[0]);
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));
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);
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));
895 throw Error.UnsupportedTimeSpanConstructorForm();
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]);
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]);
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);
915 else if (declType == typeof(System.Math)) {
916 returnValue = TranslateMathMethod(mc);
918 else if (declType == typeof(System.String)) {
919 returnValue = TranslateStringStaticMethod(mc);
921 else if (declType == typeof(System.Convert)) {
922 returnValue = TranslateConvertStaticMethod(mc);
924 else if (declType == typeof(SqlMethods)) {
925 returnValue = TranslateSqlMethodsMethod(mc);
927 else if (declType == typeof(decimal)) {
928 returnValue = TranslateDecimalMethod(mc);
930 else if (IsVbConversionMethod(mc)) {
931 return TranslateVbConversionMethod(mc);
933 else if (IsVbCompareString(mc)) {
934 return TranslateVbCompareString(mc);
936 else if (IsVbLike(mc)) {
937 return TranslateVbLikeString(mc);
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);
949 if (mc.Method.Name == "Equals" && mc.Arguments.Count == 1) {
950 return sql.Binary(SqlNodeType.EQ, mc.Object, mc.Arguments[0]);
952 else if (mc.Method.Name == "GetValueOrDefault" && mc.Method.DeclaringType.IsGenericType
953 && mc.Method.DeclaringType.GetGenericTypeDefinition() == typeof(Nullable<>)) {
954 return TranslateGetValueOrDefaultMethod(mc);
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);
961 throw Error.ToStringOnlySupportedForPrimitiveTypes();
963 else if (declType == typeof(string)) {
964 return TranslateStringMethod(mc);
966 else if (declType == typeof(TimeSpan)) {
967 returnValue = TranslateTimeSpanInstanceMethod(mc);
969 else if (declType == typeof(DateTime)) {
970 returnValue = TranslateDateTimeInstanceMethod(mc);
972 else if (declType == typeof(DateTimeOffset)) {
973 returnValue = TranslateDateTimeOffsetInstanceMethod(mc);
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);
982 throw GetMethodSupportException(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);
992 // No form of the method is supported
993 return Error.MethodHasNoSupportConversionToSql(mc.Method);
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));
1006 return sql.Binary(SqlNodeType.Coalesce, mc.Object, mc.Arguments[0]);
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);
1025 else if (name == "Like") {
1026 if (mc.Arguments.Count == 2) {
1027 return sql.Like(mc.Arguments[0], mc.Arguments[1], null, source);
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);
1033 else if (name == "RawLength") {
1034 SqlExpression length = sql.DATALENGTH(mc.Arguments[0]);
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(
1046 new SqlWhen(lower, sql.ValueFromObject(-1, false, source)),
1047 new SqlWhen(equal, sql.ValueFromObject(0, false, source)),
1049 sql.ValueFromObject(1, false, source), source
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);
1059 else if ((mc.Method.Name == "Add" && mc.Arguments.Count == 1 && mc.Arguments[0].ClrType == typeof(TimeSpan))
1060 || (mc.Method.Name == "AddTicks")) {
1062 SqlExpression sqlTicks = mc.Arguments[0];
1063 if (SqlFactory.IsSqlTimeType(mc.Arguments[0]))
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]);
1070 sql.Divide(ns, 100),
1073 sql.Multiply(sql.ConvertToBigint(hh), 3600000),
1074 sql.Multiply(sql.ConvertToBigint(mm), 60000),
1075 sql.Multiply(sql.ConvertToBigint(ss), 1000)
1080 return this.CreateDateTimeFromDateAndTicks(mc.Object, sqlTicks, source);
1082 else if (mc.Method.Name == "AddMonths") {
1083 // date + m --> DATEADD(month, @m, @date)
1084 returnValue = sql.DATEADD("MONTH", mc.Arguments[0], mc.Object);
1086 else if (mc.Method.Name == "AddYears") {
1087 // date + y --> DATEADD(year, @y, @date)
1088 returnValue = sql.DATEADD("YEAR", mc.Arguments[0], mc.Object);
1090 else if (mc.Method.Name == "AddMilliseconds") {
1091 // date + ms --> DATEADD(ms, @ms, @date)
1092 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, mc.Arguments[0], source);
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);
1100 else if (mc.Method.Name == "AddMinutes") {
1101 SqlExpression ms = sql.Multiply(mc.Arguments[0], 60000);
1102 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source);
1104 else if (mc.Method.Name == "AddHours") {
1105 SqlExpression ms = sql.Multiply(mc.Arguments[0], 3600000);
1106 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source);
1108 else if (mc.Method.Name == "AddDays") {
1109 SqlExpression ms = sql.Multiply(mc.Arguments[0], 86400000);
1110 returnValue = this.CreateDateTimeFromDateAndMs(mc.Object, ms, source);
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);
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]);
1128 SqlExpression ticks = sql.Add(
1129 sql.Divide(ns, 100),
1132 sql.Multiply(sql.ConvertToBigint(hh), 3600000),
1133 sql.Multiply(sql.ConvertToBigint(mi), 60000),
1134 sql.Multiply(sql.ConvertToBigint(ss), 1000)
1136 10000 // 1 millisecond = 10000 ticks
1139 returnValue = this.CreateDateTimeOffsetFromDateAndTicks(mc.Object, ticks, source);
1141 else if (mc.Method.Name == "AddMonths") {
1142 // date + m --> DATEADD(month, @m, @date)
1143 returnValue = sql.DATETIMEOFFSETADD("MONTH", mc.Arguments[0], mc.Object);
1145 else if (mc.Method.Name == "AddYears") {
1146 // date + y --> DATEADD(year, @y, @date)
1147 returnValue = sql.DATETIMEOFFSETADD("YEAR", mc.Arguments[0], mc.Object);
1149 else if (mc.Method.Name == "AddMilliseconds") {
1150 // date + ms --> DATEADD(ms, @ms, @date)
1151 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, mc.Arguments[0], source);
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);
1159 else if (mc.Method.Name == "AddMinutes") {
1160 SqlExpression ms = sql.Multiply(mc.Arguments[0], 60000);
1161 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source);
1163 else if (mc.Method.Name == "AddHours") {
1164 SqlExpression ms = sql.Multiply(mc.Arguments[0], 3600000);
1165 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source);
1167 else if (mc.Method.Name == "AddDays") {
1168 SqlExpression ms = sql.Multiply(mc.Arguments[0], 86400000);
1169 returnValue = this.CreateDateTimeOffsetFromDateAndMs(mc.Object, ms, source);
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]);
1180 else if (mc.Method.Name == "Subtract") {
1181 returnValue = sql.Subtract(mc.Object, mc.Arguments[0]);
1183 else if (mc.Method.Name == "CompareTo") {
1184 returnValue = CreateComparison(mc.Object, mc.Arguments[0], source);
1186 else if (mc.Method.Name == "Duration") {
1187 if (SqlFactory.IsSqlTimeType(mc.Object))
1190 returnValue = sql.FunctionCall(typeof(TimeSpan), "ABS", new SqlExpression[] { mc.Object }, source);
1192 else if (mc.Method.Name == "Negate") {
1193 returnValue = sql.Unary(SqlNodeType.Negate, mc.Object, source);
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) {
1206 targetType = typeof(bool);
1209 targetType = typeof(decimal);
1212 targetType = typeof(byte);
1215 targetType = typeof(char);
1216 if (expr.SqlType.IsChar) {
1217 providerType = sql.TypeProvider.From(targetType, 1);
1222 Type nnType = TypeSystem.GetNonNullableType(expr.ClrType);
1223 if (nnType == typeof(string) || nnType == typeof(DateTime)) {
1224 targetType = typeof(DateTime);
1227 throw Error.ConvertToDateTimeOnlyForDateTimeOrString();
1230 // not applicable: "ToDateTimeOffset"
1232 targetType = typeof(double);
1235 targetType = typeof(Int16);
1238 targetType = typeof(Int32);
1241 targetType = typeof(Int64);
1244 targetType = typeof(float);
1247 targetType = typeof(string);
1255 throw GetMethodSupportException(mc);
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
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);
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);
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);
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);
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);
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);
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);
1329 SqlExpression result = sql.Add(
1330 sql.Divide(intNSec, 100),
1331 sql.Multiply(intUSec, 10),
1334 sql.Multiply(sql.ConvertToBigint(intDays), 86400000),
1340 return sql.ConvertTo(resultType, result);
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);
1351 sql.Divide(ns, 100),
1354 sql.Multiply(sql.ConvertToBigint(hh), 3600000),
1355 sql.Multiply(sql.ConvertToBigint(mi), 60000),
1356 sql.Multiply(sql.ConvertToBigint(ss), 1000)
1358 10000 // 1 millisecond = 10000 ticks
1363 return TypeSystem.GetNonNullableType(bo.Left.ClrType) == typeof(DateTimeOffset) ?
1364 CreateDateTimeOffsetFromDateAndTicks(
1366 sql.Unary(SqlNodeType.Negate, right, bo.SourceExpression),
1367 bo.SourceExpression, resultNullable
1369 CreateDateTimeFromDateAndTicks(
1371 sql.Unary(SqlNodeType.Negate, right, bo.SourceExpression),
1372 bo.SourceExpression, resultNullable
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);
1384 return CreateDateTimeFromDateAndTicks(bo.Left, bo.Right, bo.SourceExpression, resultNullable);
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) {
1397 return sql.Binary(SqlNodeType.Mul, mc.Arguments[0], mc.Arguments[1]);
1399 return sql.Binary(SqlNodeType.Div, mc.Arguments[0], mc.Arguments[1]);
1401 return sql.Binary(SqlNodeType.Sub, mc.Arguments[0], mc.Arguments[1]);
1403 return sql.Binary(SqlNodeType.Add, mc.Arguments[0], mc.Arguments[1]);
1405 return sql.Binary(SqlNodeType.Mod, mc.Arguments[0], mc.Arguments[1]);
1408 return sql.FunctionCall(mc.Method.ReturnType, "ROUND", mc.Arguments, mc.SourceExpression);
1411 else if (mc.Arguments.Count == 1) {
1412 switch (mc.Method.Name) {
1414 return sql.Unary(SqlNodeType.Negate, mc.Arguments[0], source);
1417 // Truncate(x) --> ROUND (x, 0, 1)
1418 return sql.FunctionCall(mc.Method.ReturnType, "ROUND",
1419 new SqlExpression[] {
1421 sql.ValueFromObject(0, false, mc.SourceExpression),
1422 sql.ValueFromObject(1, false, mc.SourceExpression)
1424 mc.SourceExpression);
1427 return sql.FunctionCall(mc.Method.ReturnType, "ROUND",
1428 new SqlExpression[] {
1430 sql.ValueFromObject(0, false, mc.SourceExpression)
1432 mc.SourceExpression);
1434 if (mc.Method.Name.StartsWith("To", StringComparison.Ordinal)) {
1435 return this.TranslateConvertStaticMethod(mc);
1441 throw GetMethodSupportException(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;
1452 exprs = arr.Expressions;
1455 exprs = mc.Arguments;
1457 if (exprs.Count == 0) {
1458 returnValue = sql.ValueFromObject("", false, source);
1462 if (exprs[0].SqlType.IsString || exprs[0].SqlType.IsChar) {
1466 sum = sql.ConvertTo(typeof(string), exprs[0]);
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]);
1473 sum = sql.Concat(sum, sql.ConvertTo(typeof(string), exprs[i]));
1479 else if ((mc.Method.Name == "Equals") && (mc.Arguments.Count == 2)) {
1480 returnValue = sql.Binary(SqlNodeType.EQ2V, mc.Arguments[0], mc.Arguments[1]);
1482 else if ((mc.Method.Name == "Compare") && (mc.Arguments.Count == 2)) {
1483 returnValue = CreateComparison(mc.Arguments[0], mc.Arguments[1], source);
1486 throw GetMethodSupportException(mc);
1487 //return returnValue;
1491 private SqlExpression CreateFunctionCallStatic1(Type type, string functionName, List<SqlExpression> arguments, Expression source) {
1492 return sql.FunctionCall(type, functionName, new SqlExpression[] { arguments[0] }, 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);
1499 private SqlExpression TranslateStringMethod(SqlMethodCall mc) {
1500 Expression source = mc.SourceExpression;
1502 switch (mc.Method.Name) {
1504 if (mc.Arguments.Count == 1) {
1505 SqlExpression pattern = mc.Arguments[0];
1506 SqlExpression escape = null;
1507 bool needsEscape = true;
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);
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,
1520 Expression.Invoke(Expression.Constant(getStringContainsPatternForced), cp.Accessor.Body, Expression.Constant('~')),
1521 cp.Accessor.Parameters[0]
1527 throw Error.NonConstantExpressionsNotSupportedFor("String.Contains");
1531 escape = sql.ValueFromObject("~", false, source);
1534 return sql.Like(mc.Object, pattern, escape, source);
1538 if (mc.Arguments.Count == 1) {
1539 SqlExpression pattern = mc.Arguments[0];
1540 SqlExpression escape = null;
1541 bool needsEscape = true;
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);
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,
1554 Expression.Invoke(Expression.Constant(getStringStartsWithPatternForced), cp.Accessor.Body, Expression.Constant('~')),
1555 cp.Accessor.Parameters[0]
1561 throw Error.NonConstantExpressionsNotSupportedFor("String.StartsWith");
1565 escape = sql.ValueFromObject("~", false, source);
1568 return sql.Like(mc.Object, pattern, escape, source);
1572 if (mc.Arguments.Count == 1) {
1573 SqlExpression pattern = mc.Arguments[0];
1574 SqlExpression escape = null;
1575 bool needsEscape = true;
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);
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,
1588 Expression.Invoke(Expression.Constant(getStringEndsWithPatternForced), cp.Accessor.Body, Expression.Constant('~')),
1589 cp.Accessor.Parameters[0]
1595 throw Error.NonConstantExpressionsNotSupportedFor("String.EndsWith");
1599 escape = sql.ValueFromObject("~", false, source);
1602 return sql.Like(mc.Object, pattern, escape, source);
1606 if (mc.Arguments.Count == 1) {
1607 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) {
1608 throw Error.ArgumentNull("value");
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[] {
1618 return sql.SearchedCase(new SqlWhen[] { when }, @else, source);
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");
1625 if (mc.Arguments[1].ClrType == typeof(StringComparison)) {
1626 throw Error.IndexOfWithStringComparisonArgNotSupported();
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[] {
1637 sql.Add(mc.Arguments[1], 1)
1640 return sql.SearchedCase(new SqlWhen[] { when }, @else, source);
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");
1646 if (mc.Arguments[2].ClrType == typeof(StringComparison)) {
1647 throw Error.IndexOfWithStringComparisonArgNotSupported();
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[] {
1660 sql.ValueFromObject(1, false, source),
1661 sql.Add(mc.Arguments[1], mc.Arguments[2])
1664 SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX",
1665 new SqlExpression[] {
1668 sql.Add(mc.Arguments[1], 1)
1671 return sql.SearchedCase(new SqlWhen[] { when }, @else, source);
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))
1680 SqlExpression exprPart = mc.Arguments[0];
1681 if (exprPart is SqlValue && ((SqlValue)exprPart).Value == null) {
1682 throw Error.ArgumentNull("value");
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)));
1694 SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source));
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));
1700 return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained },
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))
1709 if (mc.Arguments[1].ClrType == typeof(StringComparison)) {
1710 throw Error.LastIndexOfWithStringComparisonArgNotSupported();
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");
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)));
1728 SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source));
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]);
1736 return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained },
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))
1745 if (mc.Arguments[2].ClrType == typeof(StringComparison)) {
1746 throw Error.LastIndexOfWithStringComparisonArgNotSupported();
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");
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)));
1766 SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source));
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]);
1774 return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained },
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");
1784 SqlFunctionCall stuffCall = sql.FunctionCall(
1785 typeof(string), "STUFF",
1786 new SqlExpression[] {
1788 sql.Add(mc.Arguments[0], 1),
1789 sql.ValueFromObject(0, false, 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]);
1801 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(insertingAtEnd, stringConcat) }, stuffCall, source);
1805 if (mc.Arguments.Count == 1) {
1807 // CASE WHEN CLRLENGTH(@s)>= @i THEN @s
1808 // ELSE SPACE(@i-CLRLENGTH(@s)) + @s
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);
1818 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source);
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
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);
1834 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source);
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))
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);
1851 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source);
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))
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);
1867 return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source);
1872 if (mc.Arguments.Count == 1) {
1873 return sql.FunctionCall(
1874 typeof(string), "STUFF",
1875 new SqlExpression[] {
1877 sql.Add(mc.Arguments[0], 1),
1878 sql.CLRLENGTH(mc.Object),
1879 sql.ValueFromObject("", false, source)
1883 else if (mc.Arguments.Count == 2) {
1884 return sql.FunctionCall(
1885 typeof(string), "STUFF",
1886 new SqlExpression[] {
1888 sql.Add(mc.Arguments[0], 1),
1890 sql.ValueFromObject("", false, source)
1896 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) {
1897 throw Error.ArgumentNull("old");
1899 if (mc.Arguments[1] is SqlValue && ((SqlValue)mc.Arguments[1]).Value == null) {
1900 throw Error.ArgumentNull("new");
1902 return sql.FunctionCall(
1903 typeof(string), "REPLACE",
1904 new SqlExpression[] {
1911 if (mc.Arguments.Count == 1) {
1912 return sql.FunctionCall(
1913 typeof(string), "SUBSTRING",
1914 new SqlExpression[] {
1916 sql.Add(mc.Arguments[0], 1),
1917 sql.CLRLENGTH(mc.Object)
1921 else if (mc.Arguments.Count == 2) {
1922 return sql.FunctionCall(
1923 typeof(string), "SUBSTRING",
1924 new SqlExpression[] {
1926 sql.Add(mc.Arguments[0], 1),
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)
1943 if (mc.Arguments.Count == 0) {
1944 return sql.FunctionCall(typeof(string), "LOWER", new SqlExpression[] { mc.Object }, source);
1948 if (mc.Arguments.Count == 0) {
1949 return sql.FunctionCall(typeof(string), "UPPER", new SqlExpression[] { mc.Object }, source);
1953 // s[i] --> SUBSTRING(@s, @i+1, 1)
1954 if (mc.Arguments.Count == 1) {
1955 return sql.FunctionCall(typeof(char), "SUBSTRING", new SqlExpression[]
1957 sql.Add( mc.Arguments[0], 1),
1958 sql.ValueFromObject(1, false, source)
1963 if (mc.Arguments.Count == 1) {
1964 if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) {
1965 throw Error.ArgumentNull("value");
1967 return CreateComparison(mc.Object, mc.Arguments[0], source);
1971 throw GetMethodSupportException(mc);
1974 private SqlExpression TranslateMathMethod(SqlMethodCall mc) {
1975 Expression source = mc.SourceExpression;
1976 switch (mc.Method.Name) {
1978 if (mc.Arguments.Count == 1) {
1979 return sql.FunctionCall(mc.Arguments[0].ClrType, "ABS", new SqlExpression[] { mc.Arguments[0] }, source);
1983 if (mc.Arguments.Count == 1) {
1984 return this.CreateFunctionCallStatic1(typeof(double), "ACOS", mc.Arguments, source);
1988 if (mc.Arguments.Count == 1) {
1989 return this.CreateFunctionCallStatic1(typeof(double), "ASIN", mc.Arguments, source);
1993 if (mc.Arguments.Count == 1) {
1994 return this.CreateFunctionCallStatic1(typeof(double), "ATAN", mc.Arguments, source);
1998 if (mc.Arguments.Count == 2) {
1999 return this.CreateFunctionCallStatic2(typeof(double), "ATN2", mc.Arguments, source);
2003 if (mc.Arguments.Count == 2) {
2004 return sql.Multiply(sql.ConvertToBigint(mc.Arguments[0]), sql.ConvertToBigint(mc.Arguments[1]));
2008 if (mc.Arguments.Count == 1) {
2009 return this.CreateFunctionCallStatic1(mc.Arguments[0].ClrType, "CEILING", mc.Arguments, source);
2013 if (mc.Arguments.Count == 1) {
2014 return this.CreateFunctionCallStatic1(typeof(double), "COS", mc.Arguments, source);
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);
2026 // DivRem has out parameter
2028 if (mc.Arguments.Count == 1) {
2029 return this.CreateFunctionCallStatic1(typeof(double), "EXP", mc.Arguments, source);
2033 if (mc.Arguments.Count == 1) {
2034 return this.CreateFunctionCallStatic1(mc.Arguments[0].ClrType, "FLOOR", mc.Arguments, source);
2037 // Math.IEEERemainder - difficult to implement correctly since SQL rounds differently
2039 if (mc.Arguments.Count == 1) {
2040 return this.CreateFunctionCallStatic1(typeof(double), "LOG", mc.Arguments, source);
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);
2050 if (mc.Arguments.Count == 1) {
2051 return this.CreateFunctionCallStatic1(typeof(double), "LOG10", mc.Arguments, source);
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);
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);
2073 if (mc.Arguments.Count == 2) {
2074 return this.CreateFunctionCallStatic2(mc.ClrType, "POWER", mc.Arguments, source);
2078 int nParams = mc.Arguments.Count;
2079 if ((mc.Arguments[nParams - 1].ClrType != typeof(MidpointRounding))) {
2080 throw Error.MathRoundNotSupported();
2083 SqlExpression x = mc.Arguments[0];
2084 SqlExpression i = null;
2086 i = sql.ValueFromObject(0, false, source);
2089 i = mc.Arguments[1];
2091 SqlExpression roundingMethod = mc.Arguments[nParams - 1];
2092 if (roundingMethod.NodeType != SqlNodeType.Value) {
2093 throw Error.NonConstantExpressionsNotSupportedForRounding();
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);
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)
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);
2114 if (mc.Arguments.Count == 1) {
2115 return sql.FunctionCall(typeof(int), "SIGN", new SqlExpression[] { mc.Arguments[0] }, source);
2119 if (mc.Arguments.Count == 1) {
2120 return this.CreateFunctionCallStatic1(typeof(double), "SIN", mc.Arguments, source);
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);
2133 if (mc.Arguments.Count == 1) {
2134 return this.CreateFunctionCallStatic1(typeof(double), "SQRT", mc.Arguments, source);
2138 if (mc.Arguments.Count == 1) {
2139 return this.CreateFunctionCallStatic1(typeof(double), "TAN", mc.Arguments, source);
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));
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);
2159 throw GetMethodSupportException(mc);
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.")]
2163 internal override SqlNode VisitMember(SqlMember m) {
2164 SqlExpression exp = this.VisitExpression(m.Expression);
2165 MemberInfo member = m.Member;
2166 Expression source = m.SourceExpression;
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);
2175 else if (baseClrTypeOfExpr == typeof(Binary) && member.Name == "Length") {
2176 return sql.DATALENGTH(exp);
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);
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);
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)),
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);
2199 SqlExpression result = exp;
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);
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);
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);
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));
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);
2230 // .DayOfWeek returns a System.DayOfWeek, so ConvertTo that enum.
2231 return sql.ConvertTo(typeof(DayOfWeek),
2234 sql.Add(new SqlVariable(typeof(int), sql.Default(typeof(int)), "@@DATEFIRST", source), 6)
2239 else if (baseClrTypeOfExpr == typeof(System.TimeSpan)) {
2240 switch (member.Name) {
2242 if (SqlFactory.IsSqlTimeType(exp)) {
2243 return this.sql.Divide(
2244 sql.ConvertToBigint(
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))
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)
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)
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)
2284 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerMinute);
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)
2294 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerHour);
2296 if (SqlFactory.IsSqlTimeType(exp)) {
2297 return this.sql.Divide(
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)),
2306 return sql.Divide(sql.ConvertToDouble(exp), TimeSpan.TicksPerDay);
2307 case "Milliseconds":
2308 if (SqlFactory.IsSqlTimeType(exp)) {
2309 return this.sql.DATEPART("MILLISECOND", exp);
2311 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerMillisecond)), 1000));
2313 if (SqlFactory.IsSqlTimeType(exp)) {
2314 return this.sql.DATEPART("SECOND", exp);
2316 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerSecond)), 60));
2318 if (SqlFactory.IsSqlTimeType(exp)) {
2319 return this.sql.DATEPART("MINUTE", exp);
2321 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerMinute)), 60));
2323 if (SqlFactory.IsSqlTimeType(exp)) {
2324 return this.sql.DATEPART("HOUR", exp);
2326 return sql.ConvertToInt(sql.Mod(sql.ConvertToBigint(sql.Divide(exp, TimeSpan.TicksPerHour)), 24));
2328 if (SqlFactory.IsSqlTimeType(exp)) {
2329 return this.sql.ValueFromObject(0, false, exp.SourceExpression);
2331 return sql.ConvertToInt(sql.Divide(exp, TimeSpan.TicksPerDay));
2333 throw Error.MemberCannotBeTranslated(member.DeclaringType, member.Name);
2336 throw Error.MemberCannotBeTranslated(member.DeclaringType, member.Name);
2339 // date + timespan --> DATEADD( day, @timespan/864000000000, DATEADD(ms,(@timespan/1000) % 86400000 , @date))
2340 private SqlExpression CreateDateTimeFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source) {
2341 return CreateDateTimeFromDateAndTicks(sqlDate, sqlTicks, source, false);
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);
2349 // date + ms --> DATEADD( day, @ms/86400000, DATEADD(ms, @ms % 86400000 , @date))
2350 private SqlExpression CreateDateTimeFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source) {
2351 return CreateDateTimeFromDateAndMs(sqlDate, ms, source, false);
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);
2360 // date + timespan --> DATEADD( day, @timespan/864000000000, DATEADD(ms,(@timespan/1000) % 86400000 , @date))
2361 private SqlExpression CreateDateTimeOffsetFromDateAndTicks(SqlExpression sqlDate, SqlExpression sqlTicks, Expression source) {
2362 return CreateDateTimeOffsetFromDateAndTicks(sqlDate, sqlTicks, source, false);
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);
2370 // date + ms --> DATEADD( day, @ms/86400000, DATEADD(ms, @ms % 86400000 , @date))
2371 private SqlExpression CreateDateTimeOffsetFromDateAndMs(SqlExpression sqlDate, SqlExpression ms, Expression source) {
2372 return CreateDateTimeOffsetFromDateAndMs(sqlDate, ms, source, false);
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);
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) {
2388 targetType = typeof(bool);
2391 targetType = typeof(sbyte);
2394 targetType = typeof(byte);
2397 targetType = typeof(char);
2399 case "ToCharArrayRankOne":
2400 targetType = typeof(char[]);
2403 targetType = typeof(DateTime);
2406 targetType = typeof(decimal);
2409 targetType = typeof(double);
2412 targetType = typeof(Int32);
2415 targetType = typeof(UInt32);
2418 targetType = typeof(Int64);
2421 targetType = typeof(UInt64);
2424 targetType = typeof(Int16);
2427 targetType = typeof(UInt16);
2430 targetType = typeof(float);
2433 targetType = typeof(string);
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>();
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));
2446 return sql.Case(targetType, expr, matchesList, valuesList, source);
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);
2453 return mc.Arguments[0];
2457 throw GetMethodSupportException(mc);
2460 private SqlExpression TranslateVbCompareString(SqlMethodCall mc) {
2461 if (mc.Arguments.Count >= 2) {
2462 return CreateComparison(mc.Arguments[0], mc.Arguments[1], mc.SourceExpression);
2464 throw GetMethodSupportException(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;
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;
2482 else if (pattern.NodeType == SqlNodeType.ClientParameter) {
2483 SqlClientParameter cp = (SqlClientParameter)pattern;
2484 pattern = new SqlClientParameter(
2485 cp.ClrType, cp.SqlType,
2487 Expression.Call(typeof(SqlHelpers), "TranslateVBLikePattern", Type.EmptyTypes, cp.Accessor.Body, Expression.Constant('~')),
2488 cp.Accessor.Parameters[0]
2494 throw Error.NonConstantExpressionsNotSupportedFor("LIKE");
2496 SqlExpression escape = needsEscape ? sql.ValueFromObject("~", false, mc.SourceExpression) : null;
2497 return sql.Like(mc.Arguments[0], pattern, escape, source);