Querying in Entity Framework:

You can build and execute queries using Entity Framework to fetch the data from underlying database. EF 6 supports different types of queries which 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


Language-Integrated Query (LINQ) is a powerful query language introduced in Visual Studio 2008. As name suggests, LINQ-to-Entities queries operates on entity set (DbSet type properties) 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.

The following sample LINQ-to-Entities query fetch 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")

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, create 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 get 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 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"))
    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);

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 chapter.