Menu

 

Thoughts on Software Engineering

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; }
  }
}
Previews (51,448), Views (43,727), Comments (11)

11 Responses to “Native SQL Queries in Entity Framework”

  1. juhaani says:

    yes thank a lot
    I search this problem for 2-3 hour
    and it help me

  2. Emin says:

    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

  3. diogo says:

    that’s perfect! thank you

  4. Great post. Saved me a lot of time and searching for a solution to calling my scalar functions. Thank you.

  5. JoseM says:

    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?

  6. Somnath says:

    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?

  7. shoma13 says:

    Probably, SchoolDBEntities derives DиContext. In this case you should do the following:
    ((IObjectContextAdapter) ctx).ObjectContext.ExecuteStoreCommand…

  8. SD says:

    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?

RSS feed for comments on this post. TrackBack URL

LEAVE A COMMENT