查询句法：
private void Syntactic()
{
StreamWriter sw = new StreamWriter(Server.MapPath("Log.txt"), true);
ctx.Log = sw;
DataLoadOptions option = new DataLoadOptions();
//加载Products实体类的同时也把Products对应的Order_Details加载出来
option.LoadWith<Products>(p => p.Order_Details);
option.AssociateWith<Products>(p => p.Order_Details.Where(o => o.Quantity > 80));
ctx.LoadOptions = option;
//select
//查询句法在定义时并未被执行，只有在使用时才会执行，此处仅仅只是一个描述，对应一个T_Sql操作
var qSelect = from c in ctx.Customers
select new
{
CompanyName = c.CompanyName,
Address = c.Address
};
var qSelect2 = from emp in ctx.Employees
select new
{
name = emp.LastName + emp.FirstName,
year = emp.HireDate.Value.Year
};
var qSelect3 = from c in ctx.Customers
select new
{
ID = c.CustomerID,
ContactInfo = new
{
position = c.ContactTitle,
Contacter = c.ContactName
}
};
var qSelect4 = from o in ctx.Orders
select new
{
OrderNumber = o.OrderID,
IsOverWeight = o.Freight > 100 ? "yes" : "no"
};
//where
var qWhere1 = from c in ctx.Customers
where c.Country == "France" && c.Orders.Count > 5
select new
{
Country = c.Country,
City = c.City,
OrderNum = c.Orders.Count
};
//orderby
var qOrderby1 = from e in ctx.Employees
where e.Employees2.Count == 0
orderby e.HireDate.Value.Year descending, e.FirstName ascending
select new
{
name = e.FirstName + e.LastName,
year = e.HireDate.Value.Year
};
//pagination
//按照每页10条记录，查询第二页的顾客
var qPage = (from c in ctx.Customers select c).Skip(10).Take(10);
//group
var qGroup1 = from c in ctx.Customers
group c by c.Country into g
where g.Count() > 5
orderby g.Count() descending
select new
{
Country = g.Key,
Number = g.Count()
};
//通过匿名对象分组
var qGroup2 = from c in ctx.Customers
group c by new { c.Country, c.City } into g
orderby g.Key.Country, g.Key.City
select new
{
Country = g.Key.Country,
City = g.Key.City
};
var qGroup3 = from o in ctx.Orders
group o by new { condition = o.Freight > 100 } into g
select new
{
number = g.Count(),//g.Count()表示分组中的元素总计
IsOverWeight = g.Key.condition ? "yes" : "no"
};
//distinct
var qDistinct = (from c in ctx.Customers
orderby c.Country ascending
select c.Country).Distinct();
//union:连接两个子查询的结果集并过滤相同项
var qUnion = (from c in ctx.Customers
where c.City.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).Union(
from c in ctx.Customers
where c.ContactName.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).OrderBy(o => o.ContactName);
//concat:连接两个子查询的结果集,不过滤相同项
var qConcat = (from c in ctx.Customers
where c.City.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).Concat(
from c in ctx.Customers
where c.ContactName.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).OrderByDescending(o => o.ContactName);
//intersect:取相交项
var qIntersect = (from c in ctx.Customers
where c.City.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).Intersect(
from c in ctx.Customers
where c.ContactName.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).OrderByDescending(o => o.ContactName);
//except:排除相交项
var qExcept = (from c in ctx.Customers
where c.City.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).Except(
from c in ctx.Customers
where c.ContactName.StartsWith("A")
select new
{
City = c.City,
ContactName = c.ContactName
}).OrderByDescending(o => o.ContactName);
//子查询SubQuery
var qSubQuery1 = from c in ctx.Customers
where
(from o in ctx.Orders
group o by o.CustomerID into g
where g.Count() > 5
select g.Key).Contains(c.CustomerID)
select new
{
name = c.ContactName,
country = c.Country
};
//in操作
var qSubQuery2 = from c in ctx.Customers
where new string[] { "Brandenburg", "Cowes", "Stavern" }.Contains(c.City)
select c;
//join描述：内连接INNER JOIN，没有分类的产品查询不到
var qJoin = from p in ctx.Products
join c in ctx.Categories
on p.CategoryID equals c.CategoryID
select new { ProductName = p.ProductName };
//sgv.DataSource = qJoin;
//sgv.DataBind();
//非连接查询，生成的sql语句为多表查询
var leftjoin = from p in ctx.Products
join c in ctx.Categories
on p.CategoryID equals c.CategoryID
into pro
from x in pro
select new { ProductName = p.ProductName };
//sgv.DataSource = leftjoin;
//sgv.DataBind();
//外连接LEFT OUTER JOIN,没有分类的产品也能查询到
var leftjoin1 = from p in ctx.Products
join c in ctx.Categories
on p.CategoryID equals c.CategoryID
into pro
from x in pro.DefaultIfEmpty()
select new { ProductName = p.ProductName };
