Exemplo n.º 1
0
        public static void Method()
        {
            IQuery <User> q = context.Query <User>();

            var space = new char[] { ' ' };

            DateTime startTime = DateTime.Now;
            DateTime endTime   = DateTime.Now.AddDays(1);

            q.Select(a => new
            {
                Id = a.Id,

                String_Length = (int?)a.Name.Length,                                 //LEN([Users].[Name])
                Substring     = a.Name.Substring(0),                                 //SUBSTRING([Users].[Name],0 + 1,LEN([Users].[Name]))
                Substring1    = a.Name.Substring(1),                                 //SUBSTRING([Users].[Name],1 + 1,LEN([Users].[Name]))
                Substring1_2  = a.Name.Substring(1, 2),                              //SUBSTRING([Users].[Name],1 + 1,2)
                ToLower       = a.Name.ToLower(),                                    //LOWER([Users].[Name])
                ToUpper       = a.Name.ToUpper(),                                    //UPPER([Users].[Name])
                IsNullOrEmpty = string.IsNullOrEmpty(a.Name),                        //too long
                Contains      = (bool?)a.Name.Contains("s"),                         //
                Trim          = a.Name.Trim(),                                       //RTRIM(LTRIM([Users].[Name]))
                TrimStart     = a.Name.TrimStart(space),                             //LTRIM([Users].[Name])
                TrimEnd       = a.Name.TrimEnd(space),                               //RTRIM([Users].[Name])
                StartsWith    = (bool?)a.Name.StartsWith("s"),                       //
                EndsWith      = (bool?)a.Name.EndsWith("s"),                         //

                DiffYears        = DbFunctions.DiffYears(startTime, endTime),        //DATEDIFF(YEAR,@P_0,@P_1)
                DiffMonths       = DbFunctions.DiffMonths(startTime, endTime),       //DATEDIFF(MONTH,@P_0,@P_1)
                DiffDays         = DbFunctions.DiffDays(startTime, endTime),         //DATEDIFF(DAY,@P_0,@P_1)
                DiffHours        = DbFunctions.DiffHours(startTime, endTime),        //DATEDIFF(HOUR,@P_0,@P_1)
                DiffMinutes      = DbFunctions.DiffMinutes(startTime, endTime),      //DATEDIFF(MINUTE,@P_0,@P_1)
                DiffSeconds      = DbFunctions.DiffSeconds(startTime, endTime),      //DATEDIFF(SECOND,@P_0,@P_1)
                DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime), //DATEDIFF(MILLISECOND,@P_0,@P_1)
                //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//DATEDIFF(MICROSECOND,@P_0,@P_1)  Exception

                /* No longer support method 'DateTime.Subtract(DateTime d)', instead of using 'DbFunctions.DiffXX' */
                //SubtractTotalDays = endTime.Subtract(startTime).TotalDays,//CAST(DATEDIFF(DAY,@P_0,@P_1)
                //SubtractTotalHours = endTime.Subtract(startTime).TotalHours,//CAST(DATEDIFF(HOUR,@P_0,@P_1)
                //SubtractTotalMinutes = endTime.Subtract(startTime).TotalMinutes,//CAST(DATEDIFF(MINUTE,@P_0,@P_1)
                //SubtractTotalSeconds = endTime.Subtract(startTime).TotalSeconds,//CAST(DATEDIFF(SECOND,@P_0,@P_1)
                //SubtractTotalMilliseconds = endTime.Subtract(startTime).TotalMilliseconds,//CAST(DATEDIFF(MILLISECOND,@P_0,@P_1)

                AddYears        = startTime.AddYears(1),                         //DATEADD(YEAR,1,@P_0)
                AddMonths       = startTime.AddMonths(1),                        //DATEADD(MONTH,1,@P_0)
                AddDays         = startTime.AddDays(1),                          //DATEADD(DAY,1,@P_0)
                AddHours        = startTime.AddHours(1),                         //DATEADD(HOUR,1,@P_0)
                AddMinutes      = startTime.AddMinutes(2),                       //DATEADD(MINUTE,2,@P_0)
                AddSeconds      = startTime.AddSeconds(120),                     //DATEADD(SECOND,120,@P_0)
                AddMilliseconds = startTime.AddMilliseconds(20000),              //DATEADD(MILLISECOND,20000,@P_0)

                Now         = DateTime.Now,                                      //GETDATE()
                UtcNow      = DateTime.UtcNow,                                   //GETUTCDATE()
                Today       = DateTime.Today,                                    //CAST(GETDATE() AS DATE)
                Date        = DateTime.Now.Date,                                 //CAST(GETDATE() AS DATE)
                Year        = DateTime.Now.Year,                                 //DATEPART(YEAR,GETDATE())
                Month       = DateTime.Now.Month,                                //DATEPART(MONTH,GETDATE())
                Day         = DateTime.Now.Day,                                  //DATEPART(DAY,GETDATE())
                Hour        = DateTime.Now.Hour,                                 //DATEPART(HOUR,GETDATE())
                Minute      = DateTime.Now.Minute,                               //DATEPART(MINUTE,GETDATE())
                Second      = DateTime.Now.Second,                               //DATEPART(SECOND,GETDATE())
                Millisecond = DateTime.Now.Millisecond,                          //DATEPART(MILLISECOND,GETDATE())
                DayOfWeek   = DateTime.Now.DayOfWeek,                            //(DATEPART(WEEKDAY,GETDATE()) - 1)

                Int_Parse    = int.Parse("1"),                                   //CAST(N'1' AS INT)
                Int16_Parse  = Int16.Parse("11"),                                //CAST(N'11' AS SMALLINT)
                Long_Parse   = long.Parse("2"),                                  //CAST(N'2' AS BIGINT)
                Double_Parse = double.Parse("3"),                                //CAST(N'3' AS FLOAT)
                Float_Parse  = float.Parse("4"),                                 //CAST(N'4' AS REAL)
                //Decimal_Parse = decimal.Parse("5"),//CAST(N'5' AS DECIMAL)  ps: 'Decimal.Parse(string s)' is not supported now,because we don't know the precision and scale information.
                Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"), //CAST(N'D544BC4C-739E-4CD3-A3D3-7BF803FCE179' AS UNIQUEIDENTIFIER) AS [Guid_Parse]

                Bool_Parse     = bool.Parse("1"),                                //CASE WHEN CAST(N'1' AS BIT) = CAST(1 AS BIT) THEN CAST(1 AS BIT) WHEN NOT (CAST(N'1' AS BIT) = CAST(1 AS BIT)) THEN CAST(0 AS BIT) ELSE NULL END AS [Bool_Parse]
                DateTime_Parse = DateTime.Parse("1992-1-16"),                    //CAST(N'1992-1-16' AS DATETIME) AS [DateTime_Parse]

                B = a.Age == null ? false : a.Age > 1,
            }).ToList();

            ConsoleHelper.WriteLineAndReadKey();
        }
Exemplo n.º 2
0
        public static void Method()
        {
            IQuery <User> q = context.Query <User>();

            var space = new char[] { ' ' };

            DateTime startTime = DateTime.Now;
            DateTime endTime   = DateTime.Now.AddDays(1);

            var ret = q.Select(a => new
            {
                Id = a.Id,

                String_Length = (int?)a.Name.Length,                       //LENGTH(`Users`.`Name`)
                Substring     = a.Name.Substring(0),                       //SUBSTRING(`Users`.`Name`,0 + 1,LENGTH(`Users`.`Name`))
                Substring1    = a.Name.Substring(1),                       //SUBSTRING(`Users`.`Name`,1 + 1,LENGTH(`Users`.`Name`))
                Substring1_2  = a.Name.Substring(1, 2),                    //SUBSTRING(`Users`.`Name`,1 + 1,2)
                ToLower       = a.Name.ToLower(),                          //LOWER(`Users`.`Name`)
                ToUpper       = a.Name.ToUpper(),                          //UPPER(`Users`.`Name`)
                IsNullOrEmpty = string.IsNullOrEmpty(a.Name),              //CASE WHEN (`Users`.`Name` IS NULL OR `Users`.`Name` = N'') THEN 1 ELSE 0 END = 1
                Contains      = (bool?)a.Name.Contains("s"),               //`Users`.`Name` LIKE CONCAT('%',N's','%')
                Trim          = a.Name.Trim(),                             //TRIM(`Users`.`Name`)
                TrimStart     = a.Name.TrimStart(space),                   //LTRIM(`Users`.`Name`)
                TrimEnd       = a.Name.TrimEnd(space),                     //RTRIM(`Users`.`Name`)
                StartsWith    = (bool?)a.Name.StartsWith("s"),             //`Users`.`Name` LIKE CONCAT(N's','%')
                EndsWith      = (bool?)a.Name.EndsWith("s"),               //`Users`.`Name` LIKE CONCAT('%',N's')

                DiffYears   = DbFunctions.DiffYears(startTime, endTime),   //TIMESTAMPDIFF(YEAR,?P_0,?P_1)
                DiffMonths  = DbFunctions.DiffMonths(startTime, endTime),  //TIMESTAMPDIFF(MONTH,?P_0,?P_1)
                DiffDays    = DbFunctions.DiffDays(startTime, endTime),    //TIMESTAMPDIFF(DAY,?P_0,?P_1)
                DiffHours   = DbFunctions.DiffHours(startTime, endTime),   //TIMESTAMPDIFF(HOUR,?P_0,?P_1)
                DiffMinutes = DbFunctions.DiffMinutes(startTime, endTime), //TIMESTAMPDIFF(MINUTE,?P_0,?P_1)
                DiffSeconds = DbFunctions.DiffSeconds(startTime, endTime), //TIMESTAMPDIFF(SECOND,?P_0,?P_1)
                //DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime),//MySql 不支持 Millisecond
                //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//ex

                Now         = DateTime.Now,             //NOW()
                UtcNow      = DateTime.UtcNow,          //UTC_TIMESTAMP()
                Today       = DateTime.Today,           //CURDATE()
                Date        = DateTime.Now.Date,        //DATE(NOW())
                Year        = DateTime.Now.Year,        //YEAR(NOW())
                Month       = DateTime.Now.Month,       //MONTH(NOW())
                Day         = DateTime.Now.Day,         //DAY(NOW())
                Hour        = DateTime.Now.Hour,        //HOUR(NOW())
                Minute      = DateTime.Now.Minute,      //MINUTE(NOW())
                Second      = DateTime.Now.Second,      //SECOND(NOW())
                Millisecond = DateTime.Now.Millisecond, //?P_2 AS `Millisecond`
                DayOfWeek   = DateTime.Now.DayOfWeek,   //(DAYOFWEEK(NOW()) - 1)

                //Byte_Parse = byte.Parse("1"),//不支持
                Int_Parse   = int.Parse("1"),    //CAST(N'1' AS SIGNED)
                Int16_Parse = Int16.Parse("11"), //CAST(N'11' AS SIGNED)
                Long_Parse  = long.Parse("2"),   //CAST(N'2' AS SIGNED)
                //Double_Parse = double.Parse("3"),//N'3' 不支持,否则可能会成为BUG
                //Float_Parse = float.Parse("4"),//N'4' 不支持,否则可能会成为BUG
                //Decimal_Parse = decimal.Parse("5"),//不支持
                Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"), //N'D544BC4C-739E-4CD3-A3D3-7BF803FCE179'

                Bool_Parse     = bool.Parse("1"),                                //CAST(N'1' AS SIGNED)
                DateTime_Parse = DateTime.Parse("2014-1-1"),                     //CAST(N'2014-1-1' AS DATETIME)
            }).ToList();

            ConsoleHelper.WriteLineAndReadKey();
        }
Exemplo n.º 3
0
        public static void Method()
        {
            IQuery <User> q = context.Query <User>();

            var space = new char[] { ' ' };

            DateTime startTime = DateTime.Now;
            DateTime endTime   = DateTime.Now.AddDays(1);

            var ret = q.Select(a => new
            {
                Id = a.Id,

                String_Length = (int?)a.Name.Length,                       //LENGTH([Users].[Name])
                Substring     = a.Name.Substring(0),                       //SUBSTR([Users].[Name],0 + 1)
                Substring1    = a.Name.Substring(1),                       //SUBSTR([Users].[Name],1 + 1)
                Substring1_2  = a.Name.Substring(1, 2),                    //SUBSTR([Users].[Name],1 + 1,2)
                ToLower       = a.Name.ToLower(),                          //LOWER([Users].[Name])
                ToUpper       = a.Name.ToUpper(),                          //UPPER([Users].[Name])
                IsNullOrEmpty = string.IsNullOrEmpty(a.Name),              //CASE WHEN ([Users].[Name] IS NULL OR [Users].[Name] = '') THEN 1 ELSE 0 END = 1
                Contains      = (bool?)a.Name.Contains("s"),               //[Users].[Name] LIKE '%' || 's' || '%'
                StartsWith    = (bool?)a.Name.StartsWith("s"),             //[Users].[Name] LIKE 's' || '%'
                EndsWith      = (bool?)a.Name.EndsWith("s"),               //[Users].[Name] LIKE '%' || 's'
                Trim          = a.Name.Trim(),                             //TRIM([Users].[Name])
                TrimStart     = a.Name.TrimStart(space),                   //LTRIM([Users].[Name])
                TrimEnd       = a.Name.TrimEnd(space),                     //RTRIM([Users].[Name])

                DiffYears   = DbFunctions.DiffYears(startTime, endTime),   //(CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER))
                DiffMonths  = DbFunctions.DiffMonths(startTime, endTime),  //((CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER)) * 12 + (CAST(STRFTIME('%m',@P_0) AS INTEGER) - CAST(STRFTIME('%m',@P_1) AS INTEGER)))
                DiffDays    = DbFunctions.DiffDays(startTime, endTime),    //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) AS INTEGER)
                DiffHours   = DbFunctions.DiffHours(startTime, endTime),   //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 24 AS INTEGER)
                DiffMinutes = DbFunctions.DiffMinutes(startTime, endTime), //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 1440 AS INTEGER)
                DiffSeconds = DbFunctions.DiffSeconds(startTime, endTime), //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 86400 AS INTEGER)
                //DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime),//不支持 Millisecond
                //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//不支持 Microseconds

                AddYears   = startTime.AddYears(1),     //DATETIME(@P_0,'+' || 1 || ' years')
                AddMonths  = startTime.AddMonths(1),    //DATETIME(@P_0,'+' || 1 || ' months')
                AddDays    = startTime.AddDays(1),      //DATETIME(@P_0,'+' || 1 || ' days')
                AddHours   = startTime.AddHours(1),     //DATETIME(@P_0,'+' || 1 || ' hours')
                AddMinutes = startTime.AddMinutes(2),   //DATETIME(@P_0,'+' || 2 || ' minutes')
                AddSeconds = startTime.AddSeconds(120), //DATETIME(@P_0,'+' || 120 || ' seconds')
                //AddMilliseconds = startTime.AddMilliseconds(2000),//不支持

                Now         = DateTime.Now,             //DATETIME('NOW','LOCALTIME')
                UtcNow      = DateTime.UtcNow,          //DATETIME()
                Today       = DateTime.Today,           //DATE('NOW','LOCALTIME')
                Date        = DateTime.Now.Date,        //DATE('NOW','LOCALTIME')
                Year        = DateTime.Now.Year,        //CAST(STRFTIME('%Y',DATETIME('NOW','LOCALTIME')) AS INTEGER)
                Month       = DateTime.Now.Month,       //CAST(STRFTIME('%m',DATETIME('NOW','LOCALTIME')) AS INTEGER)
                Day         = DateTime.Now.Day,         //CAST(STRFTIME('%d',DATETIME('NOW','LOCALTIME')) AS INTEGER)
                Hour        = DateTime.Now.Hour,        //CAST(STRFTIME('%H',DATETIME('NOW','LOCALTIME')) AS INTEGER)
                Minute      = DateTime.Now.Minute,      //CAST(STRFTIME('%M',DATETIME('NOW','LOCALTIME')) AS INTEGER)
                Second      = DateTime.Now.Second,      //CAST(STRFTIME('%S',DATETIME('NOW','LOCALTIME')) AS INTEGER)
                Millisecond = DateTime.Now.Millisecond, //@P_2 直接计算 DateTime.Now.Millisecond 的值
                DayOfWeek   = DateTime.Now.DayOfWeek,   //CAST(STRFTIME('%w',DATETIME('NOW','LOCALTIME')) AS INTEGER)

                Byte_Parse   = byte.Parse("1"),         //CAST('1' AS INTEGER)
                Int_Parse    = int.Parse("1"),          //CAST('1' AS INTEGER)
                Int16_Parse  = Int16.Parse("11"),       //CAST('11' AS INTEGER)
                Long_Parse   = long.Parse("2"),         //CAST('2' AS INTEGER)
                Double_Parse = double.Parse("3.1"),     //CAST('3.1' AS REAL)
                Float_Parse  = float.Parse("4.1"),      //CAST('4.1' AS REAL)
                //Decimal_Parse = decimal.Parse("5"),//不支持
                //Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//不支持 'D544BC4C-739E-4CD3-A3D3-7BF803FCE179'

                Bool_Parse     = bool.Parse("1"),              //CAST('1' AS INTEGER)
                DateTime_Parse = DateTime.Parse("2014-01-01"), //DATETIME('2014-01-01')
            }).ToList();

            ConsoleHelper.WriteLineAndReadKey();
        }
Exemplo n.º 4
0
        public static void MethodTest()
        {
            MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);

            var q = context.Query <User>();

            var      space     = new char[] { ' ' };
            DateTime startTime = DateTime.Now;
            DateTime endTime   = DateTime.Now.AddDays(1);
            var      xxxx      = q.Select(a => new
            {
                Id = a.Id,

                String_Length = a.Name.Length,
                Substring     = a.Name.Substring(0),
                Substring1    = a.Name.Substring(1),
                Substring1_2  = a.Name.Substring(1, 2),
                ToLower       = a.Name.ToLower(),
                ToUpper       = a.Name.ToUpper(),
                IsNullOrEmpty = string.IsNullOrEmpty(a.Name),
                Contains      = (bool?)a.Name.Contains("s"),
                Trim          = a.Name.Trim(),
                TrimStart     = a.Name.TrimStart(space),
                TrimEnd       = a.Name.TrimEnd(space),
                StartsWith    = (bool?)a.Name.StartsWith("s"),
                EndsWith      = (bool?)a.Name.EndsWith("s"),

                DiffYears        = DbFunctions.DiffYears(startTime, endTime),
                DiffMonths       = DbFunctions.DiffMonths(startTime, endTime),
                DiffDays         = DbFunctions.DiffDays(startTime, endTime),
                DiffHours        = DbFunctions.DiffHours(startTime, endTime),
                DiffMinutes      = DbFunctions.DiffMinutes(startTime, endTime),
                DiffSeconds      = DbFunctions.DiffSeconds(startTime, endTime),
                DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime),
                //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//Exception

                /* No longer support method 'DateTime.Subtract(DateTime d)', instead of using 'DbFunctions.DiffXX' */
                //SubtractTotalDays = endTime.Subtract(startTime).TotalDays,
                //SubtractTotalHours = endTime.Subtract(startTime).TotalHours,
                //SubtractTotalMinutes = endTime.Subtract(startTime).TotalMinutes,
                //SubtractTotalSeconds = endTime.Subtract(startTime).TotalSeconds,
                //SubtractTotalMilliseconds = endTime.Subtract(startTime).TotalMilliseconds,

                AddYears        = startTime.AddYears(1),            //DATEADD(YEAR,1,@P_0)
                AddMonths       = startTime.AddMonths(1),           //DATEADD(MONTH,1,@P_0)
                AddDays         = startTime.AddDays(1),             //DATEADD(DAY,1,@P_0)
                AddHours        = startTime.AddHours(1),            //DATEADD(HOUR,1,@P_0)
                AddMinutes      = startTime.AddMinutes(2),          //DATEADD(MINUTE,2,@P_0)
                AddSeconds      = startTime.AddSeconds(120),        //DATEADD(SECOND,120,@P_0)
                AddMilliseconds = startTime.AddMilliseconds(20000), //DATEADD(MILLISECOND,20000,@P_0)

                Now         = DateTime.Now,
                UtcNow      = DateTime.UtcNow,
                Today       = DateTime.Today,
                Date        = DateTime.Now.Date,
                Year        = DateTime.Now.Year,
                Month       = DateTime.Now.Month,
                Day         = DateTime.Now.Day,
                Hour        = DateTime.Now.Hour,
                Minute      = DateTime.Now.Minute,
                Second      = DateTime.Now.Second,
                Millisecond = DateTime.Now.Millisecond,
                DayOfWeek   = DateTime.Now.DayOfWeek,

                Int_Parse    = int.Parse("1"),
                Int16_Parse  = Int16.Parse("11"),
                Long_Parse   = long.Parse("2"),
                Double_Parse = double.Parse("3"),
                Float_Parse  = float.Parse("4"),
                //Decimal_Parse = decimal.Parse("5"),//'Decimal.Parse(string s)' is not supported now,because we don't know the precision and scale information.
                Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),

                Bool_Parse     = bool.Parse("1"),
                DateTime_Parse = DateTime.Parse("2014-1-1"),

                B = a.Age == null ? false : a.Age > 1,
            }).ToList();

            ConsoleHelper.WriteLineAndReadKey();
        }