Entity Framework - Native SQL Query


Entity Framework allows to query using LINQ with entity classes.

You can execute SQL queries using the following 3 types of SQL Query methods. 
1. SQL Query for a specific entity type which returns particular types of entities.
2. SQL Query for a primitive data (non-entity) type.
3. SQL commands.

1. SQL Query for a specific entity type which returns particular types of entities
The SqlQuery method on DbSet allows a raw SQL query to be written that returns entity instances. The returned objects will be tracked by the context as they would be if they were returned by a LINQ query. 
For example:

using (var context = new BooksContext()) 
{ 
    var books = context.Books.SqlQuery("SELECT * FROM dbo.Books").ToList(); 
}

Loading entities from stored procedures
You can use DbSet.SqlQuery to load entities from the results of a stored procedure. 

For example:

using (var context = new BooksContext()) 
{ 
    var books = context.Books.SqlQuery("dbo.GetBooks").ToList(); 
}

Pass parameters to a stored procedure using the following syntax:

For example:

using (var context = new BooksContext()) 
{ 
    var BookId=5;
    var books = context.Books.SqlQuery("dbo.GetBooks @Id" , BookId).ToList(); 
}

2. SQL Query for a primitive data (non-entity) type
SQL query returning instances of any type, including primitive types, can be created using the SqlQuery method on the Database class. 
The results returned from SqlQuery on Database will never be tracked by the context even if the objects are instances of an entity type.

For example:

using (var context = new BooksContext()) 
{ 
    var bookNames = context.Database.SqlQuery("SELECT BookName FROM dbo.Books").ToList(); 
}

3. SQL commands to the database
Non-query commands can be sent to the database using the ExecuteSqlCommand method on Database. Any changes made to data in the database using ExecuteSqlCommand are opaque to the context until entities are loaded or reloaded from the database.

For example:

using (var context = new BooksContext()) 
{ 
   //Update book
   int iRowUpdated = context.Database.ExecuteSqlCommand("UPDATE dbo.Books SET BookName = 'New Name' WHERE BookId = 5"); 

   //Insert book
   int iRowInserted = context.Database.ExecuteSqlCommand("insert into Books(BookName) values('New Book')");
   
   //Delete book
   int iRowDeleted = context.Database.ExecuteSqlCommand("Delete from Books where BookId=3");
}

 

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