`
yimeng500
  • 浏览: 54659 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

linq语法

    博客分类:
  • net
阅读更多
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])


分享到:
评论

相关推荐

    VS2010轻松学习C#-从零到深入-天轰穿.NET4趣味编程视频教程_第35讲:LINQ语法(二)

    总之,通过《VS2010轻松学习C# - 从零到深入 - 天轰穿.NET4趣味编程视频教程》的第35讲,学习者可以系统地掌握LINQ语法,为后续的.NET开发工作打下坚实的基础。无论你是C#初学者还是希望提升技能的开发者,这都是...

    asp.net mvc linq语法示例

    ### ASP.NET MVC LINQ语法示例详解 #### 一、引言 在现代Web开发中,ASP.NET MVC凭借其简洁的架构与强大的功能备受开发者青睐。而在数据处理方面,LINQ(Language Integrated Query)作为.NET Framework的一个重要...

    LINQ语法与实例

    ### LINQ语法与实例 #### 一、简介 LINQ(Language Integrated Query)是一种用于.NET框架的语言集成查询技术,它提供了强大的数据查询功能,并且能够跨多种数据源使用。LINQ不仅简化了代码,还提高了代码的可读性...

    Linq语法学习资料

    详细讲解linq语法,清晰解读linq语句使用!

    全面的Linq语法-SQL语法-lambda表达式对照(相同功能的不同于法)

    本文将深入探讨LINQ语法、SQL语句以及lambda表达式的对应关系,帮助开发者更好地理解和运用这些概念。 首先,让我们从SQL语句开始。SQL(Structured Query Language)是用于管理和操作关系型数据库的标准语言。常见...

    Linq 语法 总结

    1. **查询表达式**:Linq的核心是查询表达式,它使用类似SQL的语法在代码中编写查询。查询表达式通常以`from`关键字开始,接着是数据源、`where`来过滤数据、`select`或`group`来定义结果。 2. **方法链式查询**:...

    c#linq语法使用及实例,自己工作总结,最常用的

    ### C# LINQ语法使用及实例详解 #### 一、`using`语句的理解与应用场景 在C#中,`using`语句主要用于确保资源能够得到正确的释放,尤其是在处理那些实现了`IDisposable`接口的对象时。`IDisposable`接口允许对象在...

    LINQ语法基础

    ### LINQ语法基础详解 #### 一、LINQ简介 **LINQ**(Language Integrated Query)是一种集成在.NET框架中的查询技术,它使得开发者能够以一种更简洁、更强大的方式处理数据。LINQ提供了多种用于查询不同数据源的方法...

    LINQ基本语法及其示例

    ### LINQ基本语法及其示例 #### 一、前言 LINQ(Language Integrated Query)是一种集成在.NET Framework 3.5及更高版本中的技术,它允许开发人员使用类似于SQL的语言来查询各种类型的数据源,如数据库、XML文档、...

    XML Linq语法

    ### XML Linq语法详解 #### 一、基本概念与语法结构 LINQ to XML 是一种强大的查询技术,它允许开发人员使用类似于 SQL 的查询来处理 XML 数据。此技术结合了 LINQ 的强大功能以及 XML 的灵活性,使得处理 XML 数据...

    C# linq经典语法

    1. **基础概念**:LINQ提供了一种统一的查询语法,用于操作各种数据源,包括数组、集合、数据库、XML等。它的核心是查询表达式,这是一种声明式的编程方式,类似于SQL。 2. **查询表达式**:C# LINQ查询表达式以...

    完整linq语法

    ### 完整LINQ语法详解 #### 一、前言 在.NET框架中,LINQ(Language Integrated Query)提供了一种高效且易于理解的方式来查询数据集合。本篇将基于提供的部分代码示例来深入探讨LINQ的核心概念及其使用方法。 ##...

    超级全面的Linq语法、SQL语法、lambda表达式对照(相同功能的不同于法)

    很有价值的文档学习语法指南。。。 sql语句-linq语言-lambda...可以帮助对SQL、linq、lambda表达式存在疑惑不明白的在这里面可以充分提现出来语法间的对比和学习。让你逐渐对linq、lambda表达式有了解甚至使用。。。

    Python-使用LINQ功能扩展python列表实现干净快速的编码

    然而,值得注意的是,虽然LINQ语法简洁,但在某些情况下,Python原生的迭代器和生成器可能在性能上更优,因为它们不会一次性加载整个数据集到内存中。因此,在追求代码简洁的同时,也要考虑性能因素,根据实际情况...

    LINQ_to_SQL语法及实例大全

    LINQ (Language Integrated Query) to SQL 是.NET框架中的一种技术,它允许开发人员使用C#或VB.NET等语言的查询语法直接对SQL Server数据库进行操作。这种技术将查询表达式集成到编程语言中,简化了数据库操作,提高...

    ORM使用手册 使用说明. ORM系统采用与微软LINQ to SQL相似的操作方式,支持类似LINQ语法查询与基于轨迹的增删改操作。

    在标题中提到的ORM系统采用了微软的LINQ to SQL的操作方式,这意味着它支持类似于LINQ(Language Integrated Query,语言集成查询)的查询语法,这使得查询表达式更加直观且易于理解。LINQ允许开发者在C#或VB.NET等...

    Linq 中文版文档

    Linq中文版文档是一个详细的教程,涵盖了Linq的基本概念、语法、示例代码,以及在ASP.NET环境中的应用。 1. **Linq的基本原理** - **查询表达式语法**:Linq引入了一种新的查询表达式语法,使查询代码更加直观和...

    Linq 将生成SQL语句显示出来

    Linq 将生成SQL语句显示出来 用于检查linq解析出来的sql语句。 很适合新手学习

Global site tag (gtag.js) - Google Analytics