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"=SYSTIMESTAMP 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"=SYSTIMESTAMP 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'; Int32 :P_1 = 1; Int32 :P_2 = 28; Nullable<Int32> :P_3 = NULL; DateTime :P_4 = '2016/9/5 9:20:07'; UPDATE "USERS" SET "NAME"=:P_0,"GENDER"=:P_1,"AGE"=:P_2,"CITYID"=:P_3,"OPTIME"=:P_4 WHERE "USERS"."ID" = :P_1 */ /* * 支持只更新属性值已变的属性 */ 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 */

以 lambda 表达式树的方式删除：

context.Delete<User>(a => a.Id == 1); /* * DELETE FROM "USERS" WHERE "USERS"."ID" = 1 */ //批量删除 //删除所有不男不女的用户 context.Delete<User>(a => a.Gender == null); /* * DELETE FROM "USERS" WHERE "USERS"."GENDER" IS NULL */

以实体的方式删除：

User user = new User(); user.Id = 1; context.Delete(user); /* * Int32 :P_0 = 1; DELETE FROM "USERS" WHERE "USERS"."ID" = :P_0 */

通过存储过程获取一个 User 信息：

Oracle 数据库中，如果一个存储过程需要返回结果集，需要借助 RefCursor output 参数特性。用法如下：

/* 必须先自定义 RefCursor 参数 */ OracleParameter p_cur = new OracleParameter(); p_cur.ParameterName = "p_cur"; p_cur.OracleDbType = OracleDbType.RefCursor; p_cur.Direction = ParameterDirection.Output; DbParam refCursorParam = new DbParam(); /* 将自定义 RefCursor 参数设置到 DbParam 的 ExplicitParameter 属性 */ refCursorParam.ExplicitParameter = p_cur; DbParam id = new DbParam("id", 1); User user = context.SqlQuery<User>("Proc_GetUser", CommandType.StoredProcedure, id，refCursorParam).FirstOrDefault();

通过存储过程的 output 参数获取一个用户的 name：

DbParam id = new DbParam("id", 1); DbParam outputName = new DbParam("name", null, typeof(string)) { Direction = ParamDirection.Output }; context.Session.ExecuteNonQuery("Proc_GetUserName", CommandType.StoredProcedure, id, outputName);

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 = (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();