2011-05-05 21:20 kenty06 阅读(304) 评论() 收藏 举报
1、最简单
var 构建匿名类型1 = from c in ctx.Customers
select new
{
公司名 = c.CompanyName,
地址 = c.Address
};
2、自定义返回的结果
var 构建匿名类型2 = from emp in ctx.Employees
select new
{
姓名 = emp.LastName + emp.FirstName,
雇用年 = emp.HireDate.Value.Year
};
3、比较复杂一点的自定义返回结果
var 构建匿名类型3 = from c in ctx.Customers
select new
{
ID = c.CustomerID,
联系信息 = new
{
职位 = c.ContactTitle,
联系人 = c.ContactName
}
};
4、有条件判断的自定义返回结果
var select带条件 = from o in ctx.Orders
select new
{
订单号 = o.OrderID,
是否超重 = o.Freight > 100 ? "是" : "否"
};
5、简单的where，限制国家以及订单数量
var 多条件 = from c in ctx.Customers
where c.Country == "France" && c.Orders.Count > 5
select new
{
国家 = c.Country,
城市 = c.City,
订单数 = c.Orders.Count
};
6、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
};
7、分页，skip跳过几条，take选择几条， pageSize*(pageIndex-1) pageSize
var 分页 = (from c in ctx.Customers select c).Skip(10).Take(10);
8、简单分组 group
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()
};
9、自定义分组
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
};
10、按照条件分组
var 按照条件分组 = from o in ctx.Orders
group o by new { 条件 = o.Freight > 100 } into g
select new
{
数量 = g.Count(),
是否超重 = g.Key.条件 ? "是" : "否"
};
11、Distinct过滤重复项
var 过滤相同项 = (from c in ctx.Customers orderby c.Country selectc.Country).Distinct();
12、Union 连接并且过滤相同项
var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") selectc).Union
(from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c => c.ContactName);
13、Concat 连接并且不过滤相同项
var 连接并且不过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") selectc).Concat
(from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c => c.ContactName);
14、Intersect 取相交项
var 取相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect
(from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c => c.ContactName);
15、Except 排除相交项
var 排除相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Except
(from c in ctx.Customers where c.ContactName.StartsWith("A") selectc).OrderBy(c => c.ContactName);
16、子查询
var 子查询 = from c in ctx.Customers
where
(from o in ctx.Orders group o by o.CustomerID into o whereo.Count() > 5 select o.Key).Contains(c.CustomerID)
select c;
17、in查询
var in操作 = from c in ctx.Customers
where new string[] { "Brandenburg", "Cowes", "Stavern"}.Contains(c.City)
select c;
18、join 内连接，没有分类的查不到
var innerjoin = from p in ctx.Products
join c in ctx.Categories
on p.CategoryID equals c.CategoryID
select p.ProductName;
19、join 外连接，没有分类的也可以查到
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;
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。