使用EDMX查询(EF基础系列15)

时间:2024-08-27 10:35:38

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>();
    }