Executing SQL Query in Entity Framework

In this blog, I will explain to you how we can use different types of SQL Query methods.

 

Introduction

 

In this post, we will see how to execute native SQL queries, using DB Context.

 

You can execute SQL queries using the following types of SQL Query methods. 

1. SQL Query for a specific entity type.
2. SQL Query for a primitive data type.
3. SQL commands.

Let’s start.

 

SQL Query for a specific entity type

 

We can use SQLQuery() method to write SQL queries which return an entity object.

 

Example:

//DbContext
DbPersonnesEntities db = new DbPersonnesEntities();

var customerList = db.Customers.SqlQuery("Select * From Customers").ToList<Customers>();

The code, shown above, selects all the data rows from customer's table. I would like to add that columns returned by SQLQuery must match the property of an entity type, otherwise it will throw an exception.

 

SQL Query for a primitive data type

 

In this type, the SQL Query() method will return any primitive types created using the SQL Query method.

 

Example

//DbContext
DbPersonnesEntities db = new DbPersonnesEntities();

int customerId = db.Database.SqlQuery<int>("Select customerId From Customers where customerName='MAHDI'").FirstOrDefault<int>();

SQL commands

 

We can use ExcecuteSqlCommand() method to ensure the CRUD operation.

 

Example

//DbContext
DbPersonnesEntities db = new DbPersonnesEntities();
int result = db.Database.ExecuteSqlCommand("delete from Customers where customerId = 100");

As you can see, code displayed above will delete a customer that has 100 as customerId.