Query using EDMX (EF basic Series 10), edmxef

Query using EDMX (EF basic Series 10), edmxef

EF supports three types of queries:

1. LINQ to Entities

2. Entity SQL

3. Native SQL

 

1. LINQ to Entities

LINQ Method syntax:

Using (var context = new SchoolDBEntities1 () {// use Linq to Entities var myQuery = context. students. where (s => s. studentName = "Zhang San"); var student = myQuery. firstOrDefault <Student> ();}

 

LINQ Query syntax:

Using (var context = new SchoolDBEntities1 () {var myQuery = from s in context. students where s. studentName = "Zhang San" select s; var student = myQuery. firstOrDefault <Student> ();}

 

2. Entity SQL

Entity SQL is another query language, which is directly processed by EF's Object Service. The returned value is ObjectQuery rather than 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>();

 

You can also use EntityConnection and entityCommand to execute 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 does not return ObjectQuery, instead of the number of rows and columns of returned data. You can click this MSDN blog link to learn Entity SQL;

3. Native SQL

You can execute the original SQL statement:

using (var ctx = new SchoolDBEntities())    {        var studentName = ctx.Students.SqlQuery("Select studentid, studentname, standardId from Student where studentname='Bill'").FirstOrDefault<Student>();    }