Querying with Entity Framework

You can build and execute queries using Entity Framework to fetch the data from the underlying database. EF 6 supports different types of queries which in turn convert into SQL queries for the underlying 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. As the name suggests, LINQ-to-Entities queries operate on the entity set (DbSet type properties) to access the data from the underlying database. You can use the LINQ method syntax or query syntax when querying with EDM. Visit LINQ Tutorials to learn LINQ step-by-step.

The following sample LINQ-to-Entities query fetches the data from the Student table in the database.

LINQ Method syntax:

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

LINQ Query syntax:

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

As you can see above, we created an instance of the context class SchoolDBEntities. It is recommended to instantiate it in using(), so that once it goes out of scope, then it automatically gets disposed.

Learn different types of LINQ-to-Entities projection query in the next chapter.

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 an ObjectContext to create a query using Entity SQL.

The following code snippet shows the same query result as the LINQ 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);
            }
    }               
}

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 queries using DbContext in Raw SQL Query chapter.