EF支持三种类型的查询:
1.LINQ to Entities
2.Entity SQL
3.Native SQL
1.LINQ to Entities
LINQ Method syntax:
using (var context = new SchoolDBEntities1()) { //使用Linq to Entities var myQuery = context.Students.Where(s => s.StudentName == "张三"); var student = myQuery.FirstOrDefault<Student>(); }
LINQ Query syntax:
using (var context = new SchoolDBEntities1()) { var myQuery = from s in context.Students where s.StudentName == "张三" select s; var student = myQuery.FirstOrDefault<Student>(); }
2.Entity SQL
entity SQL 是另外的一个查询语言,它直接被EF的Object Service处理,返回值是ObjectQuery而不是IQueryable;
//Querying with Object Services and Entity SQL string sqlString = "SELECT VALUE st FROM SchoolDBEntities.Students " + "AS st WHERE st.StudentName == 'Bill'"; var objctx = (ctx as IObjectContextAdapter).ObjectContext; ObjectQuery<Student> student = objctx.CreateQuery<Student>(sqlString); Student newStudent = student.First<Student>();
你同样可以使用EntityConnection和entityCommand来执行Entity SQL:
using (var con = new EntityConnection("name=SchoolDBEntities")) { con.Open(); EntityCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT VALUE st FROM SchoolDBEntities.Students as st where st.StudentName='Bill'"; Dictionary<int, string> dict = new Dictionary<int, string>(); using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection)) { while (rdr.Read()) { ); ); dict.Add(a, b); } } }
EntityDataReader不返回ObjectQuery,代替的是返回数据的行数和列数,你可以点击这个 MSDN blog链接来学习Entity SQL;
3.Native SQL
你可以执行原生态的SQL:
using (var ctx = new SchoolDBEntities()) { var studentName = ctx.Students.SqlQuery("Select studentid, studentname, standardId from Student where studentname='Bill'").FirstOrDefault<Student>(); }