//Linq语句
NorthwindEntities db = new NorthwindEntities();
//orderby子句
var query = db.Customers.OrderBy(c => c.ContactName).OrderBy(c => c.CompanyName).ToList();
//OrderByDescending:倒序排列
query = db.Customers.OrderByDescending(c => c.ContactName).OrderByDescending(c => c.CompanyName).ToList();
//分组就是把相同的东西放在一块
var query1 = from q in db.Employees
group q by q.TitleOfCourtesy into yoo
select new {yoo.Key,yoo};
//多分组条件:注意key就是一个匿名对象了,里面的字段就相当于是静态字段
var query2 = from q in db.Products
group q by new { q.CategoryID, q.SupplierID }
into qq
select new {qq.Key,qq };
foreach (var tmp in query2)
{
if (tmp.Key.CategoryID == 1)
{
foreach (var tmp1 in tmp.qq)
Console.WriteLine(tmp.Key.CategoryID + "_" + tmp.Key.CategoryID + "\t" + tmp1.Suppliers.CompanyName);
}
}
//判断集合是否为空
var query3 = from q in db.Customers where q.Orders.Any(c => c.OrderID == 10905) select q;
//模糊查询:Linq to SQL支持
var query4 = from q in db.Customers where SqlMethods.Like(q.CustomerID, "A%") select q;
//检查数据是否有值
var query5 = from q in db.Employees where !q.BirthDate.HasValue select q;
//获取指定日期数据
var query6 = from q in db.Employees where q.BirthDate.Value.Day == 29 select q;
//注意:去掉AsEnumerable(),就会报一下错误:LINQ to Entities
//不识别方法“Boolean IsLondon(System.String)”,因此该方法无法转换为存储表达式。
//解释:IQueryable:提供对数据类型已知的特定数据源的查询进行计算的功能。实体框架集
//会尝试将LINQ解析成SQL放到数据库服务器上执行,然而,外部方法不能转换成SQL,于是报
//上面的错误。解决方式就是转换成客户端泛型AsEnumerable<T>
var query7 = from q in db.Employees where IsLondon(q.City) select q;
//直接执行SQL语句
string strSQL="DELETE FROM dbo.Employees WHERE EmployeeID=20";
//var query8 = db.ExecuteStoreCommand(strSQL, null);
//db.SaveChanges();
strSQL = "SELECT * FROM dbo.Employees";
var query9 = db.ExecuteStoreQuery<Employees>(strSQL, null);
foreach (var tmp in query9)
{
Console.WriteLine(tmp.EmployeeID);
}
Console.ReadKey();
}
static bool IsLondon(string city)
{
if (city == "London")
return true;
else
return false;
}
static string SetString(string kk)
{
return kk + "__yoyo";
}
static void UpdateData()
{
NorthwindEntities db = new NorthwindEntities();
//游离对象使用Attach方法进行批量修改的前后顺序
Employees tmpEmp = new Employees();
tmpEmp.EmployeeID = 11;
db.Employees.Attach(tmpEmp);
tmpEmp.LastName = "shaoshao";
db.SaveChanges();
//本身就在数据上下文中的对象修改方式
//此处,是包含隐式事务的
try
{
tmpEmp = (from q in db.Employees where q.EmployeeID == 11 select q).FirstOrDefault();
tmpEmp.LastName = "yoyozhu";
tmpEmp.ReportsTo = 2;
tmpEmp = (from q in db.Employees where q.EmployeeID == 19 select q).FirstOrDefault();
tmpEmp.LastName = "yoyo____zhu";
tmpEmp.ReportsTo = 2;
db.SaveChanges();
}
catch (Exception ex)
{
}
}