LINQ
所有的linq 都是从from开始。select或者group by 结束
public class Peson
{
public int age{set;get;}
}
person p=new List(new Person(){age=10},
new Person(){age=20} );
var selPer=from P in persons where p.age=20;
foreach(var p in selPer)
console.writeline(p)
dataContext 吧查询语句翻译成sql语句,其有一个日志功能,,可以直接看被翻译过后的sql语句,方便调试
2
我要查找 商品数量>80的商品,我可以直接在加载的时候就查找符合条件的商品中
DataLoadOptions op=new DataLoadOptions();
op.LoadWith(p=>p.Mount)
p.AssociateWith(p=>p.Mount.wnere(od=>Mount>80)
ctx.LoadOptions=op;//ctx数据库 p是记录集体的
查询语句
static void test4_1()
{
NorthwindDataContext context = new NorthwindDataContext();
var result = from s1 in context.Customers
where s1.CustomerID.Contains("A")
select s1;
foreach (var item in result)
{
Console.WriteLine(item.CustomerID + "," + item.CompanyName);
}
}
static void test101()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from c in ctx.Customers
select new
{
公司名 = c.CompanyName,
地址 = c.Address
};
foreach (var item in result)
{
Console.WriteLine("公司名：{0,-36}地址：{1}",item.公司名,item.地址);
}
}
static void test102()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from emp in ctx.Employees
select new
{
姓名 = emp.LastName + emp.FirstName,
雇用年 = emp.HireDate.Value.Year
};
foreach (var item in result)
{
Console.WriteLine("姓名：{0,-16}地址：{1,-10}", item.姓名, item.雇用年);
}
}
static void test103()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from c in ctx.Customers
select new
{
ID = c.CustomerID,
联系信息 = new
{
职位 = c.ContactTitle,
联系人 = c.ContactName
}
};
foreach (var item in result)
{
Console.WriteLine("编号：{0,-10}联系职位：{1,-24}联系人：{2,-10}", item.ID, item.联系信息.职位,item.联系信息.联系人);
}
}
static void test104()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from o in ctx.Orders
select new
{
订单号 = o.OrderID,
是否超重 = o.Freight > 100 ? "是" : "否"
};
foreach (var item in result)
{
Console.WriteLine("订单号：{0,-20}是否超重：{1,-2}", item.订单号, item.是否超重);
}
}
static void test201()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from c in ctx.Customers
where c.Country == "France" && c.ContactTitle == "Owner"
select new
{
国家 = c.Country,
城市 = c.City,
联络人职位 = c.ContactTitle
};
foreach (var item in result)
{
Console.WriteLine("国家：{0,-10}城市：{1,-10}联络人职位：{2,-10}", item.国家, item.城市,item.联络人职位);
}
}
static void test301()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from emp in ctx.Employees
where emp.EmployeeID < 8
orderby emp.HireDate.Value.Year descending, emp.FirstName ascending
select new
{
雇用年 = emp.HireDate.Value.Year,
姓名 = emp.FirstName
};
foreach (var item in result)
{
Console.WriteLine("雇用年：{0,-10}姓名：{1,-10}", item.雇用年,item.姓名);
}
}
static void test401()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (from c in ctx.Customers select c).Skip(10*2).Take(10);
Console.WriteLine("按照每页10条记录，查询第二页的顾客");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-10}公司名：{1,-10}", item.CustomerID, item.CompanyName);
}
}
static void test501()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = 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()
};
Console.WriteLine("根据顾客的国家分组，查询顾客数大于5的国家名和顾客数");
foreach (var item in result)
{
Console.WriteLine("国家：{0,-10}顾客数：{1,-10}", item.国家, item.顾客数);
}
}
static void test502()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = 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
};
Console.WriteLine("根据国家和城市分组，查询顾客覆盖的国家和城市");
foreach (var item in result)
{
Console.WriteLine("国家：{0,-16}城市：{1,-10}", item.国家, item.城市);
}
}
static void test601()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (from c in ctx.Customers orderby c.Country select c.Country).Distinct();
Console.WriteLine("查询顾客覆盖的国家");
foreach (var item in result)
{
Console.WriteLine("国家：{0,-16}", item);
}
}
static void test701()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (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);
Console.WriteLine("查询城市是A打头和城市包含A的顾客并按照顾客名字排序");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-16}公司名：{0,-16}", item.CustomerID, item.CompanyName);
}
}
static void test801()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (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);
Console.WriteLine("查询城市是A打头和城市包含A的顾客并按照顾客名字排序，相同的顾客信息不会过滤");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-16}公司名：{0,-16}", item.CustomerID, item.CompanyName);
}
}
static void test901()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (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);
Console.WriteLine("查询城市是A打头的顾客和城市包含A的顾客的交集，并按照顾客名字排序");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-16}公司名：{0,-16}", item.CustomerID, item.CompanyName);
}
}
static void test1001()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (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);
Console.WriteLine("查询城市包含A的顾客并从中删除城市以A开头的顾客，并按照顾客名字排序 ");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-16}公司名：{0,-16}", item.CustomerID, item.CompanyName);
}
}
static void test1101()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = 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;
Console.WriteLine("查询订单数超过5的顾客信息");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-16}公司名：{0,-16}", item.CustomerID, item.CompanyName);
}
}
static void test1201()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from c in ctx.Customers
where new string[] { "Brandenburg", "Cowes", "Stavern" }.Contains(c.City)
select c;
Console.WriteLine("查询指定城市中的客户");
foreach (var item in result)
{
Console.WriteLine("编号：{0,-16}公司名：{0,-16}", item.CustomerID, item.CompanyName);
}
}
static void test1301()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from p in ctx.Products
join c in ctx.Categories
on p.CategoryID equals c.CategoryID
select p.ProductName;
Console.WriteLine("内连接，没有分类的产品查询不到");
foreach (var item in result)
{
Console.WriteLine("产品名：{0,-16}", item);
}
}
static void test1302()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = 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;
Console.WriteLine("外连接，没有分类的产品也能查询到");
foreach (var item in result)
{
Console.WriteLine("产品名：{0,-16}", item);
}
}
