一般查询
var Courses = db.Courses.Where(c => c.Title == "Physics").OrderBy(c => c.Title);
foreach (var c in Courses)
{
Console.WriteLine(c.Title);
}
添加
Models.Course model = new Course();
model.Title = "test";
model.DepartmentID = 1;
model.CourseID = 4;
db.Courses.Add(model);
db.SaveChanges();
修改
Models.Course model = new Course();
var result = (from r in db.Courses
where r.Title.StartsWith("test")
orderby r.Title descending
select r).FirstOrDefault();
model = (Course)result;
model.Credits = 4;
model.Title = "good job";
db.SaveChanges();
删除
var result = from r in db.Courses
where r.CourseID == 4
select r;
foreach (var c in result)
{
db.Courses.Remove(c);
}
db.SaveChanges();
简单的函数计算（count，min，max，sum）
var result = (from r in db.StudentGrades
where r.StudentID == 2
select r).Sum(p=>p.Grade);
Console.WriteLine(result);
var result = (from r in db.StudentGrades
where r.StudentID == 2
select r).Max(p => p.Grade);
Console.WriteLine(result);
分页数据查询
var result = (from r in db.People
orderby r.LastName ascending
select r).Skip(5).Take(5);
foreach(var c in result)
{
Console.WriteLine(c.LastName);
}
分组group by
var ss = from r in db.StudentGrades
orderby r.StudentID descending
group r by new { r.StudentID, r.CourseID } into g
select new
{
g.Key,
grade = g.Sum(r => r.Grade)
};
foreach (var r in ss)
{
string output = "StudentID:" + r.Key.StudentID + " CourseID:" + r.Key.CourseID+" grade"+r.grade;
Console.WriteLine(output);
}
复杂的关联查询
var d = db.Database.SqlQueryForDynamic(@"SELECT
c.CourseID,d.[Name] as department,c.Title
FROM
Course c INNER JOIN Department d ON c.DepartmentID = d.DepartmentID");
foreach (dynamic item in d)
{
var s = item.Title;
Console.WriteLine(s);
}
Console.ReadLine();