使用EDMX查詢（EF基礎系列15） - caofangsheng－IT工程師數位筆記本

文章出處

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())
                {
                    int a = rdr.GetInt32(0);
                    var b = rdr.GetString(1);
                    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>();
    }