Native SQL Queries in Entity Framework
ADO.NET Entity Framework (EF) is powerful object-relational persistence framework. It has great capabilities for querying the database with LINQ but sometimes a custom native SQL could be more efficient way to execute a certain native SQL command or query directly at database level.
To execute native SQL query in EF you could use the following method of the ObjectContext:
objectContext.ExecuteStoreQuery<return-type>(native-SQL-query);
Native SQL could return nothing, a single value, a multiple data rows. You could map the returned data rows into classes. To achieve this you should define properties with the same name and corresponding type like the returned data rows from the native SQL query. Moreover, you could execute parameterized queries by passing the SQL command and its parameters to the ExecuteStoreQuery<T> method.
Developers rarely read text when a good example is available so I have prepared a fully functional example on the Northwind database in SQL Server which illustrates how to use native SQL queries in ADO.NET Entity Framework:
class ExecutingSQLQueriesExample
{
static void Main()
{
int customersCount = SelectCustomersCount();
Console.WriteLine("Customers count: {0}", customersCount);
Console.WriteLine("\nList of products:");
var products = SelectTop5ProductsIdAndName();
foreach (var product in products)
{
Console.WriteLine("{0}. {1}", product.ID, product.Name);
}
Console.WriteLine("\nList of employees from London:");
var employees = SelectEmployeeNamesByCountryAndCity("UK", "London");
foreach (var emp in employees)
{
Console.WriteLine(emp);
}
}
static int SelectCustomersCount()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
string nativeSQLQuery = "SELECT count(*) FROM dbo.Customers";
var queryResult = northwindEntities.ExecuteStoreQuery<int>(nativeSQLQuery);
int customersCount = queryResult.FirstOrDefault();
return customersCount;
}
static IEnumerable<ProductIdAndName> SelectTop5ProductsIdAndName()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
string nativeSQLQuery =
"SELECT TOP 5 ProductID as ID, ProductName as Name " +
"FROM dbo.Products " +
"ORDER BY ProductID";
ObjectResult<ProductIdAndName> products =
northwindEntities.ExecuteStoreQuery<ProductIdAndName>(nativeSQLQuery);
return products;
}
private static IEnumerable<string> SelectEmployeeNamesByCountryAndCity(
string country, string city)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
string nativeSQLQuery =
"SELECT FirstName + ' ' + LastName " +
"FROM dbo.Employees " +
"WHERE Country = {0} AND City = {1}";
object[] parameters = { country, city };
var employees = northwindEntities.ExecuteStoreQuery<string>(
nativeSQLQuery, parameters);
return employees;
}
class ProductIdAndName
{
public int ID { get; set; }
public string Name { get; set; }
}
}
12 Responses to “Native SQL Queries in Entity Framework”
yes thank a lot
I search this problem for 2-3 hour
and it help me
Zdraveyte,
It is a nice example, I spent near 2 hours of one issue, here are the details:
Please have on mind that:
class ProductIDAndName – properties types
and returned sql result table – column types
must match
if some property is type OBJECT from the class, it won’t fill with ExecuteStoreQuery, because first it looks for the TYPE and then for the NAME for matching purposes
Example: if ID was OBJECT type, it won’t work for SelectTop5ProductsIdAndName() function
SELECT COUNT(*) FROM ALL_USERS
that’s perfect! thank you
Great post. Saved me a lot of time and searching for a solution to calling my scalar functions. Thank you.
Hi, Very very good work, I’ve tried to use this aproach in silverlight, the sample is used is the first one
var products = SelectTop5ProductsIdAndName();
but from silverlight 5 using EF4 I got and error in this
foreach (var product in products) “products” Collection Expected
Can anyone help?
I am a Novice Who is trying to use EF.
I followed your example an tried to implement the same in my project.
The error is “‘EFTutorialConsole.SchoolDBEntities’ does not contain a definition for ‘ExecuteStoreCommand’ and no extension method ‘ExecuteStoreCommand’ accepting a first argument of type ‘EFTutorialConsole.SchoolDBEntities’ could be found (are you missing a using directive or an assembly reference?)”.
Below is the code snippet,
using (SchoolDBEntities ctx = new SchoolDBEntities())
{
//Inserting Student using ExecuteStoreCommand
int InsertedRows = ctx.ExecuteStoreCommand(“Insert into Student(StudentName,StandardId) values(‘StudentName1’,262)”);
//Fetching student using ExecuteStoreQuery
var student = ctx.ExecuteStoreQuery(“Select * from Student where StudentName = ‘StudentName1′”, null).ToList();
}
Can you please help me on this?
Maybe you have not included some assembly reference.
Probably, SchoolDBEntities derives DиContext. In this case you should do the following:
((IObjectContextAdapter) ctx).ObjectContext.ExecuteStoreCommand…
This was changed in the newer versions of EF, right.
Suppose I have multiple SQL select queries from different tables(no joining) and want to write a generic method where I pass only the sql query. Is it possible and how?
I believe you can create a stored procedure, return multiple results sets and map them in EF as described here: http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram.