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.
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); } } }
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 queries using
DbContext
in Raw SQL Query chapter.
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.