Oracle-基本查询

基本查询

根据 Id 查询出一个 User 对象:

string connString = "Your connection string";
OracleContext context = new OracleContext(new OracleConnectionFactory(connString));
IQuery<User> q = context.Query<User>();

q.Where(a => a.Id == 1).FirstOrDefault();
/*
 *生成的 sql:
 * 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)
 */

like 查询:

//注:在lambda里使用常量不会参数化处理,使用时切记要将值以变量的形式传进lambda
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<int> ids = new List<int>() { 1, 2, 3 };
q.Where(a => ids.Contains(a.Id)).ToList();
/*
 * Int32 :P_0 = 1;
   Int32 :P_1 = 2;
   Int32 :P_2 = 3;
   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" IN (:P_0,:P_1,:P_2)
 */

/* in 子查询 */
/* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
users = q.Where(a => context.Query<City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).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"."CITYID" IN (SELECT "CITY"."ID" AS "C" FROM "CITY" "CITY")
 */

可以选取指定的字段,返回一个匿名类型:

q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
 * 只会生成包含 Id 和 Name 两个字段的 sql 语句:
 * 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).ThenBy(a => a.Id).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" "USERS" 
   WHERE "USERS"."ID" > 0 
   ORDER BY "USERS"."AGE" ASC,"USERS"."ID" ASC
 */

分页:

q.Where(a => a.Id > 0).OrderBy(a => a.Age).TakePage(1, 20).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 < 21) "T" WHERE "T"."ROW_NUMBER_0" > 0
 */

distinct 查询:

q.Select(a => new { a.Name }).Distinct().ToList();
/*
 * SELECT DISTINCT "USERS"."NAME" AS "NAME" FROM "USERS" "USERS"
 */

exists 查询:

/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = context.Query<User>().Where(a => context.Query<City>().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")
 */

更多用法请查看使用进阶