目錄
1 esql的查詢結果集 ObjectQuery 1
1.1 ObjectQuery<實體> 1
1.2 ObjectQuery<DbDataRecord> 1
1.3 ObjectQuery<簡單類型> 1
2 esql的使用 2
2.1 it關鍵字 2
2.2 value 關鍵字 2
2.3 查詢參數的使用 3
2.4 中文字段 3
3 得到esql與sql字串 3
3.1 CommandText屬性 4
3.2 ToTraceString方法 4
4 ObjectQuery的Linq方法 4
4.1 Where 4
4.2 OrderBy 4
4.3 Select 4
4.4 SelectValue(projection) 4
4.5 Top(count) 5
4.6 Skip(keys,count) 5
4.7 分頁 Skip Top 5
4.8 GroupBy(keys,projection) 6
4.9 Include(path) 6
5 esql注釋,成員訪問,分行 6
6 esql運算符 7
6.1 算術運算符 7
6.2 比效運算符 7
6.3 邏輯運算符 7
6.4 區間 7
6.5 集合運算 8
7 esql函數 10
7.1 統計類 10
7.1.1 聯合使用 11
7.1.2 與group by一起使用 11
7.2 數學類 11
7.3 日期 11
7.4 字符 12
8 esql語句 12
8.1 查詢語句 12
8.2 CASE語句 14
9 esql 類型 14
9.1 簡單類型 14
9.2 REF 16
9.3 ROW 16
9.4 集合 16
9.5 Object 返回對像 17
9.6 CAST 類型轉換 17
9.7 OFTYPE 18
9.8 TREAT 18
9.9 IS 類型判斷 19
10 esql Namespace 19
10.1 使用SqlServer函數 19
10.2 使用NET的數據類型 20
11 esql關系,導航 20
11.1 KEY 20
比起 LINQ to SQL，EF 除了提供 LINQ 查詢方式, 還提供了 Entity SQL language
ESQL 類似 Hibernate 的 HSQL，ESQL 與SQL 語言的語法相似，以字符串的方式執行
esql的查詢結果集 ObjectQuery ObjectQuery<實體>
myContext context = new myContext();
string esql = "SELECT VALUE DBItemList FROM myContext.DBItemList";
// ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context);
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine(r.NameID);
}
myContext context = new myContext();
string esql = "SELECT VALUE it FROM myContext.DBItemList as it";
// ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context);
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine(r.NameID);
}
ObjectQuery<DbDataRecord>
myContext context = new myContext();
string esql = "SELECT it.NameID FROM myContext.DBItemList as it";
//ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(esql, context);
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);
foreach (DbDataRecord r in query)
{
Console.WriteLine(r["NameID"].ToString());
}
ObjectQuery<簡單類型>
myContext context = new myContext();
string esql = "SELECT value count(it.NameID) FROM myContext.DBItemList as it";
// ObjectQuery<int> query = new ObjectQuery<int>(esql, context);
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}
myContext context = new myContext();
string esql = "SELECT value it.NameID FROM myContext.DBItemList as it";
// ObjectQuery<int> query = new ObjectQuery<int>(esql, context);
ObjectQuery<string> query = context.CreateQuery<string>(esql);
foreach (string n in query)
{
Console.WriteLine(n);
}
esql的使用
可以在
ObjectQuery的Linq方法,
構造ObjectQuery,
context.CreateQuery返方法,
中使用esql,並得到返回的榄查詢結果ObjectQuery
it關鍵字
[it] 出現在 ESQL 中, 由 ObjectQuery<T>.Name 屬性設定，用於標示源查詢對象(ObjectQuery)的名稱，
類似於 "SELECT * FROM Tab as it WHERE it.ItemValue =14" 。
可以將這個默認值 "it" 改成其他字符串。
myContext context = new myContext();
context.DBItemList.Name = "wxd";
ObjectQuery<DBItemList> list = context.DBItemList.Where("wxd.ItemValue=5");
myContext context = new myContext();
var sql = "SELECT VALUE DBItemList FROM myContext.DBItemList";
var query = new ObjectQuery<DBItemList>(sql, context);
query.Name = "wxd";
ObjectQuery<DBItemList> list = query.Where("wxd.ItemValue=@v", new ObjectParameter("v", 5));
value 關鍵字
value 後只能返回一個成員
myContext context = new myContext();
string esql = "SELECT value AVG(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}
/* print:
3
*/
string esql = "select value it.ItemID from myContext.DBItemList as it";
ObjectQuery<string> query = context.CreateQuery<string>(esql);
foreach (string r in query)
{
Console.WriteLine(r);
}
myContext context = new myContext();
string esql = "select value row( it.ItemValue ,it.NameID,'wxd' as wxwinter) from myContext.DBItemList as it";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);
foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1},{2}", r["ItemValue"], r["NameID"], r["wxwinter"]);
}
查詢參數的使用
myContext context = new myContext();
string esql = "SELECT VALUE it FROM myContext.DBItemList as it where it.ItemValue=@v1 or it.NameID=@v2";
ObjectParameter v1 = new ObjectParameter("v1", 3);
ObjectParameter v2 = new ObjectParameter("v2", "n01");
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql,v1,v2);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}",r.NameID,r.ItemValue);
}
中文字段
使用[]將字段括起來
myContext context = new myContext();
ObjectQuery<typeTest> query = context.typeTest.Where("it.值 ==22.22");
System.Console.WriteLine(query.CommandText);
foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}", r.a, r.b, r.c, r.d, r.e, r.值);
}
myContext context = new myContext();
ObjectQuery<typeTest> query = context.typeTest.Where("it.[值] ==22.22");
System.Console.WriteLine(query.CommandText);
foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}", r.a, r.b, r.c, r.d, r.e, r.值);
}
得到esql與sql字串
myContext context = new myContext();
string esql = "SELECT VALUE it FROM myContext.DBItemList as it";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
Console.WriteLine(query.CommandText);
Console.WriteLine(query.ToTraceString())
SELECT VALUE it FROM myContext.DBItemList as it
SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
CommandText屬性
得到esql字串
ToTraceString方法
得到sql字串
ObjectQuery的Linq方法 Where
用字符串為條件進行查詢
ObjectQuery<T> Where(string predicate, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<DBItemList> list = context.DBItemList.Where("(it.ItemValue=5 or it .ItemValue=5) and it.NameID='n01'");
SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
WHERE (([Extent1].[ItemValue] = 5) OR ([Extent1].[ItemValue] = 5)) AND ([Extent1].[NameID] = 'n01')
OrderBy
排序
ObjectQuery<T> OrderBy(string keys, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.OrderBy("it.ItemValue,it.ItemID desc");
foreach (var r in query)
{
Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
}
SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC
Select
射影
ObjectQuery<DbDataRecord> Select(string projection, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<DbDataRecord> list = context.DBItemList.Select(" it.ItemValue as a,it.NameID ");
SELECT
1 AS [C1],
[Extent1].[ItemValue] AS [ItemValue],
[Extent1].[NameID] AS [NameID]
FROM [dbo].[DBItemList] AS [Extent1]
SelectValue(projection)
返回只有一組字段的數組
ObjectQuery<TResultType> SelectValue<TResultType>(string projection, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<int> query = context.DBItemList.SelectValue<int>("it.ItemValue + it.AutoID");
foreach (var r in query)
{
Console.WriteLine(r);
}
SELECT
[Extent1].[ItemValue] + [Extent1].[AutoId] AS [C1]
FROM [dbo].[DBItemList] AS [Extent1]
Top(count)
集合的前n個元素
count : 前n個元素
ObjectQuery<T> Top(string count, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.Top("3"); ;
foreach (var r in query)
{
Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
}
SELECT TOP (3)
[c].[AutoId] AS [AutoId],
[c].[NameID] AS [NameID],
[c].[ItemID] AS [ItemID],
[c].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [c]
Skip(keys,count)
跳過集合的前n個元素,
keys : 用於排序的字段
count : 要跳過的記錄個數
ObjectQuery<T> Skip(string keys, string count, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5");
foreach (var r in query)
{
Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
}
SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number]
FROM [dbo].[DBItemList] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 5
ORDER BY [Extent1].[ItemValue] ASC
分頁 Skip Top
Skip與Top一起使用
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5").Top("3"); ;
foreach (var r in query)
{
Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
}
SELECT TOP (3)
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number]
FROM [dbo].[DBItemList] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 5
ORDER BY [Extent1].[ItemValue] ASC
GroupBy(keys,projection)
分組
keys: GROUP BY的字段
projection : Select 的內容
ObjectQuery<DbDataRecord> GroupBy(string keys, string projection, params ObjectParameter[] parameters);
myContext context = new myContext();
ObjectQuery<DbDataRecord> query = context.DBItemList.GroupBy("it.ItemID", "it.ItemID,Sum(it.ItemValue) as ValueSum");
foreach (var r in query)
{
Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]);
}
/*
a,23
b,8
c,23
*/
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [ItemID],
[GroupBy1].[A1] AS [C2]
FROM ( SELECT
[Extent1].[ItemID] AS [K1],
SUM([Extent1].[ItemValue]) AS [A1]
FROM [dbo].[DBItemList] AS [Extent1]
GROUP BY [Extent1].[ItemID]
) AS [GroupBy1]
SELECT it.ItemID,Sum(it.ItemValue) as ValueSum
FROM ( [DBItemList] ) AS it
GROUP BY it.ItemID
Include(path)
加載關聯數據,參數為實體的[導航屬性]的字串,調用Include("導航屬性")後,關聯數據會加載,這樣就不用在[實體.導航屬性]上調用Load()方法
ObjectQuery<T> Include(string path);
myContext context = new myContext();
var r = context.DBItem.Include("DBItemList");
foreach (var dbitem in r)
{
foreach (var dbitemlist in dbitem.DBItemList)
{
Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue);
}
}
效果與下例相同
myContext context = new myContext();
var r = context.DBItem;
foreach (var dbitem in r)
{
dbitem.DBItemList.Load();
foreach (var dbitemlist in dbitem.DBItemList)
{
Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue);
}
}
esql注釋,成員訪問,分行
注釋 -- 成員訪問 . 分行 ;
esql運算符 算術運算符
加 + 減 - 乘 * 除 / 模 % 負 -
比效運算符
等於 = 大於 > 大於等於 >= 空判斷 IS NOT NULL
IS NULL
小於 < 小天等於 <= 不等於 !=<>
字符比效
LIKE ''NOT LIKE ''
% :_ :
[ ] :
[^] :
邏輯運算符
與 AND
&&
非 NOT!
或 OR||
區間
BETWEEN
之間
BETWEEN n AND m
NOT BETWEEN n AND m
myContext context = new myContext();
string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue BETWEEN 2 and 4";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.Where(" it.ItemValue not BETWEEN 2 and 4");
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
IN
在集合中
IN {v,v}
NOT IN{v,v}
myContext context = new myContext();
string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue IN {1,2,3}";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.Where("it.ItemValue not IN {1,2,3}");
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
EXISTS
存在
EXISTS(select from)
NOT EXISTS(select from)
myContext context = new myContext();
string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
myContext context = new myContext();
ObjectQuery<DBItemList> query = context.DBItemList.Where("exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )");
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
SELECT VALUE it FROM ( [DBItemList] ) AS it
ORDER BY it.ItemValue
SKIP 5
LIMIT 3
集合運算
Union
(合集)
連接不同集合
UNION --自動過濾相同項
UNION ALL --兩個集合的相同項都會返回
myContext context = new myContext();
string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) UNION (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
(左並集)
從集合中刪除其與另一個集合中相同的項
myContext context = new myContext();
string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) EXCEPT (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
（交集）
獲取不同集合的相同項
myContext context = new myContext();
string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) INTERSECT (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
ANYELEMENT
集合中的第一個
myContext context = new myContext();
string esql = "ANYELEMENT(select value it from myContext.DBItemList as it where it.ItemID == 'a') ";
ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);
//如果集合中有兩個以上,只返回第一個到集合中
foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}
SELECT
[Element1].[AutoId] AS [AutoId],
[Element1].[NameID] AS [NameID],
[Element1].[ItemID] AS [ItemID],
[Element1].[ItemValue] AS [ItemValue]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT TOP (1)
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
WHERE [Extent1].[ItemID] = 'a' ) AS [Element1] ON 1 = 1
OVERLAPS
兩個集合是否有相交部份
myContext context = new myContext();
string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID=='b' ) OVERLAPS (select value it from myContext.DBItemList as it where it.ItemID == 'a' || it.ItemID=='b')";
ObjectQuery<bool> query = context.CreateQuery<bool>(esql);
foreach (bool r in query)
{
Console.WriteLine(r);
}
//print: True
SELECT
CASE WHEN ( EXISTS (SELECT
cast(1 as bit) AS [C1]
FROM (SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
WHERE ([Extent1].[ItemID] = 'c') OR ([Extent1].[ItemID] = 'b')
INTERSECT
SELECT
[Extent2].[AutoId] AS [AutoId],
[Extent2].[NameID] AS [NameID],
[Extent2].[ItemID] AS [ItemID],
[Extent2].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent2]
WHERE ([Extent2].[ItemID] = 'a') OR ([Extent2].[ItemID] = 'b')) AS [Intersect1]
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
cast(1 as bit) AS [C1]
FROM (SELECT
[Extent3].[AutoId] AS [AutoId],
[Extent3].[NameID] AS [NameID],
[Extent3].[ItemID] AS [ItemID],
[Extent3].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent3]
WHERE ([Extent3].[ItemID] = 'c') OR ([Extent3].[ItemID] = 'b')
INTERSECT
SELECT
[Extent4].[AutoId] AS [AutoId],
[Extent4].[NameID] AS [NameID],
[Extent4].[ItemID] AS [ItemID],
[Extent4].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent4]
WHERE ([Extent4].[ItemID] = 'a') OR ([Extent4].[ItemID] = 'b')) AS [Intersect2]
)) THEN cast(0 as bit) END AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
Set
去掉重復項
myContext context = new myContext();
