Often when we use tools like Entity Framework we use it as it is designed out of the box, forgetting that you can optimize certain aspects of the underlying SQL engine through the use of our own SQL. You can interact with the database directly through SQL Queries, Queries with Parameters, Stored procedures…basically any valid SQL. What I find even more useful is the fact that you can interact with, and automatically bind to your Database Domain Objects. Or, if you prefer, you can execute SQL that has no return type.
I found a really good MSDN article that explains how to use SQL outside of LINQ within the Entity Framework environment. https://msdn.microsoft.com/en-us/data/jj592907.aspx
To summarize here, you can run a query from a DbSet domain object defined in your context. They use the example:
using (var context = new BloggingContext())
{
var blogs = context.Blogs.SqlQuery(“SELECT * FROM dbo.Blogs”).ToList();
}
context.Blogs is a DbSet of Blogs defined in your database context class. You could put any kind of where clause, or sorting, you want in the SQL Statement. The where and order by will be executed on the database server prior to returning the data to your application. Unlike adding filters and sorting in your link query which may be applied to the total set returned from the database after retrieving the list, this will be applied to the set in the database, and then returned to the client.
Well, if it’s that easy to run a SQL statement and get back an object, or list of the object, then stored procedures are even easier. The MSDN article demonstrates using an SP for you.
using (var context = new BloggingContext())
{
var blogs = context.Blogs.SqlQuery("dbo.GetBlogs").ToList();
}
And if you want to use parameters in your stored procedure the next example in MSDN demonstrates that as well.
using (var context = new BloggingContext())
{
var blogId = 1;
var blogs = context.Blogs.SqlQuery("dbo.GetBlogById @p0", blogId).Single();
}
When you want to return data from a query in a form that is not part of your domain model, you can use the SqlQuery method, found on the context.Database object. The MSDN example is quite simplistic, only returning a single data type. Here is the MSDN example for this method.
using (var context = new BloggingContext())
{
var blogNames = context.Database.SqlQuery
"SELECT Name FROM dbo.Blogs").ToList();
}
If you’d like to see an example of how to return more than a single column, or even use your own custom class that is not part of the database domain model, I found good examples at http://www.binaryintellect.net/articles/fbc96859-8a31-4735-baeb-7adcbc521b30.aspx.
You can also execute SQL Commands returning nothing from the database. This is done by executing a command against the context.Database object using the ExecuteSqlCommand method. Again, MSDN demonstrates this technique.
using (var context = new BloggingContext())
{
context.Database.ExecuteSqlCommand(
"UPDATE dbo.Blogs SET Name = 'Another Name' WHERE BlogId = 1");
}
Perhaps this may fill in some gaps you wish to add to your Entity Framework ORM Projects.
Cheers,
Ben