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 result = q.Select(a => new
{
Id = a.Id,
CustomFunction = DbFunctions.MyFunction(a.Id), //自定义函数
String_Length = (int?)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"), //
Replace = a.Name.Replace("l", "L"),
DateTimeSubtract = endTime.Subtract(startTime),
/* pgsql does not support Sql.DiffXX methods. */
//DiffYears = Sql.DiffYears(startTime, endTime),//DATEDIFF(YEAR,@P_0,@P_1)
//DiffMonths = Sql.DiffMonths(startTime, endTime),//DATEDIFF(MONTH,@P_0,@P_1)
//DiffDays = Sql.DiffDays(startTime, endTime),//DATEDIFF(DAY,@P_0,@P_1)
//DiffHours = Sql.DiffHours(startTime, endTime),//DATEDIFF(HOUR,@P_0,@P_1)
//DiffMinutes = Sql.DiffMinutes(startTime, endTime),//DATEDIFF(MINUTE,@P_0,@P_1)
//DiffSeconds = Sql.DiffSeconds(startTime, endTime),//DATEDIFF(SECOND,@P_0,@P_1)
//DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),//DATEDIFF(MILLISECOND,@P_0,@P_1)
//DiffMicroseconds = Sql.DiffMicroseconds(startTime, endTime),//DATEDIFF(MICROSECOND,@P_0,@P_1) Exception
AddYears = startTime.AddYears(1), //
AddMonths = startTime.AddMonths(1), //
AddDays = startTime.AddDays(1), //
AddHours = startTime.AddHours(1), //
AddMinutes = startTime.AddMinutes(2), //
AddSeconds = startTime.AddSeconds(120), //
AddMilliseconds = startTime.AddMilliseconds(20000), //
Now = DateTime.Now, //NOW()
//UtcNow = DateTime.UtcNow,//GETUTCDATE()
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("32"), //
Int16_Parse = Int16.Parse("16"), //
Long_Parse = long.Parse("64"), //
Double_Parse = double.Parse("3.123"), //
Float_Parse = float.Parse("4.123"), //
Decimal_Parse = decimal.Parse("5.123"), //
//Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//
Bool_Parse = bool.Parse("1"), //
DateTime_Parse = DateTime.Parse("1992-1-16"), //
B = a.Age == null ? false : a.Age > 1,
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
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,
CustomFunction = DbFunctions.MyFunction(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"), //
Replace = a.Name.Replace("l", "L"),
DiffYears = Sql.DiffYears(startTime, endTime), //DATEDIFF(YEAR,@P_0,@P_1)
DiffMonths = Sql.DiffMonths(startTime, endTime), //DATEDIFF(MONTH,@P_0,@P_1)
DiffDays = Sql.DiffDays(startTime, endTime), //DATEDIFF(DAY,@P_0,@P_1)
DiffHours = Sql.DiffHours(startTime, endTime), //DATEDIFF(HOUR,@P_0,@P_1)
DiffMinutes = Sql.DiffMinutes(startTime, endTime), //DATEDIFF(MINUTE,@P_0,@P_1)
DiffSeconds = Sql.DiffSeconds(startTime, endTime), //DATEDIFF(SECOND,@P_0,@P_1)
DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime), //DATEDIFF(MILLISECOND,@P_0,@P_1)
//DiffMicroseconds = Sql.DiffMicroseconds(startTime, endTime),//DATEDIFF(MICROSECOND,@P_0,@P_1) Exception
/* No longer support method 'DateTime.Subtract(DateTime d)', instead of using 'Sql.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();
}
public static void Method()
{
IQuery <User> q = context.Query <User>();
var space = new char[] { ' ' };
DateTime startTime = DateTime.Now;
DateTime endTime = startTime.AddDays(1);
var ret = q.Select(a => new
{
Id = a.Id,
CustomFunction = DbFunctions.MyFunction(a.Id), //自定义函数
String_Length = (int?)a.Name.Length, //LENGTH("USERS"."NAME")
Substring = a.Name.Substring(0), //SUBSTR("USERS"."NAME",0 + 1,LENGTH("USERS"."NAME"))
Substring1 = a.Name.Substring(1), //SUBSTR("USERS"."NAME",1 + 1,LENGTH("USERS"."NAME"))
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), //too long
Contains = (bool?)a.Name.Contains("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"), //
EndsWith = (bool?)a.Name.EndsWith("s"), //
Replace = a.Name.Replace("l", "L"),
/* oracle is not supported DbFunctions.Diffxx. */
//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),//
/* ((CAST(:P_0 AS DATE)-CAST(:P_1 AS DATE)) * 86400000 + CAST(TO_CHAR(CAST(:P_0 AS TIMESTAMP),'ff3') AS NUMBER) - CAST(TO_CHAR(CAST(:P_1 AS TIMESTAMP),'ff3') AS NUMBER)) / 86400000 */
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), //ADD_MONTHS(:P_0,12 * 1)
AddMonths = startTime.AddMonths(1), //ADD_MONTHS(:P_0,1)
AddDays = startTime.AddDays(1), //(:P_0 + 1)
AddHours = startTime.AddHours(1), //(:P_0 + NUMTODSINTERVAL(1,'HOUR'))
AddMinutes = startTime.AddMinutes(2), //(:P_0 + NUMTODSINTERVAL(2,'MINUTE'))
AddSeconds = startTime.AddSeconds(120), //(:P_0 + NUMTODSINTERVAL(120,'SECOND'))
//AddMilliseconds = startTime.AddMilliseconds(20000),//不支持
Now = DateTime.Now, //SYSTIMESTAMP
UtcNow = DateTime.UtcNow, //SYS_EXTRACT_UTC(SYSTIMESTAMP)
Today = DateTime.Today, //TRUNC(SYSDATE,'DD')
Date = DateTime.Now.Date, //TRUNC(SYSTIMESTAMP,'DD')
Year = DateTime.Now.Year, //CAST(TO_CHAR(SYSTIMESTAMP,'yyyy') AS NUMBER)
Month = DateTime.Now.Month, //CAST(TO_CHAR(SYSTIMESTAMP,'mm') AS NUMBER)
Day = DateTime.Now.Day, //CAST(TO_CHAR(SYSTIMESTAMP,'dd') AS NUMBER)
Hour = DateTime.Now.Hour, //CAST(TO_CHAR(SYSTIMESTAMP,'hh24') AS NUMBER)
Minute = DateTime.Now.Minute, //CAST(TO_CHAR(SYSTIMESTAMP,'mi') AS NUMBER)
Second = DateTime.Now.Second, //CAST(TO_CHAR(SYSTIMESTAMP,'ss') AS NUMBER)
Millisecond = DateTime.Now.Millisecond, //CAST(TO_CHAR(SYSTIMESTAMP,'ff3') AS NUMBER)
DayOfWeek = DateTime.Now.DayOfWeek, //(CAST(TO_CHAR(SYSTIMESTAMP,'D') AS NUMBER) - 1)
Int_Parse = int.Parse("1"), //CAST(N'1' AS NUMBER)
Int16_Parse = Int16.Parse("11"), //CAST(N'11' AS NUMBER)
Long_Parse = long.Parse("2"), //CAST(N'2' AS NUMBER)
Double_Parse = double.Parse("3"), //CAST(N'3' AS BINARY_DOUBLE)
Float_Parse = float.Parse("4"), //CAST(N'4' AS BINARY_FLOAT)
Decimal_Parse = decimal.Parse("5"), //CAST(N'5' AS NUMBER)
//Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//不支持
Bool_Parse = bool.Parse("1"), //
DateTime_Parse = DateTime.Parse("1992-1-16"), //TO_TIMESTAMP(N'1992-1-16','yyyy-mm-dd hh24:mi:ssxff')
B = a.Age == null ? false : a.Age > 1,
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
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,
CustomFunction = DbFunctions.MyFunction(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')
Replace = a.Name.Replace("l", "L"),
DiffYears = Sql.DiffYears(startTime, endTime), //TIMESTAMPDIFF(YEAR,?P_0,?P_1)
DiffMonths = Sql.DiffMonths(startTime, endTime), //TIMESTAMPDIFF(MONTH,?P_0,?P_1)
DiffDays = Sql.DiffDays(startTime, endTime), //TIMESTAMPDIFF(DAY,?P_0,?P_1)
DiffHours = Sql.DiffHours(startTime, endTime), //TIMESTAMPDIFF(HOUR,?P_0,?P_1)
DiffMinutes = Sql.DiffMinutes(startTime, endTime), //TIMESTAMPDIFF(MINUTE,?P_0,?P_1)
DiffSeconds = Sql.DiffSeconds(startTime, endTime), //TIMESTAMPDIFF(SECOND,?P_0,?P_1)
//DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),//MySql 不支持 Millisecond
//DiffMicroseconds = Sql.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)
B = a.Age == null ? false : a.Age > 1, //三元表达式
CaseWhen = Case.When(a.Id > 100).Then(1).Else(0) //case when
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}