public override void Method()
{
IQuery <Person> q = this.DbContext.Query <Person>();
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, //三元表达式
CaseWhen = Case.When(a.Id > 100).Then(1).Else(0) //case when
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
/*复杂查询*/
public static void ComplexQuery()
{
/*
* 支持 select * from Users where CityId in (1,2,3) --in一个数组
* 支持 select * from Users where CityId in (select Id from City) --in子查询
* 支持 select * from Users exists (select 1 from City where City.Id=Users.CityId) --exists查询
* 支持 select (select top 1 CityName from City where Users.CityId==City.Id) as CityName, Users.Id, Users.Name from Users --select子查询
* 支持 select
* (select count(*) from Users where Users.CityId=City.Id) as UserCount, --总数
* (select max(Users.Age) from Users where Users.CityId=City.Id) as MaxAge, --最大年龄
* (select avg(Users.Age) from Users where Users.CityId=City.Id) as AvgAge --平均年龄
* from City
* --统计查询
*/
IQuery <User> userQuery = context.Query <User>();
IQuery <City> cityQuery = context.Query <City>();
List <User> users = null;
/* in 一个数组 */
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = userQuery.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
*
*/
/* in 子查询 */
users = userQuery.Where(a => cityQuery.Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
*
*/
/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = userQuery.Where(a => cityQuery.Where(c => c.Id == a.CityId).Any()).ToList();
/*
*
*/
/* select 子查询 */
var result = userQuery.Select(a => new
{
CityName = cityQuery.Where(c => c.Id == a.CityId).First().Name,
User = a
}).ToList();
/*
*
*/
/* 统计 */
var statisticsResult = cityQuery.Select(a => new
{
UserCount = userQuery.Where(u => u.CityId == a.Id).Count(),
MaxAge = userQuery.Where(u => u.CityId == a.Id).Max(c => c.Age),
AvgAge = userQuery.Where(u => u.CityId == a.Id).Average(c => c.Age),
}).ToList();
/*
*
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id == 1).FirstOrDefault();
/*
*
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
*
*/
//分页
q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
*
*/
/* like 查询 */
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
*
*/
/* in 一个数组 */
List <User> users = null;
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = q.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
*
*/
/* in 子查询 */
users = q.Where(a => context.Query <City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
*
*/
/* distinct 查询 */
q.Select(a => new { a.Name }).Distinct().ToList();
/*
*
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void Update()
{
context.Update <User>(a => a.Id == 1, a => new User()
{
Name = a.Name, Age = a.Age + 1, Gender = Gender.Man, OpTime = DateTime.Now
}, 1);
context.Update <User>(a => a.Id == 1, a => new User()
{
Name = a.Name, Age = a.Age + 1, Gender = Gender.Man, OpTime = DateTime.Now
});
/*
* UPDATE `Users` SET `Name`=`Users`.`Name`,`Age`=(`Users`.`Age` + 1),`Gender`=1,`OpTime`=NOW() WHERE `Users`.`Id` = 1
*/
//批量更新
//给所有女性年轻 1 岁
context.Update <User>(a => a.Gender == Gender.Woman, a => new User()
{
Age = a.Age - 1, OpTime = DateTime.Now
});
/*
* UPDATE `Users` SET `Age`=(`Users`.`Age` - 1),`OpTime`=NOW() WHERE `Users`.`Gender` = 2
*/
User user = new User();
user.Id = 1;
user.Name = "lu";
user.Age = 28;
user.Gender = Gender.Man;
user.OpTime = DateTime.Now;
context.Update(user); //会更新所有映射的字段
/*
* String ?P_0 = 'lu';
* Gender ?P_1 = Man;
* Int32 ?P_2 = 28;
* Nullable<Int32> ?P_3 = NULL;
* DateTime ?P_4 = '2016/8/26 18:18:36';
* Int32 ?P_5 = 1;
* UPDATE `Users` SET `Name`=?P_0,`Gender`=?P_1,`Age`=?P_2,`CityId`=?P_3,`OpTime`=?P_4 WHERE `Users`.`Id` = ?P_5
*/
/*
* 支持只更新属性值已变的属性
*/
context.TrackEntity(user); //在上下文中跟踪实体
user.Name = user.Name + "1";
context.Update(user); //这时只会更新被修改的字段
/*
* String ?P_0 = 'lu1';
* Int32 ?P_1 = 1;
* UPDATE `Users` SET `Name`=?P_0 WHERE `Users`.`Id` = ?P_1
*/
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();
}
/*复杂查询*/
public static void ComplexQuery()
{
/*
* 支持 select * from Users where CityId in (1,2,3) --in一个数组
* 支持 select * from Users where CityId in (select Id from City) --in子查询
* 支持 select * from Users exists (select 1 from City where City.Id=Users.CityId) --exists查询
* 支持 select (select top 1 CityName from City where Users.CityId==City.Id) as CityName, Users.Id, Users.Name from Users --select子查询
* 支持 select
* (select count(*) from Users where Users.CityId=City.Id) as UserCount, --总数
* (select max(Users.Age) from Users where Users.CityId=City.Id) as MaxAge, --最大年龄
* (select avg(Users.Age) from Users where Users.CityId=City.Id) as AvgAge --平均年龄
* from City
* --统计查询
*/
IQuery <User> userQuery = context.Query <User>();
IQuery <City> cityQuery = context.Query <City>();
List <User> users = null;
/* in 一个数组 */
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = userQuery.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
* WHERE `Users`.`Id` IN (1,2,3)
*/
/* in 子查询 */
users = userQuery.Where(a => cityQuery.Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
* WHERE `Users`.`CityId` IN (SELECT `City`.`Id` AS `C` FROM `City` AS `City`)
*/
/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = userQuery.Where(a => cityQuery.Where(c => c.Id == a.CityId).Any()).ToList();
/*
* SELECT
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
* WHERE Exists (SELECT N'1' AS `C` FROM `City` AS `City` WHERE `City`.`Id` = `Users`.`CityId`)
*/
/* select 子查询 */
var result = userQuery.Select(a => new
{
CityName = cityQuery.Where(c => c.Id == a.CityId).First().Name,
User = a
}).ToList();
/*
* SELECT
* (SELECT `City`.`Name` AS `C` FROM `City` AS `City` WHERE `City`.`Id` = `Users`.`CityId` LIMIT 0,1) AS `CityName`,
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
*/
/* 统计 */
var statisticsResult = cityQuery.Select(a => new
{
UserCount = userQuery.Where(u => u.CityId == a.Id).Count(),
MaxAge = userQuery.Where(u => u.CityId == a.Id).Max(c => c.Age),
AvgAge = userQuery.Where(u => u.CityId == a.Id).Average(c => c.Age),
}).ToList();
/*
* SELECT
* (SELECT COUNT(1) AS `C` FROM `Users` AS `Users` WHERE `Users`.`CityId` = `City`.`Id`) AS `UserCount`,
* (SELECT MAX(`Users`.`Age`) AS `C` FROM `Users` AS `Users` WHERE `Users`.`CityId` = `City`.`Id`) AS `MaxAge`,
* (SELECT AVG(`Users`.`Age`) AS `C` FROM `Users` AS `Users` WHERE `Users`.`CityId` = `City`.`Id`) AS `AvgAge`
* FROM `City` AS `City`
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id == 1).FirstOrDefault();
/*
* SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`,`Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime` FROM `Users` AS `Users` WHERE `Users`.`Id` = 1 LIMIT 0,1
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
* SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name` FROM `Users` AS `Users` WHERE `Users`.`Id` = 1 LIMIT 0,1
*/
//分页
q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
* SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`,`Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime` FROM `Users` AS `Users` WHERE `Users`.`Id` > 0 ORDER BY `Users`.`Age` ASC LIMIT 20,10
*/
/* like 查询 */
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
* SELECT
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
* WHERE (`Users`.`Name` LIKE CONCAT('%',N'so','%') OR `Users`.`Name` LIKE CONCAT(N's','%') OR `Users`.`Name` LIKE CONCAT('%',N'o'))
*/
/* in 一个数组 */
List <User> users = null;
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = q.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
* WHERE `Users`.`Id` IN (1,2,3)
*/
/* in 子查询 */
users = q.Where(a => context.Query <City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`
* FROM `Users` AS `Users`
* WHERE `Users`.`CityId` IN (SELECT `City`.`Id` AS `C` FROM `City` AS `City`)
*/
/* distinct 查询 */
q.Select(a => new { a.Name }).Distinct().ToList();
/*
* SELECT DISTINCT `Users`.`Name` AS `Name` FROM `Users` AS `Users`
*/
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,
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();
}
public override void Method()
{
IQuery <Person> q = this.DbContext.Query <Person>();
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("PERSON"."NAME")
Substring = a.Name.Substring(0), //SUBSTR("PERSON"."NAME",0 + 1,LENGTH("PERSON"."NAME"))
Substring1 = a.Name.Substring(1), //SUBSTR("PERSON"."NAME",1 + 1,LENGTH("PERSON"."NAME"))
Substring1_2 = a.Name.Substring(1, 2), //SUBSTR("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(), //TRIM("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"), //
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, //三元表达式
CaseWhen = Case.When(a.Id > 100).Then(1).Else(0) //case when
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id == 1).FirstOrDefault();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] = 1 LIMIT 1 OFFSET 0
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name] FROM [Users] AS [Users] WHERE [Users].[Id] = 1 LIMIT 1 OFFSET 0
*/
//分页
var result = q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 ORDER BY [Users].[Age] ASC LIMIT 10 OFFSET 20
*/
/* like 查询 */
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE ([Users].[Name] LIKE '%' || 'so' || '%' OR [Users].[Name] LIKE 's' || '%' OR [Users].[Name] LIKE '%' || 'o')
*/
/* in 一个数组 */
List <User> users = null;
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = q.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[Id] IN (1,2,3)
*/
/* in 子查询 */
users = q.Where(a => context.Query <City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
*/
/* distinct 查询 */
q.Select(a => new { a.Name }).Distinct().ToList();
/*
* SELECT DISTINCT [Users].[Name] AS [Name] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}
/*复杂查询*/
public static void ComplexQuery()
{
/*
* 支持 select * from Users where CityId in (1,2,3) --in一个数组
* 支持 select * from Users where CityId in (select Id from City) --in子查询
* 支持 select * from Users exists (select 1 from City where City.Id=Users.CityId) --exists查询
* 支持 select (select top 1 CityName from City where Users.CityId==City.Id) as CityName, Users.Id, Users.Name from Users --select子查询
* 支持 select
* (select count(*) from Users where Users.CityId=City.Id) as UserCount, --总数
* (select max(Users.Age) from Users where Users.CityId=City.Id) as MaxAge, --最大年龄
* (select avg(Users.Age) from Users where Users.CityId=City.Id) as AvgAge --平均年龄
* from City
* --统计查询
*/
IQuery <User> userQuery = context.Query <User>();
IQuery <City> cityQuery = context.Query <City>();
List <User> users = null;
/* in 一个数组 */
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = userQuery.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[Id] IN (1,2,3)
*/
/* in 子查询 */
users = userQuery.Where(a => cityQuery.Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
*/
/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = userQuery.Where(a => cityQuery.Where(c => c.Id == a.CityId).Any()).ToList();
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE Exists (SELECT '1' AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Users].[CityId])
*/
/* select 子查询 */
var result = userQuery.Select(a => new
{
CityName = cityQuery.Where(c => c.Id == a.CityId).First().Name,
User = a
}).ToList();
/*
* SELECT
* (SELECT [City].[Name] AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Users].[CityId] LIMIT 1 OFFSET 0) AS [CityName],
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
*/
/* 统计 */
var statisticsResult = cityQuery.Select(a => new
{
UserCount = userQuery.Where(u => u.CityId == a.Id).Count(),
MaxAge = userQuery.Where(u => u.CityId == a.Id).Max(c => c.Age),
AvgAge = userQuery.Where(u => u.CityId == a.Id).Average(c => c.Age),
}).ToList();
/*
* SELECT
* (SELECT COUNT(1) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [UserCount],
* (SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [MaxAge],
* (SELECT CAST(AVG([Users].[Age]) AS REAL) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [AvgAge]
* FROM [City] AS [City]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
var ccc = q.Select(a => Sql.NextValueForSequence <int>("USERS_AUTOID", null)).ToList();
var x = q.Where(a => a.Id >= GetUser().Id).ToList();
q.Where(a => a.Id == 1).FirstOrDefault();
/*
* SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME","USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME" FROM "USERS" "USERS" WHERE ("USERS"."ID" = 1 AND ROWNUM < 2)
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
* SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME" FROM "USERS" "USERS" WHERE ("USERS"."ID" = 1 AND ROWNUM < 2)
*/
//分页
q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
* SELECT "T"."ID" AS "ID","T"."NAME" AS "NAME","T"."GENDER" AS "GENDER","T"."AGE" AS "AGE","T"."CITYID" AS "CITYID","T"."OPTIME" AS "OPTIME" FROM (SELECT "TTAKE"."ID" AS "ID","TTAKE"."NAME" AS "NAME","TTAKE"."GENDER" AS "GENDER","TTAKE"."AGE" AS "AGE","TTAKE"."CITYID" AS "CITYID","TTAKE"."OPTIME" AS "OPTIME",ROWNUM AS "ROW_NUMBER_0" FROM (SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME","USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME" FROM "USERS" "USERS" WHERE "USERS"."ID" > 0 ORDER BY "USERS"."AGE" ASC) "TTAKE" WHERE ROWNUM < 31) "T" WHERE "T"."ROW_NUMBER_0" > 20
*/
/* like 查询 */
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
* SELECT
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
* WHERE ("USERS"."NAME" LIKE '%' || N'so' || '%' OR "USERS"."NAME" LIKE N's' || '%' OR "USERS"."NAME" LIKE '%' || N'o')
*/
/* in 一个数组 */
List <User> users = null;
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = q.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
* WHERE "USERS"."ID" IN (1,2,3)
*/
/* in 子查询 */
users = q.Where(a => context.Query <City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
* WHERE "USERS"."CITYID" IN (SELECT "CITY"."ID" AS "C" FROM "CITY" "CITY")
*/
/* distinct 查询 */
q.Select(a => new { a.Name }).Distinct().ToList();
/*
* SELECT DISTINCT "USERS"."NAME" AS "NAME" FROM "USERS" "USERS"
*/
ConsoleHelper.WriteLineAndReadKey();
}
/*复杂查询*/
public static void ComplexQuery()
{
/*
* 支持 select * from Users where CityId in (1,2,3) --in一个数组
* 支持 select * from Users where CityId in (select Id from City) --in子查询
* 支持 select * from Users exists (select 1 from City where City.Id=Users.CityId) --exists查询
* 支持 select (select top 1 CityName from City where Users.CityId==City.Id) as CityName, Users.Id, Users.Name from Users --select子查询
* 支持 select
* (select count(*) from Users where Users.CityId=City.Id) as UserCount, --总数
* (select max(Users.Age) from Users where Users.CityId=City.Id) as MaxAge, --最大年龄
* (select avg(Users.Age) from Users where Users.CityId=City.Id) as AvgAge --平均年龄
* from City
* --统计查询
*/
IQuery <User> userQuery = context.Query <User>();
IQuery <City> cityQuery = context.Query <City>();
List <User> users = null;
/* in 一个数组 */
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = userQuery.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
* WHERE "USERS"."ID" IN (1,2,3)
*/
/* in 子查询 */
users = userQuery.Where(a => cityQuery.Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
* WHERE "USERS"."CITYID" IN (SELECT "CITY"."ID" AS "C" FROM "CITY" "CITY")
*/
/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = userQuery.Where(a => cityQuery.Where(c => c.Id == a.CityId).Any()).ToList();
/*
* SELECT
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
* WHERE Exists (SELECT N'1' AS "C" FROM "CITY" "CITY" WHERE "CITY"."ID" = "USERS"."CITYID")
*/
/* select 子查询 */
var result = userQuery.Select(a => new
{
CityName = cityQuery.Where(c => c.Id == a.CityId).First().Name,
User = a
}).ToList();
/*
* SELECT
* (SELECT "CITY"."NAME" AS "C" FROM "CITY" "CITY" WHERE ("CITY"."ID" = "USERS"."CITYID" AND ROWNUM < 2)) AS "CITYNAME",
* "USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","USERS"."ID" AS "ID","USERS"."NAME" AS "NAME"
* FROM "USERS" "USERS"
*/
/* 统计 */
var statisticsResult = cityQuery.Select(a => new
{
UserCount = userQuery.Where(u => u.CityId == a.Id).Count(),
MaxAge = userQuery.Where(u => u.CityId == a.Id).Max(c => c.Age),
AvgAge = userQuery.Where(u => u.CityId == a.Id).Average(c => c.Age),
}).ToList();
/*
* SELECT
* (SELECT COUNT(1) AS "C" FROM "USERS" "USERS" WHERE "USERS"."CITYID" = "CITY"."ID") AS "USERCOUNT",
* (SELECT MAX("USERS"."AGE") AS "C" FROM "USERS" "USERS" WHERE "USERS"."CITYID" = "CITY"."ID") AS "MAXAGE",
* (SELECT AVG("USERS"."AGE") AS "C" FROM "USERS" "USERS" WHERE "USERS"."CITYID" = "CITY"."ID") AS "AVGAGE"
* FROM "CITY" "CITY"
*/
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, //三元表达式
CaseWhen = Case.When(a.Id > 100).Then(1).Else(0) //case when
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}