《linq基础操作实例》由会员分享，可在线阅读，更多相关《linq基础操作实例（11页珍藏版）》请在金锄头文库上搜索。
1、select 描述：查询顾客的公司名、地址信息 查询句法： var 构建匿名类型 1 = from c in ctx.Customersselect new公司名 = c.CompanyName,地址 = c.Address;对应 SQL：SELECT t0.CompanyName, t0.AddressFROM dbo.Customers AS t0描述：查询职员的姓名和雇用年份 查询句法： var 构建匿名类型 2 = from emp in ctx.Employeesselect new姓名 = emp.LastName + emp.FirstName,雇用年 = emp.HireDat
2、e.Value.Year;对应 SQL： SELECT t0.LastName + t0.FirstName AS value, DATEPART(Year, t0.HireDate) AS value2FROM dbo.Employees AS t0描述：查询顾客的 ID 以及联系信息（职位和联系人） 查询句法： var 构建匿名类型 3 = from c in ctx.Customersselect newID = c.CustomerID,联系信息 = new职位 = c.ContactTitle,联系人 = c.ContactName;对应 SQL： SELECT t0.Custome
3、rID, t0.ContactTitle, t0.ContactNameFROM dbo.Customers AS t0描述：查询订单号和订单是否超重的信息 查询句法： var select 带条件 = from o in ctx.Ordersselect new订单号 = o.OrderID,是否超重 = o.Freight 100 ? 是 : 否;对应 SQL：SELECT t0.OrderID,(CASEWHEN t0.Freight p0 THEN p1ELSE p2END) AS valueFROM dbo.Orders AS t0- p0: Input Currency (Size
4、 = 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.Customerswhere c.Country = France & c.Orders.Count 5select new国家 = c.Country,城市 = c.City,订单数
5、= c.Orders.Count;对应 SQL： SELECT t0.Country, t0.City, (SELECT COUNT(*)FROM dbo.Orders AS t2WHERE t2.CustomerID = t0.CustomerID) AS valueFROM dbo.Customers AS t0WHERE (t0.Country = p0) AND (SELECT COUNT(*)FROM dbo.Orders AS t1WHERE t1.CustomerID = t0.CustomerID) p1)- p0: Input String (Size = 6; Prec =
6、 0; Scale = 0) France- p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) 5orderby 描述：查询所有没有下属雇员的雇用年和名，按照雇用年倒序，按照名正序 查询句法： var 排序 = from emp in ctx.Employeeswhere emp.Employees.Count = 0orderby emp.HireDate.Value.Year descending, emp.FirstName ascendingselect new雇用年 = emp.HireDate.Value.Year,名 = emp.Fi
7、rstName;对应 SQL： SELECT DATEPART(Year, t0.HireDate) AS value, t0.FirstNameFROM dbo.Employees AS t0WHERE (SELECT COUNT(*)FROM dbo.Employees AS t1WHERE t1.ReportsTo = t0.EmployeeID) = p0ORDER BY DATEPART(Year, t0.HireDate) DESC, t0.FirstName- p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) 0分页 描述：按照每页
8、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.FaxFROM (SELECT ROW_NUMBER() OVER (ORDER BY t0.CustomerID
9、, 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.FaxFROM dbo.Customers
10、 AS t0) AS t1WHERE t1.ROW_NUMBER p0- p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) 10分组 描述：根据顾客的国家分组，查询顾客数大于 5 的国家名和顾客数 查询句法： var 一般分组 = from c in ctx.Customersgroup c by c.Country into gwhere g.Count() 5orderby g.Count() descendingselect new国家 = g.Key,顾客数 = g.Count();对应 SQL： SELECT t1.Country, t1
11、.value3 AS 顾客数FROM (SELECT COUNT(*) AS value, COUNT(*) AS value2, COUNT(*) AS value3, t0.CountryFROM dbo.Customers AS t0GROUP BY t0.Country) AS t1WHERE t1.value p0ORDER BY t1.value2 DESC- p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) 5 描述：根据国家和城市分组，查询顾客覆盖的国家和城市 查询句法：var 匿名类型分组 = from c in ctx.Cust
12、omersgroup c by new c.City, c.Country into gorderby g.Key.Country, g.Key.Cityselect new国家 = g.Key.Country,城市 = g.Key.City;对应 SQL： SELECT t1.Country, t1.CityFROM (SELECT t0.City, t0.CountryFROM dbo.Customers AS t0GROUP BY t0.City, t0.Country) AS t1ORDER BY t1.Country, t1.City描述：按照是否超重条件分组，分别查询订单数量 查询
13、句法： var 按照条件分组 = from o in ctx.Ordersgroup o by new 条件 = o.Freight 100 into gselect new数量 = g.Count(),是否超重 = g.Key.条件 ? 是 : 否;对应 SQL： SELECT(CASEWHEN t2.value2 = 1 THEN p1ELSE p2END) AS value, t2.value AS 数量FROM (SELECT COUNT(*) AS value, t1.value AS value2FROM (SELECT(CASEWHEN t0.Freight p0 THEN 1W
14、HEN NOT (t0.Freight p0) THEN 0ELSE NULLEND) AS valueFROM dbo.Orders AS t0) AS t1GROUP 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.CountryFROM dbo.Customers AS t0union 描述：查询城市是 A 打头和城市包含 A 的顾客并按照顾客名字排序 查询句法： var 连接并且过滤相同项 =(from c in ctx.Cust
