LINQ to SQL 查詢小技巧

2010-06-24

19449
0
LINQ

LINQ to SQL 查詢小技巧

1. 字串 (string) null查詢

平常我們在使用LINQ to SQL的時候，大部份的型別碰到可為 null 時， DataContext 內會

建立相對應的 nullable 型別，例如：DateTime?、int? 等等，可是 string 本身就是可為 null

的型別，如果我們需要查詢哪幾筆資料是 null 的時候，應該要怎麼下條件呢？

SQL的下法通常是：
WHERE Mobil is null
而在程式裡面，平常的時候，我們都是用參數宣告要查詢的值，例如：
var list =  db.Members.Where(p=>p.Mobil == a);
可是實際上轉換成SQL查詢的時候，會變成：
DECLARE @p0 VarChar(1) = null
-- EndRegion
SELECT [t0].[ID], [t0].[ChName], [t0].[TEL], [t0].[Mobil], [t0].[Account], [t0].[Password]
FROM [Member] AS [t0]
WHERE [t0].[Mobil] = @p0
這個查詢結果並不是我們要的，但是在.NET並不支援 p.Mobil is null 的寫法，這時候如果

你要查詢 null 的資料，在 LINQ 要怎麼處理呢？其實直接使用 null 就可以了。
這時候的SQL查詢就會變成： FROM [Member] AS [t0] WHERE [t0].[Mobil] IS NULL 所以，如果你今天要查詢可能是空值，或是 null 的資料時，記得二個條件都要下哦。



2. Like 通配符(萬用字元% or 單一字元 _ ) 查詢


  通常我們查詢like的方式有幾種 “%abc%”、”%abc”、”abc%”、”a%b%c”；在 LINQ 

  裡面可以利用

  Contains(“abc”)   >> “%abc"%”

  StartWith(“abc”)  >> “abc%”

  EndWith(“abc”)   >> “%abc”

  會轉換成相對應的 SQL 查詢句，可是如果是”a%b%c”呢？如果直接用 Contains 查詢…

  
    

  

  可是實際查詢的SQL句…

  
    

DECLARE @p0 NVarChar(9) = '%a~%b~%c%'
-- EndRegion
SELECT [t0].[ID], [t0].[ChName], [t0].[TEL], [t0].[Mobil], [t0].[Account], [t0].[Password]
FROM [Member] AS [t0]
WHERE [t0].[ChName] LIKE @p0 ESCAPE '~'

  

  因為%是特殊字元，所以除了Contains本身會加的%以外，其它的會自動設定escape。這時

  候我們要利用 SQLMethods.Like (System.Data.Linq.SqlClient)

  
    

  

  查詢的SQL就會符合我們想要的結果了。

  
    

DECLARE @p0 NVarChar(5) = 'a%b%c'
-- EndRegion
SELECT [t0].[ID], [t0].[ChName], [t0].[TEL], [t0].[Mobil], [t0].[Account], [t0].[Password]
FROM [Member] AS [t0]
WHERE [t0].[ChName] LIKE @p0
  

  SQLMethods 除了 Like 以外，還有很多日期比較的函數可以用，不過只支援 MS SQL 哦。

  PS：如果要使用「_」來查詢，那就一定要使用SqlMethods啦～因為沒有內建的可以用！


3. Exists 查詢


  有時候我們想要查詢某筆資料是否有在其它 table 中出現過，例如：查詢哪些會員是否

  曾經預訂過餐廳；在 SQL 查詢的時候，可以利用 Exists ，可以提高查詢的效率：

  
FROM Member AS [t0]
WHERE EXISTS(
SELECT MemberID FROM Dinner AS [t1]
WHERE [t1].MemberID = [t0].ID
)

  但是在 LINQ 要怎麼下呢？我們可以利用 Any 來處理！

  


  

  
實際查詢的SQL句…

  
    

FROM [Member] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Dinner] AS [t1]
    WHERE [t1].[MemberID] = [t0].[ID]
    )

  

  如果需要額外指定條件，也可以從 Any 代入：

  
    

  

  實際查詢的SQL句…

  
DECLARE @p0 NVarChar(6) = 'shelly'
-- EndRegion
SELECT [t0].[ID], [t0].[ChName], [t0].[TEL], [t0].[Mobil], [t0].[Account], [t0].[Password]
FROM [Member] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Dinner] AS [t1]
    WHERE ([t0].[ChName] = @p0) AND ([t1].[MemberID] = [t0].[ID])
    )







4. 子 table 查詢


  假如今天我們需要知道名字中含有「Chen」的客人，曾經預訂過幾次餐廳，我們在 SQL 

  可能會用子 table 來協助查詢，例如：

   FROM Dinner 
WHERE MemberID IN (SELECT MemberID FROM Member WHERE ChName like '%Chen%')

  在LINQ，我們可以很簡單的利用他們的關連來查詢：

  


  

  
其實上面的SQL查詢實際上會轉換成…

  
DECLARE @p0 NVarChar(6) = '%chen%'
-- EndRegion
SELECT [t0].[DinnerID], [t0].[Title], [t0].[EventDate], [t0].[Description], [t0].[HostedBy], [t0].[ContactPhone], [t0].[Address], [t0].[Country], [t0].[Latitude], [t0].[Longitude], [t0].[MemberID]
FROM [Dinner] AS [t0]
INNER JOIN [Member] AS [t1] ON [t1].[ID] = [t0].[MemberID]
WHERE [t1].[ChName] LIKE @p0


  但是，如果今天這二張 table 並沒有關連的話，要怎麼查詢呢？從上面的轉換查詢，我們

  知道，其實這就是 join 的一種利用方式，所以我們在LINQ的時候也可以利用 join ！

  


  而實際查詢的SQL…

  
DECLARE @p0 NVarChar(6) = '%chen%'
-- EndRegion
SELECT [t0].[DinnerID], [t0].[Title], [t0].[EventDate], [t0].[Description], [t0].[HostedBy], [t0].[ContactPhone], [t0].[Address], [t0].[Country], [t0].[Latitude], [t0].[Longitude], [t0].[MemberID], [t1].[ID], [t1].[ChName], [t1].[TEL], [t1].[Mobil], [t1].[Account], [t1].[Password]
FROM [Dinner] AS [t0]
INNER JOIN [Member] AS [t1] ON [t0].[MemberID] = [t1].[ID]
WHERE [t1].[ChName] LIKE @p0


  

  


  
出來的結果其實是一樣的～


 

DotBlogs 的標籤：LINQ,C#