EF Basic 15: Querying EDM

Querying with EDM

We have created EDM, DbContext, and entity classes in the previous sections. Here, you will learn the different types of queries an entity framework supports, which is in turn converted into SQL query for the underlaying database.

Entity framework supports three types of queries: 1) LINQ to Entities, 2) Entity SQL, and 3) Native SQL

LINQ to Entities:

Language-Integrated Query (LINQ) is a powerful query language introduced in Visual Studio 2008. You can use LINQ in C# or Visual Basic to query different data sources. LINQ-to-Entities operates on entity framework entities to access the data from the underlying database. You can use LINQ method syntax or query syntax when querying with EDM. Visit LINQ Tutorials to learn LINQ step-by-step.

LINQ Method syntax:

            
//Querying with LINQ to Entities 
using (var context = new SchoolDBEntities())
{
    var L2EQuery = context.Students.where(s => s.StudentName == "Bill");
        
    var student = L2EQuery.FirstOrDefault<Student>();

}
        

LINQ Query syntax:

using (var context = new SchoolDBEntities())
{
    var L2EQuery = from st in context.Students
                    where st.StudentName == "Bill"
                    select st;
   
    var student = L2EQuery.FirstOrDefault<Student>();
    }

First, you have to create an object of context class, which is SchoolDBEntities. You should initialize it in using() so that once it goes out of scope then it will automatically call Dispose() method of DbContext. In both the syntaxes above, context returns IQueryable.

Learn different types of LINQ to Entities projection query in the Projection Query section.

Entity SQL:

Entity SQL is another way to create a query. It is processed by the Entity Framework’s Object Services directly. It returns ObjectQuery instead of IQueryable.

You need ObjectContext to create a query using Entity SQL.

The following code snippet shows the same query result as L2E query above.

//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 as shown below:

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 doesn't return ObjectQuery. Instead, it returns the data in rows & columns.

Visit MSDN blog to learn Entity SQL.

Native SQL:

You can execute native SQL queries for a relational database as shown below:

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

Learn to execute raw sql query using DbContext in Raw SQL Query section.