- 浏览: 619741 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (314)
- 生活 (2)
- c# (37)
- 技术 (3)
- 400电话 (0)
- 400常见问题 (0)
- 400资费 (0)
- html (7)
- css (0)
- 数据库 (7)
- javascript (16)
- php (33)
- asp.net mvc2 (10)
- mysql (9)
- C# 3.0 LinQ (10)
- vs2005或vs2008 (4)
- flash and as3 (7)
- fms (1)
- dedeCMS (11)
- java (33)
- j2me (1)
- swing (1)
- c++ (1)
- jquery easyui (3)
- jquery (5)
- android (29)
- MongoDB (9)
- VtigerCRM (1)
- test (0)
- linux (30)
- nutch (2)
- SqlServer数据库 (2)
- 数据检索 (2)
- java抓取 (11)
- 乐天 (1)
- 淘宝 (1)
- Silverlight4.0 (6)
- sphinx实时索引 (5)
- ecshop (9)
- codeigniter(CI) (3)
- axure6 (1)
- 京东店铺装修教程 (2)
- xpath (1)
- joomla (2)
- bpm (1)
- Bootstrap (2)
- knockout (4)
- ecstore (4)
- css3 (1)
- 微信 (2)
- dede (0)
- soa_edi (1)
- odoo (0)
- web (1)
最新评论
-
骑着蜗牛超F1:
在ie6下报了个stack overflow at line ...
兼容ie6和ie7 的16进制码流在html中显示为图片代码(base64) -
冰之海洋:
好像少了一句代码吧? FloatingFunc.show(th ...
android 一直在最前面的浮动窗口效果 -
yanzhoupuzhang:
连接有问题!
iis7.0官方下载 IIS 7.0(微软Web服务器组件IIS 7.0) 官方(windows 2003,XP,2000) -
whatable:
唉,楼主你都没有搞清楚重量级和轻量级。。。。既然引用了SWT, ...
java swing 内置浏览器打开网页显示flash图表-swt Browser应用 -
yy_owen:
我晕啊,你链接的什么内容额,我要的iis,你链接个视频什么意思 ...
iis7.0官方下载 IIS 7.0(微软Web服务器组件IIS 7.0) 官方(windows 2003,XP,2000)
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, 网站 = "http://www.my400800.cn ", 订单数 = 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]) |
你可能会很奇怪,原先很复杂的SQL使用查询句法会很简单(比如按照条件分组)。但是原先觉得很好理解的SQL使用查询句法会觉得很复杂(比如连接查 询)。其实,我们还可以通过其它方式进行连接操作,在以后说DataLoadOptions类型的时候会再说。虽然Linq to sql已经非常智能了,但是对于非常复杂的查询还是建议通过存储过程实现,下次讲解如何调用存储过程。
发表评论
-
LINQ体验(7)――LINQ to SQL语句之Group By/Having和Exists/In/Any/All/Contains
2010-09-23 11:36 1251我们继续讲解LINQ to SQL语句,这篇我们来讨论Gro ... -
C#3.0入门系列(LingQ)(七)-GroupBy操作
2010-09-23 10:53 1219换一种写作风格。本节讲 groupby 操作。 ... -
C#3.0入门系列(LinQ)(七)-之OR工具介绍
2010-08-30 15:08 1070不得不再次给关注 dlinq的朋友道歉了。好久都没有更新 ... -
C#3.0入门系列(LinQ)(六)-之OrderBy操作
2010-08-12 10:51 2062本节讲orderby 操作. ... -
C#3.0入门系列(LingQ)(五)-之Where操作
2010-08-12 10:48 1346从本节开始,本文正式更名为C#3.0 入门系列。先发布 ... -
C# 3.0入门系列(LinQ)(四)-之Select操作
2010-08-12 10:44 3240先给关注dlinq 的朋友们道歉,最近工作实在忙,没有 ... -
C# 3.0入门系列(LinQ)(三)
2010-08-12 10:37 1258从本节开始,笔者将会和大家一起开始体验dlinq 了。 ... -
C# 3.0 入门系列(linq)(二)
2010-08-12 10:32 1090在第一篇中,我已经和大家简单介绍了linq 。也和大家 ... -
C# 3.0 入门系列(linq)(一)
2010-08-12 10:24 1263谈到dlinq ,就不得不先说linq 。让我们先看 ...
相关推荐
Ling to sql 语句,网站开发时时很有用的
Ling to sql
LINQ To SQL实现分页效果源码 VS2008 Frameworks3.5 LINQ To SQL 实现分页效果 数据库SQL 2005
三层模式 XML CSS word文档 JS 详细可以看携带的PPT程
linq to sql实现分层与gridview使用实现增删改,适合新手做三层项目开发,快速入门
ASP.NET MVC Linq to SQL 实现数据库增删改(源代码)ASP.NET MVC Linq to SQL 实现数据库增删改(源代码) ASP.NET MVC 分页控件
非常精彩的sql到linq 的教程实例,特别适合原来学习SQL,现在学习ling,两种语法对比介绍,也适合高级开发人员作为查询手册使用,值得下载收藏。
主要介绍了C# ling to sql 取多条记录最大时间,文中通过实例代码给大家介绍了sql 查询相同记录下日期最大的 一条,代码简单易懂,需要的朋友可以参考下
1、使用Linq to sql以对象形式操作SqlServer数据库的案例 2、使用Unity2018+vs2017 3、详细介绍https://blog.csdn.net/zhangxiao13627093203/article/details/113176556
LINQ测试查询工具,可以很好的测试你写的LINQ语法
这是本人写的linq to sql的增删改查封装基类(泛型),如果你想使用linq to sql开发项目的话,只要把需要增删改查的类继承该基类,就可以轻松实现。敬请参考,不到之处请多多指点。
采用三层架构加LING的人员信息管理系统,SQL数据库
用图形方式象形介绍Ling技术 简单易懂而且容易记忆 介绍Aggregation Filtering Group Joining Ordering Projection Quantifiers Selection Set Operations 等等......
sql、ling、实例大全. 特别全的ling语句大全,适合初学者,也适合高级人员查询用。
抛砖引玉,这里我采用ado的方法直接使用数据适配器读取到DataSet中,方法集成在linq to oracle的ObjectContext内,如果用其他的Context类型,例如DataContext,或者DbContext,对代码进行小量修改就可以。...
文档的导入导出功能 运用ling to sql 技术
用可视化变成工具编写一个模拟SPOOLING假脱机输入输出技术的程序,所以我要设计一个SP00LING输出进程和两个请求输出的用户进程,以及一个SP00LING输出服务程序。当请求输出的用户进程希望输出一系列信息时,调用输出...
写的自行开发的ling to sql三层架构的自行车租赁系统 不是很完善,但还不错
sql语句-linq语言-lambda表达式对照。 超级全面的Ling语法、SQL语法、lambda表达式对照(相同功能的不同于法)、 可以帮助对SQL、linq、lambda表达式存在疑惑不明白的在这里面可以充分提现出来语法间的对比和学习。让...