Linq to sql(四):查询句法

转载 2012年03月31日 10:22:27

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.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 ((

    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 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

        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"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 (

    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"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 (

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

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


转载原文http://www.cnblogs.com/lovecherry/archive/2007/08/17/859826.html

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

原文作者链接:http://www.cnblogs.com/lovecherry/archive/2007/08/17/859826.html  select 描述:查询顾客的公司名、地址信息查询句法...

Linq to sql学习之查询句法

select 描述:查询顾客的公司名、地址信息查询句法:var 构建匿名类型1 = from c in ctx.Customers                      select new   ...

Linq to sql :查询句法

select   描述:查询顾客的公司名、地址信息   查询句法: var 构建匿名类型1 = from c in ctx.Customers select new   {   公司名 = ...

Linq to sql查询句法

select   描述:查询顾客的公司名、地址信息 查询句法: var 构建匿名类型1 = from c in ctx.Customers                  ...

关于Linq to sql 实现 模糊查询 string数组

前景:当UI一个查询条件为多项选择,或录入多个条件的时候,比如查询所有名称里面包含以下动态条件,需要模糊查询里面每一项时 //比如是这样一个数组条件:new string[]{"兴业银行","广州银行...
  • lwbjyl
  • lwbjyl
  • 2016年05月20日 19:53
  • 1568

FineUI LINQ TO SQL 数据库分页查询

FineUI

LINQ to SQL异步查询

http://www.prg-cn.com/article-4419-1.html 异步操作是提高Web应用程序吞吐量的重要手段,关于这方面的话题已经在前 文《正确使用异步操作》中解释过了。对于...
  • wyh0318
  • wyh0318
  • 2012年08月02日 17:15
  • 281

LINQ to SQL语句之动态查询

高级特性 本文介绍LINQ的高级特性,其包括大家都关心的动态查询的用法,另外简单提下ID标识这个知识。 动态查询 有这样一个场景:应用程序可能会提供一个用户界面,用户可以使用该用户界面指定一个或...

linq to sql的多条件动态查询

linq  to sql的多条件动态查询确实是一件头痛的事情,用表达式树或反射方法要写一大堆代码,有时候想想与其这么复杂,还不如回到手动sql拼接的年代,但是技术总是向前发展的,终归还是要在linq上...
  • nnsword
  • nnsword
  • 2011年09月04日 17:41
  • 1038

LINQ to SQL语句(23)之动态查询

http://www.prg-cn.com/article-4430-1.html 动态查询 有这样一个场景:应用程序可能会提供一个用户界面,用 户可以使用该用户界面指定一个或多个谓词来筛选数...
  • wyh0318
  • wyh0318
  • 2012年08月02日 17:10
  • 824
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Linq to sql(四):查询句法
举报原因:
原因补充:

(最多只允许输入30个字)