vainnetwork的专栏

共同学习进步

一步一步学Linq to sql(四):查询句法

本系列课程均转自

LoveCherry

技术无极限http://www.cnblogs.com/lovecherry/archive/2007/08/14/855681.html

版权归LoveCherry所有

 

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], (

    SELECT COUNT(*)

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

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

    ) AS [value]

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

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

    SELECT COUNT(*)

    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.FirstName ascending

                 select new

                 {

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

                     = emp.FirstName

                 };     

对应SQL

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

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

WHERE ((

    SELECT COUNT(*)

    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 (

    SELECT ROW_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 (

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

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

    GROUP BY [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]

    GROUP BY [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 (

    SELECT COUNT(*) AS [value], [t1].[value] AS [value2]

    FROM (

        SELECT

            (CASE

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

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

                ELSE NULL

             END) AS [value]

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

        ) AS [t1]

    GROUP BY [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 select c.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") select c).Union

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).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") select c).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 ALL

        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") select c).Intersect

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).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 (

    SELECT DISTINCT [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(

    SELECT NULL 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") select c).Except

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).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 (

    SELECT DISTINCT [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(

    SELECT NULL 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 where o.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(

    SELECT NULL AS [EMPTY]

    FROM (

        SELECT COUNT(*) AS [value], [t1].[CustomerID]

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

        GROUP BY [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已经非常智能了,但是对于非常复杂的查询还是建议通过存储过程实现,下次讲解如何调用存储过程。

阅读更多
个人分类: LINQ系列学习
上一篇一步一步学Linq to sql(三):增删改
下一篇一步一步学Linq to sql(五):存储过程
想对作者说点什么? 我来说一句

LINQ中文教程

2013年02月19日 876KB 下载

LINQ中文教程word格式

2010年12月22日 855KB 下载

LINQ详细教程

2014年01月28日 865KB 下载

LINQ中文教程LINQ中文教程

2011年03月22日 833KB 下载

VS2008 LINQ基础教程

2011年03月07日 858KB 下载

一步一步Linq to sql

2010年01月29日 850KB 下载

LINQ中文教程(WORD格式)

2010年08月07日 786KB 下载

LINQ中文系列教程

2010年10月16日 406KB 下载

一步一步LINQ to sql

2009年07月27日 2.64MB 下载

没有更多推荐了,返回首页

关闭
关闭