03Linqtosql--查询语法.doc_第1页
03Linqtosql--查询语法.doc_第2页
03Linqtosql--查询语法.doc_第3页
03Linqtosql--查询语法.doc_第4页
03Linqtosql--查询语法.doc_第5页
已阅读5页,还剩8页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

03 Linq to sql-查询语法知识点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.HireDate.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.CustomerID, 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, (CASE WHEN t0.Freight p0 THEN p1ELSE p2END) AS valueFROM 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.Customerswhere c.Country = France & c.Orders.Count 5select new国家 = c.Country,城市 = c.City,订单数 = 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 = 0; Scale = 0) France- p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) 5 orderby描述:查询所有没有下属雇员的雇用年和名,按照雇用年倒序,按照名正序查询句法:var 排序 = from emp in ctx.Employeeswhere emp.Employees.Count = 0orderby emp.HireDate.Value.Year descending, emp.FirstName ascendingselect new雇用年 = emp.HireDate.Value.Year,名 = emp.FirstName; 对应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分页描述:按照每页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, 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 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.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.Customersgroup 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描述:按照是否超重条件分组,分别查询订单数量查询句法:var 按照条件分组 = from o in ctx.Ordersgroup o by new 条件 = o.Freight 100 into gselect new数量 = g.Count(),是否超重 = g.Key.条件 ? 是 : 否;对应SQL:SELECT (CASE WHEN t2.value2 = 1 THEN p1ELSE p2END) AS value, t2.value AS 数量FROM (SELECT COUNT(*) AS value, t1.value AS value2FROM (SELECT (CASE WHEN t0.Freight p0 THEN 1WHEN 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 select c.Country).Distinct(); 对应SQL:SELECT DISTINCT t0.CountryFROM dbo.Customers AS t0Union(说明:连接不同的集合,自动过滤相同项;延迟。即是将两个集合进行合并操作,过滤相同的项。)描述:查询姓名是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.FaxFROM (SELECT t2.CustomerID, t2.CompanyName, t2.ContactName, t2.ContactTitle, t2.Address, t2.City, t2.Region, t2.PostalCode, t2.Country, t2.Phone, t2.FaxFROM (SELECT t0.CustomerID, t0.CompanyName, t0.ContactName, t0.ContactTitle, t0.Address, t0.City, t0.Region, t0.PostalCode, t0.Country, t0.Phone, t0.FaxFROM dbo.Customers AS t0WHERE t0.City LIKE p0UNIONSELECT t1.CustomerID, t1.CompanyName, t1.ContactName, t1.ContactTitle, t1.Address, t1.City, t1.Region, t1.PostalCode, t1.Country, t1.Phone, t1.FaxFROM dbo.Customers AS t1WHERE t1.ContactName LIKE p1) AS t2) AS t3ORDER BY t3.ContactName- p0: Inp0ut String (Size = 3; Prec = 0; Scale = 0) %A%- p1: Input String (Size = 2; Prec = 0; Scale = 0) A%Concat说明:连接不同的集合,不会自动过滤相同项;延迟。描述:查询是包含A的城市对应的顾客和姓名以A开头的顾客,并按照顾客名字排序,相同的顾客信息不会过滤(两个条件多成立1.只要城市是包含A,2.也可以只要姓名一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.FaxFROM (SELECT t2.CustomerID, t2.CompanyName, t2.ContactName, t2.ContactTitle, t2.Address, t2.City, t2.Region, t2.PostalCode, t2.Country, t2.Phone, t2.FaxFROM (SELECT t0.CustomerID, t0.CompanyName, t0.ContactName, t0.ContactTitle, t0.Address, t0.City, t0.Region, t0.PostalCode, t0.Country, t0.Phone, t0.FaxFROM dbo.Customers AS t0WHERE t0.City LIKE p0UNION ALLSELECT t1.CustomerID, t1.CompanyName, t1.ContactName, t1.ContactTitle, t1.Address, t1.City, t1.Region, t1.PostalCode, t1.Country, t1.Phone, t1.FaxFROM dbo.Customers AS t1WHERE t1.ContactName LIKE p1) AS t2) AS t3ORDER 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.FaxFROM (SELECT DISTINCT t0.CustomerID, t0.CompanyName, t0.ContactName, t0.ContactTitle, t0.Address, t0.City, t0.Region, t0.PostalCode, t0.Country, t0.Phone, t0.FaxFROM dbo.Customers AS t0) AS t1WHERE (EXISTS(SELECT NULL AS EMPTYFROM dbo.Customers AS t2WHERE (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.FaxFROM (SELECT DISTINCT t0.CustomerID, t0.CompanyName, t0.ContactName, t0.ContactTitle, t0.Address, t0.City, t0.Region, t0.PostalCode, t0.Country, t0.Phone, t0.FaxFROM dbo.Customers AS t0) AS t1WHERE (NOT (EXISTS(SELECT NULL AS EMPTYFROM dbo.Customers AS t2WHERE (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.Customerswhere(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.FaxFROM dbo.Customers AS t0WHERE EXISTS(SELECT NULL AS EMPTYFROM (SELECT COUNT(*) AS value, t1.CustomerIDFROM dbo.Orders AS t1GROUP BY t1.CustomerID) AS t2WHERE (t2.CustomerID = t0.CustomerID) AND (t2.value p0)- p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) 5in操作描述:查询指定城市中的客户查询句法:var in操作 = from c in ctx.Customerswhere 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.Reg

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论