LINQ查詢，linq動態查詢

LINQ查詢，linq動態查詢

select

 

描述：查詢顧客的公司名、地址信息

查詢句法：

 

var 構建匿名類型1= from c in ctx.Customers

                      select new

                      {

                          公司名 = c.CompanyName,

                          地址 = c.Address

                      };

對應SQL：

 

SELECT [t0].[CompanyName],[t0].[Address]

FROM [dbo].[Customers] AS[t0]      

 

描述：查詢職員的姓名和雇用年份

查詢句法：

 

var 構建匿名類型2= from emp in ctx.Employees

                      select new

                      {

                          姓名 = emp.LastName +emp.FirstName,

                          雇用年 = emp.HireDate.Value.Year

                      };

對應SQL：

 

SELECT [t0].[LastName] + [t0].[FirstName] AS[value], DATEPART(Year, [t0].[HireDate]) AS [value2]

FROM [dbo].[Employees] AS [t0]

 

描述：查詢顧客的ID以及聯系信息（職位和聯系人）

查詢句法：

 

var 構建匿名類型3= from c in ctx.Customers

                      select new

                      {

                          ID= c.CustomerID,

                          聯系信息 = new

                          {

                              職位 = c.ContactTitle,

                              聯系人 = c.ContactName

                          }

                      };

對應SQL：

 

SELECT [t0].[CustomerID], [t0].[ContactTitle],[t0].[ContactName]

FROM [dbo].[Customers] AS [t0]

 

描述：查詢訂單號和訂單是否超重的信息

查詢句法：

 

var select帶條件 = from o in ctx.Orders

                        select new

                        {

                            訂單號 = o.OrderID,

                            是否超重 = o.Freight > 100? "是" : "否"

                        };

對應SQL：

 

SELECT [t0].[OrderID],

    (CASE

        WHEN[t0].[Freight] > @p0 THEN @p1

        ELSE@p2

     END)AS [value]

FROM [dbo].[Orders] AS [t0]

-- @p0: Input Currency (Size = 0; Prec = 19; Scale= 4) [100]

-- @p1: Input String (Size = 1; Prec = 0; Scale =0) [是]

-- @p2: Input String (Size = 1; Prec = 0; Scale =0) [否]

 

where

 

描述：查詢顧客的國家、城市和訂單數信息，要求國家是法國並且訂單數大於5

查詢句法：

 

var 多條件 = from c in ctx.Customers

                  where c.Country== "France" &&c.Orders.Count > 5

                  select new

                  {

                      國家 = c.Country,

                      城市 = c.City,

                      訂單數 = c.Orders.Count

                  };      

對應SQL：

 

SELECT [t0].[Country], [t0].[City], (

    SELECTCOUNT(*)

    FROM[dbo].[Orders] AS [t2]

    WHERE[t2].[CustomerID] = [t0].[CustomerID]

    )AS [value]

FROM [dbo].[Customers] AS [t0]

WHERE ([t0].[Country] = @p0) AND (((

    SELECTCOUNT(*)

    FROM[dbo].[Orders] AS [t1]

    WHERE[t1].[CustomerID] = [t0].[CustomerID]

    ))> @p1)

-- @p0: Input String (Size = 6; Prec = 0; Scale =0) [France]

-- @p1: Input Int32 (Size = 0; Prec = 0; Scale =0)[5]    

 

orderby

 

描述：查詢所有沒有下屬雇員的雇用年和名，按照雇用年倒序，按照名正序

查詢句法：

 

  var 排序 = from emp in ctx.Employees

                 where emp.Employees.Count== 0

                 orderby emp.HireDate.Value.Year descending,emp.FirstNameascending

                 select new

                 {

                     雇用年 = emp.HireDate.Value.Year,

                     名 = emp.FirstName

                 };     

對應SQL：

 

SELECT DATEPART(Year, [t0].[HireDate]) AS [value],[t0].[FirstName]

FROM [dbo].[Employees] AS [t0]

WHERE ((

    SELECTCOUNT(*)

    FROM[dbo].[Employees] AS [t1]

    WHERE[t1].[ReportsTo] = [t0].[EmployeeID]

    ))= @p0

ORDER BY DATEPART(Year, [t0].[HireDate]) DESC,[t0].[FirstName]

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [0]

 

分頁

 

描述：按照每頁10條記錄，查詢第二頁的顧客

查詢句法：

 

  var 分頁 = (from c in ctx.Customers select c).Skip(10).Take(10);

對應SQL：

 

SELECT TOP 10 [t1].[CustomerID],[t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle],[t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode],[t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

    SELECTROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[CompanyName],[t0].[ContactName], [t0].[ContactTitle], [t0].[Address],[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],[t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER], [t0].[CustomerID],[t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],[t0].[Country], [t0].[Phone], [t0].[Fax]

    FROM[dbo].[Customers] AS [t0]

    )AS [t1]

WHERE [t1].[ROW_NUMBER] >@p0

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [10]

 

分組

 

描述：根據顧客的國家分組，查詢顧客數大於5的國家名和顧客數

查詢句法：

 

        var 一般分組 = from c in ctx.Customers

                   group c by c.Country into g

                   where g.Count()> 5

                   orderby g.Count() descending

                   select new

                   {

                       國家 = g.Key,

                       顧客數 = g.Count()

                   };

對應SQL：

 

SELECT [t1].[Country], [t1].[value3] AS[顧客數]

FROM (

    SELECTCOUNT(*) AS [value], COUNT(*) AS [value2], COUNT(*) AS [value3],[t0].[Country]

    FROM[dbo].[Customers] AS [t0]

    GROUPBY [t0].[Country]

    )AS [t1]

WHERE [t1].[value] > @p0

ORDER BY [t1].[value2] DESC

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [5]

 

描述：根據國家和城市分組，查詢顧客覆蓋的國家和城市

查詢句法：

 

     var 匿名類型分組 = from c in ctx.Customers

                     group c by new {c.City, c.Country } into g

                     orderby g.Key.Country,g.Key.City

                     select new

                     {

                         國家 = g.Key.Country,

                         城市 = g.Key.City

                     };

對應SQL：

 

SELECT [t1].[Country], [t1].[City]

FROM (

    SELECT[t0].[City], [t0].[Country]

    FROM[dbo].[Customers] AS [t0]

    GROUPBY [t0].[City], [t0].[Country]

    )AS [t1]

ORDER BY [t1].[Country], [t1].[City]

 

描述：按照是否超重條件分組，分別查詢訂單數量

查詢句法：

 

var 按照條件分組 = from o in ctx.Orders

                     group o by new { 條件 = o.Freight > 100} into g

                     select new

                     {

                         數量 = g.Count(),

                         是否超重 = g.Key.條件 ? "是" : "否"

                     };

對應SQL：

 

SELECT

    (CASE

        WHEN[t2].[value2] = 1 THEN @p1

        ELSE@p2

     END)AS [value], [t2].[value] AS [數量]

FROM (

    SELECTCOUNT(*) AS [value], [t1].[value] AS [value2]

    FROM(

        SELECT

            (CASE

                WHEN[t0].[Freight] > @p0 THEN 1

                WHENNOT ([t0].[Freight] > @p0) THEN 0

                ELSENULL

             END)AS [value]

        FROM[dbo].[Orders] AS [t0]

        )AS [t1]

    GROUPBY [t1].[value]

    )AS [t2]

-- @p0: Input Currency (Size = 0; Prec = 19; Scale= 4) [100]

-- @p1: Input String (Size = 1; Prec = 0; Scale =0) [是]

-- @p2: Input String (Size = 1; Prec = 0; Scale =0) [否]

 

distinct

 

描述：查詢顧客覆蓋的國家

查詢句法：

 

var 過濾相同項 = (from c in ctx.Customers orderby c.Country selectc.Country).Distinct();

對應SQL：

 

SELECT DISTINCT [t0].[Country]

FROM [dbo].[Customers] AS [t0]

 

union

 

描述：查詢城市是A打頭和城市包含A的顧客並按照顧客名字排序

查詢句法：

 

var 連接並且過濾相同項 = (from c in ctx.Customers where c.City.Contains("A") selectc).Union

            (from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c=> c.ContactName);

對應SQL：

 

SELECT [t3].[CustomerID], [t3].[CompanyName],[t3].[ContactName], [t3].[ContactTitle], [t3].[Address],[t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country],[t3].[Phone], [t3].[Fax]

FROM (

    SELECT[t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName],[t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region],[t2].[PostalCode], [t2].[Country], [t2].[Phone],[t2].[Fax]

    FROM(

        SELECT[t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone],[t0].[Fax]

        FROM[dbo].[Customers] AS [t0]

        WHERE[t0].[City] LIKE @p0

        UNION

        SELECT[t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName],[t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region],[t1].[PostalCode], [t1].[Country], [t1].[Phone],[t1].[Fax]

        FROM[dbo].[Customers] AS [t1]

        WHERE[t1].[ContactName] LIKE @p1

        )AS [t2]

    )AS [t3]

ORDER BY [t3].[ContactName]

-- @p0: Input String (Size = 3; Prec = 0; Scale =0) [%A%]

-- @p1: Input String (Size = 2; Prec = 0; Scale =0) [A%]

 

concat

 

描述：查詢城市是A打頭和城市包含A的顧客並按照顧客名字排序，相同的顧客信息不會過濾

查詢句法：

 

var 連接並且不過濾相同項 = (from c in ctx.Customers where c.City.Contains("A")select c).Concat

            (from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c=> c.ContactName);

對應SQL：

 

SELECT [t3].[CustomerID], [t3].[CompanyName],[t3].[ContactName], [t3].[ContactTitle], [t3].[Address],[t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country],[t3].[Phone], [t3].[Fax]

FROM (

    SELECT[t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName],[t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region],[t2].[PostalCode], [t2].[Country], [t2].[Phone],[t2].[Fax]

    FROM(

        SELECT[t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone],[t0].[Fax]

        FROM[dbo].[Customers] AS [t0]

        WHERE[t0].[City] LIKE @p0

        UNIONALL

        SELECT[t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName],[t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region],[t1].[PostalCode], [t1].[Country], [t1].[Phone],[t1].[Fax]

        FROM[dbo].[Customers] AS [t1]

        WHERE[t1].[ContactName] LIKE @p1

        )AS [t2]

    )AS [t3]

ORDER BY [t3].[ContactName]

-- @p0: Input String (Size = 3; Prec = 0; Scale =0) [%A%]

-- @p1: Input String (Size = 2; Prec = 0; Scale =0) [A%]

 

取相交項

 

描述：查詢城市是A打頭的顧客和城市包含A的顧客的交集，並按照顧客名字排序

查詢句法：

 

var 取相交項 = (from c in ctx.Customers where c.City.Contains("A") selectc).Intersect

            (from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c=> c.ContactName);

對應SQL：

 

SELECT [t1].[CustomerID], [t1].[CompanyName],[t1].[ContactName], [t1].[ContactTitle], [t1].[Address],[t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country],[t1].[Phone], [t1].[Fax]

FROM (

    SELECTDISTINCT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone],[t0].[Fax]

    FROM[dbo].[Customers] AS [t0]

    )AS [t1]

WHERE (EXISTS(

    SELECTNULL AS [EMPTY]

    FROM[dbo].[Customers] AS [t2]

    WHERE([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName]LIKE @p0)

    ))AND ([t1].[City] LIKE @p1)

ORDER BY [t1].[ContactName]

-- @p0: Input String (Size = 2; Prec = 0; Scale =0) [A%]

-- @p1: Input String (Size = 3; Prec = 0; Scale =0) [%A%]

 

排除相交項

 

描述：查詢城市包含A的顧客並從中刪除城市以A開頭的顧客，並按照顧客名字排序

查詢句法：

 

var 排除相交項 = (from c in ctx.Customers where c.City.Contains("A") selectc).Except

            (from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c=> c.ContactName);

對應SQL：

 

SELECT [t1].[CustomerID], [t1].[CompanyName],[t1].[ContactName], [t1].[ContactTitle], [t1].[Address],[t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country],[t1].[Phone], [t1].[Fax]

FROM (

    SELECTDISTINCT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone],[t0].[Fax]

    FROM[dbo].[Customers] AS [t0]

    )AS [t1]

WHERE (NOT (EXISTS(

    SELECTNULL AS [EMPTY]

    FROM[dbo].[Customers] AS [t2]

    WHERE([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName]LIKE @p0)

    )))AND ([t1].[City] LIKE @p1)

ORDER BY [t1].[ContactName]

-- @p0: Input String (Size = 2; Prec = 0; Scale =0) [A%]

-- @p1: Input String (Size = 3; Prec = 0; Scale =0) [%A%]

 

子查詢

 

描述：查詢訂單數超過5的顧客信息

查詢句法：

 

var 子查詢 = from c in ctx.Customers

                   where

                       (from o in ctx.Orders group o by o.CustomerID into o whereo.Count()> 5 select o.Key).Contains(c.CustomerID)

                   select c;

對應SQL：

 

SELECT [t0].[CustomerID], [t0].[CompanyName],[t0].[ContactName], [t0].[ContactTitle], [t0].[Address],[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],[t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE EXISTS(

    SELECTNULL AS [EMPTY]

    FROM(

        SELECTCOUNT(*) AS [value], [t1].[CustomerID]

        FROM[dbo].[Orders] AS [t1]

        GROUPBY [t1].[CustomerID]

        )AS [t2]

    WHERE([t2].[CustomerID] = [t0].[CustomerID]) AND ([t2].[value]> @p0)

    )

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [5]

 

in操作

 

描述：查詢指定城市中的客戶

查詢句法：

 

        var in操作 = from c in ctx.Customers

                    where new string[]{ "Brandenburg", "Cowes", "Stavern"}.Contains(c.City)

                    select c;

對應SQL：

 

       SELECT[t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone],[t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE [t0].[City] IN (@p0, @p1, @p2)

-- @p0: Input String (Size = 11; Prec = 0; Scale =0) [Brandenburg]

-- @p1: Input String (Size = 5; Prec = 0; Scale =0) [Cowes]

-- @p2: Input String (Size = 7; Prec = 0; Scale =0) [Stavern]

 

join

 

描述：內連接，沒有分類的產品查詢不到

查詢句法：

 

var innerjoin = from p in ctx.Products

                        join c in ctx.Categories

                        on p.CategoryID equals c.CategoryID

                        select p.ProductName;

對應SQL：

 

SELECT COUNT(*) AS [value]

FROM [dbo].[Products] AS [t0]

INNER JOIN [dbo].[Categories] AS [t1] ON[t0].[CategoryID] = ([t1].[CategoryID])

 

描述：外連接，沒有分類的產品也能查詢到

查詢句法：

 

var leftjoin = from p in ctx.Products

                       join c in ctx.Categories

                       on p.CategoryID equals c.CategoryID

                       into pro

                       from x in pro.DefaultIfEmpty()

                       select p.ProductName;

對應SQL：

 

SELECT COUNT(*) AS [value]

FROM [dbo].[Products] AS [t0]

LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON[t0].[CategoryID] = ([t1].[CategoryID])

 

       你可能會很奇怪，原先很復雜的SQL使用查詢句法會很簡單（比如按照條件分組）。但是原先覺得很好理解的SQL使用查詢句法會覺得很復雜（比如連接查詢）。其實，我們還可以通過其它方式進行連接操作，在以後說DataLoadOptions類型的時候會再說。雖然Linq to sql已經非常智能了，但是對於非常復雜的查詢還是建議通過存儲過程實現，下次講解如何調用存儲過程。

本文轉自：http://www.cnblogs.com/lovecherry/archive/2007/08/17/859826.html

相關內容網址：http://www.cnblogs.com/lovecherry/archive/2007/08/13/853754.html

 

Linq查詢

這個是Linq中Linq to DataSet的部分，簡單講Linq就是C#內置的一種查詢語句，實際上你所使用的這個Linq to DataSet的語句是轉換成Enumerable.SelectMany().OrderBy()這種語句的，那麼相比於Linq to Object是針對IEnumerable<T>操作，Linq to DataSet主要是針對EnumerableRowCollection<DataRow>操作的的，類似的還有Linq to Xml是針對IEnumerable<XElement>操作的一樣

這句話的意思是以DataTable中OrderDate排序並返回結果行集，var只是C#3.0新添加的推斷機制，也就是你不寫EnumerableRowCollection<DataRow>，他在編譯的時候也會自動推斷var query是這個類型的。

你問其他的這樣是問有其他類似的語句麼？那麼基本上linq的from where orderby groupby join let都可以用在這裡面，你可以從msdn找一些例子來
 

Linq查詢問題

#region 測試LINQ
int[] list= new int[] { 6, 4, 3, 2, 9, 1, 7, 8, 5 };
var n = from p in list
.Where(p => p % 2 == 0)
select p;
foreach (var item in n)
{
Console.WriteLine(item);
}
#endregion
Console.Read();
參考資料：自己手打