如何利用LINQ做到類似TSQL的Not Exists的效果

  • 11076
  • 0
  • LINQ
  • 2011-08-24

如何於LINQ to SQL中使用TSQL的Exists子句

假設有t1和t2兩個table(table schema如下),分別於t1和t2新增5筆資料(如下圖):

   1:  create table t1
   2:  (c1 int,c2 varchar(10))
   3:  go
   4:  create table t2
   5:  (c1 int,c2 varchar(10))
   6:  go

 

 

 

 

 

image

若要在LINQ中使用TSQL的Exists查出只在t1.c1存在但不在t2.c1中存在的資料,也就是下圖的結果,可以使用下列程式碼來完成:

image

   1:  using (DataClasses1DataContext context = new DataClasses1DataContext())
   2:  {
   3:      var query = from p in context.t1
   4:                  where !(from q in context.t2
   5:                          select q.c1).Contains(p.c1)
   6:                  select p;
   7:      foreach (var item in query)
   8:      {
   9:          Response.Write(string.Format("{0},{1}<br/>", item.c1, item.c2));
  10:      }                    
  11:  }

 

 

 

 

 

我們可以透過DataContext的GetCommand來取得CommandText(如下)。

   1:  SELECT [t0].[c1], [t0].[c2] FROM [dbo].[t1] AS [t0] 
   2:  WHERE NOT (EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[t2] AS [t1] WHERE [t1].[c1] = [t0].[c1] ))