Entity Framework - Queries with EDM

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. LINQ to Entities converts Language-Integrated Queries (LINQ) queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ.

LINQ to Entities provides Language-Integrated Query (LINQ) support that enables engineers to write queries against the Entity Framework conceptual model. Queries against the Entity Framework are represented by command tree queries, which execute against the object context. 


LINQ to Entities queries can be composed in two different syntaxes: query expression syntax and method-based query syntax. 

Query Expression Syntax

Query expressions are declarative query syntax. This syntax enables enginners to write queries in a high level language that is formatted similar to Transact-SQL. By using query expression syntax, we can perform complex filtering, ordering, and grouping operations on data sources with minimal code. 

Method Based Query Syntax

The other way to compose LINQ to Entities queries is method-based queries. The method-based query syntax is a sequence of direct method calls to LINQ operator methods, passing lambda expressions as parameters.

Entity SQL

Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships. ObjectContext is needed to create a query using Entity SQL.

string sqlString = "SELECT e.Name FROM Employees as e WHERE e.EmpID = 10";
var objOca = (oca as IObjectContextAdapter).ObjectContext;
ObjectQuery<Employees> employee = objOca.CreateQuery<Employees>(sqlString);
Employees newEmployees = employee.First<Employees>();


EntityConnection and EntityCommand to execute Entity SQL

using (var conn = new EntityConnection("name=EFSourceDBEntities"))
     EntityCommand cmd = conn.CreateCommand();
     cmd.CommandText = "SELECT e.Name FROM Employees as e WHERE e.EmpID = 10";
     Dictionary<int, string> objDict = new Dictionary<int, string>();
     using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
        int a = 0;
        String b = String.Empty;
        while (rdr.Read())
            a = rdr.GetInt32(0);
            b = rdr.GetString(1);
            objDict.Add(a, b);

Native SQL

Writing query with entity classes using LINQ is called Native SQL.

using (var objEFSource = new EFSourceDBEntities())
  var empName = objEFSource.Employees.SqlQuery("Select EmpID, Name, Designation from Employees where EmpID=10").FirstOrDefault<Employees>();


Entity SQL Overview

Praesent mattis

Pellentesque viverra vulputate enim. Aliquam erat volutpat. Pellentesque tristique ante ut risus. Quisque dictum. Integer nisl risus, sagittis convallis, rutrum id, elementum congue, nibh. Suspendisse dictum porta lectus. Donec placerat odio vel elit.

Read More