Oracle for Entity Framework - LIKE 的查詢操作方式

資料庫使用 MS SQL Server 在 Entity Framework（EF）中如果要操作「like」查詢時，可以使用「Contains」，

例如以下的 LINQ Query：

var query = this.Products
    .Where(x=>x.ProductName.Contains("CH"))
    .OrderBy(x=>x.ProductID);

轉出的 SQL Command：

SELECT 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[ProductName] LIKE N'%CH%'
ORDER BY [Extent1].[ProductID] ASC

而當資料庫使用 Oracle 時，LINQ Query 的操作還是使用「Contains」，但有些地方還是不同的。

 

---

EF + MS SQL Serer 下的 Like 查詢

剛才的 LINQ Query Exprression 中，要做 like 查詢操作的比對條件值，轉出的 SQL Command 在 MS SQL Ser ver 的執行是不管大小寫，只要條件相符的就會找出來，

但如果同樣的 LINQ Query Expression 所查詢的對象是在 Oracle 資料庫中的話就會不一樣了。

 

EF + Oracle 下的 Like 查詢

執行相同的 LINQ Query Expression，在 Oracle 的查詢結果是會不同的，

其實熟悉 Oracle 資料庫查詢的開發者都會知道，在 Oracle 中的查詢是要明確的區分大小寫，

對 Oracle 資料庫做 like 查詢而且不區分大小寫的 SQL Command，如下：

select * from "Products"
where UPPER("ProductName") like '%CH%'
order by "ProductID"

或是

select * from "Products"
where LOWER("ProductName") like '%ch%'
order by "ProductID"

查詢到的結果都會是：

 

那麼要如何下 LINQ Query Expression 來執行這種 LIKE 且不分大小寫的查詢呢？

var query = this.Products
    .Where(x=>x.ProductName.ToUpper().Contains("CH"))
    .OrderBy(x=>x.ProductID);

如上面的寫法，先對資料集合中的「ProductName」做轉為大寫的處理，然後再比對同樣也轉為大寫的關鍵字，

查詢結果：

這個查詢結果可以與上面在 Oracle 中使用 SQL Command 查詢的結果做個比對，結果是相同的。

再來可以觀察這個 LINQ Query Expression 所轉出來的 SQL Command：

SELECT 
"Extent1"."ProductID" AS "ProductID", 
"Extent1"."ProductName" AS "ProductName", 
"Extent1"."SupplierID" AS "SupplierID", 
"Extent1"."CategoryID" AS "CategoryID", 
"Extent1"."QuantityPerUnit" AS "QuantityPerUnit", 
"Extent1"."UnitPrice" AS "UnitPrice", 
"Extent1"."UnitsInStock" AS "UnitsInStock", 
"Extent1"."UnitsOnOrder" AS "UnitsOnOrder", 
"Extent1"."ReorderLevel" AS "ReorderLevel", 
"Extent1"."Discontinued" AS "Discontinued", 
"Extent1"."DiscontinuedDate" AS "DiscontinuedDate"
FROM "NORTHWIND_USER"."Products" "Extent1"
WHERE (UPPER("Extent1"."ProductName") LIKE '%CH%')
ORDER BY "Extent1"."ProductID" ASC

轉換的 SQL Command 中有關 LIKE 查詢部分的處理就跟我們之前在 Oracle 中所下的指令是相同的。

 

這邊要提醒一下，在專案中如果要使用這樣的查詢方式，對於關鍵字的大小寫處理，如果是放在 LINQ Query Expression 之中，如下所示：

var query = this.Products
    .Where(x=>x.ProductName.ToUpper().Contains("ch".ToUpper()))
    .OrderBy(x=>x.ProductID);

這樣的查詢所轉換出來的 SQL Command 如下：

SELECT 
"Extent1"."ProductID" AS "ProductID", 
"Extent1"."ProductName" AS "ProductName", 
"Extent1"."SupplierID" AS "SupplierID", 
"Extent1"."CategoryID" AS "CategoryID", 
"Extent1"."QuantityPerUnit" AS "QuantityPerUnit", 
"Extent1"."UnitPrice" AS "UnitPrice", 
"Extent1"."UnitsInStock" AS "UnitsInStock", 
"Extent1"."UnitsOnOrder" AS "UnitsOnOrder", 
"Extent1"."ReorderLevel" AS "ReorderLevel", 
"Extent1"."Discontinued" AS "Discontinued", 
"Extent1"."DiscontinuedDate" AS "DiscontinuedDate"
FROM "NORTHWIND_USER"."Products" "Extent1"
WHERE (( NVL(INSTR(UPPER("Extent1"."ProductName"), UPPER('ch')), 0) ) > 0)
ORDER BY "Extent1"."ProductID" ASC

可以看到原本使用 LIKE 查詢的地方就變成使用 INSTR 的方式來做查詢，在大量資料中使用 INSTR 代替 LIKE 查詢，效率會有所提昇，

不過必須要說明的是，所謂的效率提昇只限於欄位有做索引的情況下，如果沒有做索引，兩者的查詢效率是不會差太多的。

 

另外就是不一定要轉換為大寫來做 LIKE 查詢，也可以轉換為小寫來做 LIKE 查詢，不過要注意的就是 LIKE 前面用小寫轉換的話，那麼 LIKE 後面的關鍵字就一定要轉換為小寫，不然怎麼查就是找不到資料，

LINQ Query Expression

var query = this.Products
    .Where(x=>x.ProductName.ToLower().Contains("ch"))
    .OrderBy(x=>x.ProductID);

轉換的 SQL Command：

SELECT 
"Extent1"."ProductID" AS "ProductID", 
"Extent1"."ProductName" AS "ProductName", 
"Extent1"."SupplierID" AS "SupplierID", 
"Extent1"."CategoryID" AS "CategoryID", 
"Extent1"."QuantityPerUnit" AS "QuantityPerUnit", 
"Extent1"."UnitPrice" AS "UnitPrice", 
"Extent1"."UnitsInStock" AS "UnitsInStock", 
"Extent1"."UnitsOnOrder" AS "UnitsOnOrder", 
"Extent1"."ReorderLevel" AS "ReorderLevel", 
"Extent1"."Discontinued" AS "Discontinued", 
"Extent1"."DiscontinuedDate" AS "DiscontinuedDate"
FROM "NORTHWIND_USER"."Products" "Extent1"
WHERE (LOWER("Extent1"."ProductName") LIKE '%ch%')
ORDER BY "Extent1"."ProductID" ASC

 

如果說你不放心單一的大寫或是小寫 LIKE 查詢，也可以兩個都用上，

var query = this.Products
    .Where(x=>x.ProductName.ToUpper().Contains("CH") || x.ProductName.ToLower().Contains("ch"))
    .OrderBy(x=>x.ProductID);

SELECT 
"Extent1"."ProductID" AS "ProductID", 
"Extent1"."ProductName" AS "ProductName", 
"Extent1"."SupplierID" AS "SupplierID", 
"Extent1"."CategoryID" AS "CategoryID", 
"Extent1"."QuantityPerUnit" AS "QuantityPerUnit", 
"Extent1"."UnitPrice" AS "UnitPrice", 
"Extent1"."UnitsInStock" AS "UnitsInStock", 
"Extent1"."UnitsOnOrder" AS "UnitsOnOrder", 
"Extent1"."ReorderLevel" AS "ReorderLevel", 
"Extent1"."Discontinued" AS "Discontinued", 
"Extent1"."DiscontinuedDate" AS "DiscontinuedDate"
FROM "NORTHWIND_USER"."Products" "Extent1"
WHERE ((UPPER("Extent1"."ProductName") LIKE '%CH%') OR (LOWER("Extent1"."ProductName") LIKE '%ch%'))
ORDER BY "Extent1"."ProductID" ASC

 

---

小技巧：LINQPad 觀察 Oracle for EF 轉煥的 SQL Command

如果資料庫是使用 MS SQL Server 的話，在 LINQPad 是可以直接看到轉換的 SQL Command

但是如果資料庫是使用 Oracle 就沒辦法如此便利了，

其實可以在查詢的文字框中再加上一條程式碼，讓顯示結果的同時也可以輸出轉換的 SQL Command，

var traceQuery = ((System.Data.Objects.ObjectQuery)query).ToTraceString();
traceQuery.Dump();

執行結果如下：

 

---

這一篇提供使用 Oracle for EF 查詢時使用 LIKE 的查詢操作方式，

另外也提供在 LINQPad 觀察 SQL Command 的小技巧，希望能夠對大家有些幫助。

 

以上