SQLite-常用函数

常用函数

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

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 = Sql.DiffYears(startTime, endTime),//(CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER))
    DiffMonths = Sql.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 = Sql.DiffDays(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) AS INTEGER)
    DiffHours = Sql.DiffHours(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 24 AS INTEGER)
    DiffMinutes = Sql.DiffMinutes(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 1440 AS INTEGER)
    DiffSeconds = Sql.DiffSeconds(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 86400 AS INTEGER)
    //DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),//不支持 Millisecond
    //DiffMicroseconds = Sql.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();

上述的函数可以用于 Select 方法,亦可用在 Where 或其它方法的 lambda 表达式树中,最终都会翻译成数据库函数。