public static void GroupQuery()
{
IQuery <User> q = context.Query <User>();
IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
g = g.Having(a => a.Age > 1 && Sql.Count() > 0);
g.Select(a => new { a.Age, Count = Sql.Count(), Sum = Sql.Sum(a.Age), Max = Sql.Max(a.Age), Min = Sql.Min(a.Age), Avg = Sql.Average(a.Age) }).ToList();
/*
* SELECT `Users`.`Age` AS `Age`,COUNT(1) AS `Count`,CAST(SUM(`Users`.`Age`) AS SIGNED) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Avg` FROM `Users` AS `Users` WHERE `Users`.`Id` > 0 GROUP BY `Users`.`Age` HAVING (`Users`.`Age` > 1 AND COUNT(1) > 0)
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void GroupJion()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> users = context.Query <User>();
IQuery <City> cities = context.Query <City>();
var gq = users.GroupBy(a => a.CityId).Select(a => new { a.CityId, MinAge = Sql.Min(a.Age) });
cities.LeftJoin(gq, (city, g) => city.Id == g.CityId).Select((city, g) => new { City = city, MinAge = g.MinAge }).ToList();
/*
* SELECT [T].[MinAge] AS [MinAge],[City].[Id] AS [Id],[City].[Name] AS [Name],[City].[ProvinceId] AS [ProvinceId] FROM [City] AS [City] LEFT JOIN (SELECT [Users].[CityId] AS [CityId],MIN([Users].[Age]) AS [MinAge] FROM [Users] AS [Users] GROUP BY [Users].[CityId]) AS [T] ON [City].[Id] = [T].[CityId]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void JoinQuery()
{
var user_city_province = context.Query <User>()
.InnerJoin <City>((user, city) => user.CityId == city.Id)
.InnerJoin <Province>((user, city, province) => city.ProvinceId == province.Id);
//查出一个用户及其隶属的城市和省份的所有信息
var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province }).Where(a => a.User.Id > 1).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`,`City`.`Id` AS `Id0`,`City`.`Name` AS `Name0`,`City`.`ProvinceId` AS `ProvinceId`,`Province`.`Id` AS `Id1`,`Province`.`Name` AS `Name1` FROM `Users` AS `Users` INNER JOIN `City` AS `City` ON `Users`.`CityId` = `City`.`Id` INNER JOIN `Province` AS `Province` ON `City`.`ProvinceId` = `Province`.`Id` WHERE `Users`.`Id` > 1
*/
//也可以只获取指定的字段信息：UserId,UserName,CityName,ProvinceName
user_city_province.Select((user, city, province) => new { UserId = user.Id, UserName = user.Name, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId > 1).ToList();
/*
* SELECT `Users`.`Id` AS `UserId`,`Users`.`Name` AS `UserName`,`City`.`Name` AS `CityName`,`Province`.`Name` AS `ProvinceName` FROM `Users` AS `Users` INNER JOIN `City` AS `City` ON `Users`.`CityId` = `City`.`Id` INNER JOIN `Province` AS `Province` ON `City`.`ProvinceId` = `Province`.`Id` WHERE `Users`.`Id` > 1
*/
/* quick join and paging. */
context.JoinQuery <User, City>((user, city) => new object[]
{
JoinType.LeftJoin, user.CityId == city.Id
})
.Select((user, city) => new { User = user, City = city })
.Where(a => a.User.Id > -1)
.OrderByDesc(a => a.User.Age)
.TakePage(1, 20)
.ToList();
context.JoinQuery <User, City, Province>((user, city, province) => new object[]
{
JoinType.LeftJoin, user.CityId == city.Id, /* 表 User 和 City 进行Left连接 */
JoinType.LeftJoin, city.ProvinceId == province.Id /* 表 City 和 Province 进行Left连接 */
})
.Select((user, city, province) => new { User = user, City = city, Province = province }) /* 投影成匿名对象 */
.Where(a => a.User.Id > -1) /* 进行条件过滤 */
.OrderByDesc(a => a.User.Age) /* 排序 */
.TakePage(1, 20) /* 分页 */
.ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void DeleteTest1()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
context.Delete <User>(a => a.Id == 1);
/*
* DELETE [Users] WHERE [Users].[Id] = 1
*/
//批量删除
//删除所有不男不女的用户
context.Delete <User>(a => a.Gender == null);
/*
* DELETE [Users] WHERE [Users].[Gender] IS NULL
*/
User user = new User();
user.Id = 1;
context.Delete(user);
/*
* Int32 @P_0 = 1;
* DELETE [Users] WHERE [Users].[Id] = @P_0
*/
IDbSession dbSession = context.Session;
try
{
dbSession.BeginTransaction();
//to do somethings here...
dbSession.CommitTransaction();
}
catch
{
dbSession.RollbackTransaction();
}
ConsoleHelper.WriteLineAndReadKey(1);
}
public static void JoinQuery()
{
var user_city_province = context.Query <User>()
.InnerJoin <City>((user, city) => user.CityId == city.Id)
.InnerJoin <Province>((user, city, province) => city.ProvinceId == province.Id);
//查出一个用户及其隶属的城市和省份的所有信息
var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province }).Where(a => a.User.Id > 1).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","CITY"."ID" AS "ID0","CITY"."NAME" AS "NAME0","CITY"."PROVINCEID" AS "PROVINCEID","PROVINCE"."ID" AS "ID1","PROVINCE"."NAME" AS "NAME1" FROM "USERS" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "PROVINCE" ON "CITY"."PROVINCEID" = "PROVINCE"."ID" WHERE "USERS"."ID" > 1
*/
//也可以只获取指定的字段信息：UserId,UserName,CityName,ProvinceName
user_city_province.Select((user, city, province) => new { UserId = user.Id, UserName = user.Name, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId > 1).ToList();
/*
* SELECT "USERS"."ID" AS "USERID","USERS"."NAME" AS "USERNAME","CITY"."NAME" AS "CITYNAME","PROVINCE"."NAME" AS "PROVINCENAME" FROM "USERS" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "PROVINCE" ON "CITY"."PROVINCEID" = "PROVINCE"."ID" WHERE "USERS"."ID" > 1
*/
/* quick join and paging. */
context.JoinQuery <User, City>((user, city) => new object[]
{
JoinType.LeftJoin, user.CityId == city.Id
})
.Select((user, city) => new { User = user, City = city })
.Where(a => a.User.Id > -1)
.OrderByDesc(a => a.User.Age)
.TakePage(1, 20)
.ToList();
context.JoinQuery <User, City, Province>((user, city, province) => new object[]
{
JoinType.LeftJoin, user.CityId == city.Id, /* 表 User 和 City 进行Left连接 */
JoinType.LeftJoin, city.ProvinceId == province.Id /* 表 City 和 Province 进行Left连接 */
})
.Select((user, city, province) => new { User = user, City = city, Province = province }) /* 投影成匿名对象 */
.Where(a => a.User.Id > -1) /* 进行条件过滤 */
.OrderByDesc(a => a.User.Age) /* 排序 */
.TakePage(1, 20) /* 分页 */
.ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void Run()
{
Test();
//BasicQuery();
//JoinQuery();
//AggregateQuery();
//GroupQuery();
//ComplexQuery(); /* v2.18复杂查询 */
Insert();
Update();
Delete();
Method();
ExecuteCommandText();
DoWithTransaction();
DoWithTransactionEx();
ConsoleHelper.WriteLineAndReadKey();
}
public static void Test()
{
var q = context.Query <User>();
DateTime dt = DateTime.Now;
object result = null;
string name = "lu";
string name1 = "lu";
string nameNull = null;
string ageString = "18";
int id = 2;
long longId = 2;
Gender gender = Gender.Man;
//result = q.Where(a => a.OpTime != null).Select(a => a.OpTime.Value.AddHours(id)).ToList();
result = q.Where(a => a.OpTime != null).Select(a => new { dt.Subtract(a.OpTime.Value).TotalDays }).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void QTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id > 0).FirstOrDefault();
q.Where(a => a.Id > 0).ToList();
q.Where(a => a.Id > 0).OrderBy(a => a.Age).ToList();
q.Where(a => a.Id > 0).Take(999).OrderBy(a => a.Age).ToList();
//分页，避免生成的 sql 语句太长，占篇幅，只选取 Id 和 Name 两个字段
q.Where(a => a.Id > 0).OrderBy(a => a.Age).ThenByDesc(a => a.Id).Select(a => new { a.Id, a.Name }).Skip(1).Take(999).ToList();
/*
* SELECT TOP (999) [T].[Id] AS [Id],[T].[Name] AS [Name] FROM (SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],ROW_NUMBER() OVER(ORDER BY [Users].[Age] ASC,[Users].[Id] DESC) AS [ROW_NUMBER_0] FROM [Users] AS [Users] WHERE [Users].[Id] > 0) AS [T] WHERE [T].[ROW_NUMBER_0] > 1
*/
//如果需要多个条件的话
q.Where(a => a.Id > 0).Where(a => a.Name.Contains("lu")).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 AND [Users].[Name] LIKE '%' + N'lu' + '%')
*/
//选取指定字段
q.Select(a => new { a.Id, a.Name, a.Age }).ToList();
//或者
q.Select(a => new User()
{
Id = a.Id, Name = a.Name, Age = a.Age
}).ToList();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Age] AS [Age] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void InsertTest()
{
string name = "so88";
long longAge = 10;
int? age = 18;
name = null;
int r = -1;
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
User user = new User();
user.Name = "lu";
user.Age = 21;
user.Gender = Gender.Man;
user.CityId = 1;
user.OpTime = DateTime.Now;
user.Id = 100;
//var id = context.Insert<User>(() => new User() { Name = user.Name, NickName = user.Name, Age = user.Age, Gender = Gender.Man, OpTime = DateTime.Now });
////var users = context.Query<User>().Where(a => a.Name == null).ToList();
////user = context.Query<User>().Where(a => a.Id == (int)id).First();
//user.ByteArray = new byte[] { 1, 2, 3 };
//user.OpTime = DateTime.Now;
var user1 = context.Insert(user);
//返回主键 Id
int id = (int)context.Insert <User>(() => new User()
{
Name = user.Name, Age = user.Age, Gender = Gender.Man, CityId = 1, OpTime = DateTime.Now
});
ConsoleHelper.WriteLineAndReadKey();
}
public static void SqlQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
//Dictionary<string, object> dic = new Dictionary<string, object>();
//dic.Add("@p", "shuxin");
var users = context.SqlQuery <User>("select Id as id,Name as name,'asdsd' as Name,ByteArray from Users where Name=@name", DbParam.Create("@name", "lu11"));
try
{
var list = users.ToList();
ret = context.SqlQuery <int?>("select Id from Users").ToList();
}
catch
{
ConsoleHelper.WriteLineAndReadKey();
}
ConsoleHelper.WriteLineAndReadKey();
}
public static void Test()
{
object result = null;
List <int> ids = new List <int>()
{
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
};
var qt = context.Query <TestEntity>();
var q = context.Query <User>();
string name = "lu";
string name1 = "lu";
string nameNull = null;
string ageString = "18";
int id = 2;
long longId = 2;
result = q.Where(a => a.OpTime != null).Select(a => a.OpTime.Value.AddDays(id)).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void InsertTest1()
{
IDbContext context = new MsSqlContext(DbHelper.ConnectionString);
//返回主键 Id
int id = (int)context.Insert <User>(() => new User()
{
Name = "lu", Age = 18, Gender = Gender.Man, CityId = 1, OpTime = DateTime.Now
});
/*
* INSERT INTO [Users]([Name],[Age],[Gender],[CityId],[OpTime]) VALUES(N'lu',18,1,1,GETDATE());SELECT @@IDENTITY
*/
User user = new User();
user.Name = "lu";
//user.Age = 18;
user.Gender = Gender.Man;
user.CityId = 1;
user.OpTime = DateTime.Now;
//会自动将自增 Id 设置到 user 的 Id 属性上
user = context.Insert(user);
/*
* String @P_0 = "lu";
* Gender @P_1 = Man;
* Int32 @P_2 = 18;
* Int32 @P_3 = 1;
* DateTime @P_4 = "1992/1/16 0:00:00";
* INSERT INTO [Users]([Name],[Gender],[Age],[CityId],[OpTime]) VALUES(@P_0,@P_1,@P_2,@P_3,@P_4);SELECT @@IDENTITY
*/
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 CTest()
{
object result = null;
List <int> ids = new List <int>()
{
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
};
var q = context.Query <User>();
DateTime dt = DateTime.Now;
string name = "hu";
string name1 = "lu";
string nameNull = null;
string ageString = "18";
Int16 shortId = 2;
int id = 2;
long longId = 2;
Gender gender = Gender.Man;
User user = new User();
user.Name = "qinshuxin";
user.Age = 188;
user.CityId = 1;
user.OpTime = DateTime.Now;
//user.TimeSpan = TimeSpan.FromDays(2);
//context.Insert(user);
//result = q.ToList();
//result = q.Select(a => new
//{
// t = (TimeSpan?)(dt.Subtract(a.OpTime.Value)),
// t2 = (TimeSpan?)(dt.Subtract(dt.AddMinutes(-60)))
//}).ToList();
var reader = context.Session.ExecuteReader("SELECT DATE_PART('YEAR',NOW()) AS \"year\" FROM \"users\" AS \"users\"");
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine($"{reader.GetName(i)}, {reader.GetFieldType(i).Name}, {reader.GetDataTypeName(i)}");
Console.WriteLine(reader.GetInt32(i));
}
reader.Close();
TestEntity t = new TestEntity();
t.F_Byte = 1;
t.F_Int16 = 16;
t.F_Int32 = 32;
t.F_Int64 = 64;
t.F_Double = 1.2;
t.F_Float = 1.1f;
t.F_Decimal = 1.112m;
t.F_Bool = true;
t.F_DateTime = DateTime.Now;
t.F_String = "soooo";
t.F_Json = "{\"id\": 10,\"age\":18}";
context.Insert(t);
var list = context.Query <TestEntity>().OrderByDesc(a => a.Id).ToList();
t = list.First();
t.F_Bool = false;
//context.Delete(t);
ConsoleHelper.WriteLineAndReadKey();
}
public static void UpdateTest1()
{
object ret = null;
int r = -1;
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
var u = context.Query <User>().AsTracking().First(a => a.Id == 3);
context.Update <User>(a => a.Id == 1, a => new User()
{
Name = a.Name, Age = a.Age + 100, Gender = Gender.Man, OpTime = DateTime.Now
});
/*
* UPDATE [Users] SET [Name]=[Users].[Name],[Age]=([Users].[Age] + 100),[Gender]=1,[OpTime]=GETDATE() WHERE [Users].[Id] = 1
*/
//批量更新
//给所有女性年轻 10 岁
context.Update <User>(a => a.Gender == Gender.Woman, a => new User()
{
Age = a.Age - 10, OpTime = DateTime.Now
});
/*
* UPDATE [Users] SET [Age]=([Users].[Age] - 10),[OpTime]=GETDATE() 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/7/8 11:28:27";
* 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 MethodTest1()
{
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 = 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, //CURDATE()
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.1"), //N'3'
Float_Parse = float.Parse("4.1"), //N'4'
//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-01-01"), //CAST(N'2014-1-1' AS DATETIME)
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public static void MethodTest()
{
var q = context.Query <User>();
var space = new char[] { ' ' };
DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now.AddYears(1).AddMonths(1).AddDays(1);
var xxxx = q.Select(a => new
{
Id = 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"),
DiffYears = Sql.DiffYears(startTime, endTime),
DiffMonths = Sql.DiffMonths(startTime, endTime),
DiffDays = Sql.DiffDays(startTime, endTime),
DiffHours = Sql.DiffHours(startTime, endTime),
DiffMinutes = Sql.DiffMinutes(startTime, endTime),
DiffSeconds = Sql.DiffSeconds(startTime, endTime),
//DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),
//DiffMicroseconds = Sql.DiffMicroseconds(startTime, endTime),//ex
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"),
Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),
Bool_Parse = bool.Parse("1"),
DateTime_Parse = DateTime.Parse("2016-08-07"),
B = a.Age == null ? false : a.Age > 1,
}).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 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 = startTime.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,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"), //
/* oracle is not supported Sql.Diffxx. */
//DiffYears = Sql.DiffYears(startTime, endTime),//
//DiffMonths = Sql.DiffMonths(startTime, endTime),//
//DiffDays = Sql.DiffDays(startTime, endTime),//
//DiffHours = Sql.DiffHours(startTime, endTime),//
//DiffMinutes = Sql.DiffMinutes(startTime, endTime),//
//DiffSeconds = Sql.DiffSeconds(startTime, endTime),//
//DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),//
//DiffMicroseconds = Sql.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 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);
int i = 0;
string n = "";
//q = q.Where(a => a.Name == n);
//q = q.Where(a => !string.IsNullOrEmpty(a.Name));
//q = q.Where(a => bool.Parse(null));
//var xxx = q.Where(a => a.Name.Substring(0, 2).Length > 2).ToList();
var xxxx = q.Select(a => new
{
Id = 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"),
DiffYears = Sql.DiffYears(startTime, endTime),
DiffMonths = Sql.DiffMonths(startTime, endTime),
DiffDays = Sql.DiffDays(startTime, endTime),
DiffHours = Sql.DiffHours(startTime, endTime),
DiffMinutes = Sql.DiffMinutes(startTime, endTime),
DiffSeconds = Sql.DiffSeconds(startTime, endTime),
DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),
//DiffMicroseconds = Sql.DiffMicroseconds(startTime, endTime),//ex
//No longer support method 'DateTime.Subtract(DateTime d)', instead of using 'Sql.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,
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,
Byte_Parse = byte.Parse("1"),
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"),
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();
}
public static void Insert()
{
/* User 实体打了序列标签，会自动获取序列值。返回主键 Id */
int id = (int)context.Insert <User>(() => new User()
{
Name = "lu", Age = 18, Gender = Gender.Man, CityId = 1, OpTime = DateTime.Now
});
/*
* SELECT "USERS_AUTOID"."NEXTVAL" FROM "DUAL"
* Int32 :P_0 = 14;
* INSERT INTO "USERS"("NAME","AGE","GENDER","CITYID","OPTIME","ID") VALUES(N'lu',18,1,1,SYSTIMESTAMP,:P_0)
*/
context.Session.ExecuteNonQuery("delete from users where id=56744");
OracleConnection oracleConnection = new OracleConnection("Data Source=localhost/chloe;User ID=system;Password=sa;");
OracleCommand cmd = oracleConnection.CreateCommand();
//cmd.Parameters.Add(new OracleParameter("P_0", Gender.Man));
//cmd.Parameters.Add(new OracleParameter("P_1", 18) { OracleType = OracleType.Int32, DbType = System.Data.DbType.Int32, Direction = System.Data.ParameterDirection.Input });
//cmd.Parameters.Add(new OracleParameter("P_2", DateTime.Now));
cmd.Parameters.Add(new OracleParameter(":P_3", 56744));
//cmd.Parameters.Add(new OracleParameter("P_4", "lu"));
oracleConnection.Open();
cmd.CommandText = "delete from users where id=:P_3";
//cmd.CommandText = "INSERT INTO USERS(NAME,AGE,GENDER,CITYID,OPTIME,ID) VALUES(N'lu',18,1,1,SYSTIMESTAMP,56744)";
var xx = cmd.ExecuteNonQuery();
//Input Int32 :P_0 = 1;
//Input Int32 :P_1 = 18;
//Input DateTime :P_2 = '2017/10/18 23:29:48';
//Input Int32 :P_3 = 56744;
//Input String :P_4 = 'lu';
//INSERT INTO "USERS"("GENDER", "AGE", "CITYID", "OPTIME", "ID", "NAME") VALUES(:P_0,:P_1,:P_0,:P_2,:P_3,:P_4)
string sql = "INSERT INTO \"USERS\"(\"GENDER\", \"AGE\", \"CITYID\", \"OPTIME\", \"ID\", \"NAME\") VALUES(:P_0,:P_1,:P_0,:P_2,:P_3,:P_4)";
sql = "INSERT INTO \"USERS\"(\"GENDER\", \"AGE\", \"CITYID\", \"ID\", \"NAME\") VALUES(1,18,1,56744,'lu')";
DbParamList dbParamList = new DbParamList();
//dbParamList.Add("P_0", Gender.Man);
//dbParamList.Add("P_1", 18);
//dbParamList.Add("P_2", DateTime.Now);
//dbParamList.Add("P_3", 56744);
//dbParamList.Add("P_4", "lu");
context.Session.ExecuteNonQuery(sql, dbParamList.ToArray());
User user = new User();
user.Name = "lu";
user.Age = 18;
user.Gender = Gender.Man;
user.CityId = 1;
user.OpTime = DateTime.Now;
//会自动将自增 Id 设置到 user 的 Id 属性上
user = context.Insert(user);
/*
* SELECT "USERS_AUTOID"."NEXTVAL" FROM "DUAL"
* Int32 :P_0 = 15;
* String :P_1 = 'lu';
* Int32 :P_2 = 1;
* Int32 :P_3 = 18;
* DateTime :P_4 = '2016/9/5 9:16:59';
* INSERT INTO "USERS"("ID","NAME","GENDER","AGE","CITYID","OPTIME") VALUES(:P_0,:P_1,:P_2,:P_3,:P_2,:P_4)
*/
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" "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 MTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
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), //太长，不贴了
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"), //太长，略
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)
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)
Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"), //CAST(N'xxx' 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();
}
