Methods

常用函数

Chloe 对很多数据库函数支持很丰富，具体用法如下：

注：以下只列举了 SqlServer 支持的函数，其它数据库可参考 github 上的 demo。 https://github.com/shuxinqin/Chloe/tree/master/src/ChloeDemo

IQuery<Person> q = context.Query<Person>();

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([Person].[Name])
    Substring = a.Name.Substring(0), //SUBSTRING([Person].[Name],0 + 1,LEN([Person].[Name]))
    Substring1 = a.Name.Substring(1), //SUBSTRING([Person].[Name],1 + 1,LEN([Person].[Name]))
    Substring1_2 = a.Name.Substring(1, 2), //SUBSTRING([Person].[Name],1 + 1,2)
    ToLower = a.Name.ToLower(), //LOWER([Person].[Name])
    ToUpper = a.Name.ToUpper(), //UPPER([Person].[Name])
    IsNullOrEmpty = string.IsNullOrEmpty(a.Name), //too long
    Contains = (bool?)a.Name.Contains("s"), //
    Trim = a.Name.Trim(),//RTRIM(LTRIM([Person].[Name]))
    TrimStart = a.Name.TrimStart(space),//LTRIM([Person].[Name])
    TrimEnd = a.Name.TrimEnd(space),//RTRIM([Person].[Name])
    StartsWith = (bool?)a.Name.StartsWith("s"), //
    EndsWith = (bool?)a.Name.EndsWith("s"), //

    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), //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("1949-10-01"), //CAST(N'1992-1-16' AS DATETIME) AS [DateTime_Parse]

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

上述的函数可以用于 Select 方法，亦可用在 Where 或其它方法的 lambda 表达式树中，最终都会翻译成数据库函数。如需扩展支持更多方法翻译，请参考 demo： https://github.com/shuxinqin/Chloe/blob/master/src/ChloeDemo/Program.cs#L64 。