4,查询语法

概要：

       LinQ的查询语法。再次重申此系列是我的学习笔记，仅供个人复习，在第一片有来源链接。

内容：

       Select：

1， 查询顾客公司名，地址

Var tb=from c inctx..Customers

        Select new

        {

               公司名=c.CompanyName,

               地址=c.Address

};

 

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

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

 

2， 查询雇员的姓名和雇佣年龄

Var tb=from c inctx..Emplpyees

        Select new

        {

               姓名=c.LastName+c.FirstName,

               雇佣年龄=c.HireDate.Value.Year

};

 

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

[t0].[HireDate]) AS [value2] FROM [dbo].[Employees] AS [t0]

      

3， 查询顾客ID和联系信息（职位，联系人）

Var tb=from c inctx..Customers

        Select new

        {

               ID=c.CustomerID,

               联系信息=new

               {

                      职位=c.ContactTitle,

                      联系人=c.ContactName

               }

        };

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

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

                   4,查询订单号和订单是否超重

                     Vartb=from c in ctx .Orders

                            Selectnew

                            {

                                   订单号=c.OrderID,

                                   是否超重=c.Freight>100?”是”:”否”

                            };

       Where：

1， 查询顾客国家，城市，订单数信息，要求国家是法国，订单数大于5

Var tb=from c inctx .Customers

Wherec.Country==”France&&c.Orders.count>5

Select new

{

        国家=c.Country,

        城市=c.City,

        订单数=c.Orders.Count

};

 

2， 查询所有没有下属雇员的雇佣年和名，按照雇佣年倒序，名正序

Var tb=from x inctx.Employees

Wherec.Employees.Count==0

Orderbyc.HireDate.Value.Year descending,c.FirstName ascending

Select new

{

        雇佣年=c.HireDate.Value.Year,

        名=c.FirstName

};

       分页：

1， 按照每页10条记录，查询第二页的顾客

Var tb=(from cin ctx.Customers select c).Skip(10).Take(10);

       分组：

1， 根据顾客的国家分组，查询顾客数大于5的国家名和顾客数

Var tb=from c inctx.Customers

Group c byc.Country into g

Whereg.Count()>5

Orderbyg.Count() descending

Select new

{

        国家=g.Key,

        顾客数=g.Count()

};

2，  按照是否超重条件分组，分别查询订单数量

Var tb=from c in ctx .Orders

Group c by new {条件=c.Freight>100}into g

Select new

{

        数量=g.Count(),

        是否超重=g.Key.条件？”是”:”否”

};

           Distinct:

                     1,查询顾客覆盖的国家

                     Var tb=(from c inctx.Customers orderby c.Country select c.Country).Distinct();

 

                     ELECT DISTINCT [t0].[Country]

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

 

           Union:

1，  查询城市是A打头和城市包含A的顾客并按照顾客名字排序

Var 连接并过滤相同项=(fromc in ctx.Customers where c.City.Contains(“A”)

Select c).Union (from c in ctx.Customers wherec.ContactName.StartWith(“A”)

Select c).OrderBy(c=>c.ContactName);

 

           Concat:

                     1, 查询城市是A打头和城市包含A的顾客并按照顾客名字排序，

相同的顾客信息不会过滤

var 连接并且不过滤相同项 = (fromc in ctx.Customers wherec.City.Contains("A")

select c).Concat(from c inctx.Customers where c.ContactName.StartsWith("A") select

c).OrderBy(c => c.ContactName);

 

           子查询：

                     1,查询订单数超过5的顾客信息

                     Var tb=from c inctx.Customers

                     Where(from o in ctx.Ordersgroup o by o.CustomerID o where o.Count()>5

                     Selecto.Key).Contains(c.CustomerID)

                     Select c;

           In操作：

1，  查询指定城市中的客户

Var in操作=from c inctx.Customers

Where new string[]{“Brandenburg”,”Cowes”,”Stavern”}.Contains(c.City)

Select c;

           Join连接：

1，  内连接，没有分类的产品查询不到

Var innerjoin=from p in ctx.Products

Join c in ctx.Categories

On p.CategoryID equals c.CategoryID

Select p.ProductName;

 

                     SELECT COUNT(*) AS [value]

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

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

 

                     2,外连接，没有分类的产品也能查询到

                     var leftjoin = from p in ctx.Products

                      join c inctx.Categories

                      on p.CategoryID equalsc.CategoryID

                      into pro

                      from x inpro.DefaultIfEmpty()

                      selectp.ProductName;

 

                     SELECT COUNT(*) AS [value]

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

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