Is it possible to run native SQL with Entity Framework?

Precise handling and considerations

Fetching query results

If you expect to receive a result set, use DbContext.Database.SqlQuery<T>(),

// Calling Doctor Data! Paging Doctor Data! 🩺🔍
var users = dbContext.Users.SqlQuery("SELECT * FROM Users WHERE Age > 18").ToList();


Safety and SQL injection

Always parameterize your queries to avoid SQL injection, especially when you're considering user inputs. EF helps us by simplifying parameterization:

// Safety first! No unwanted guests, please. 🚧🔒
var user = dbContext.Users.FromSqlRaw("SELECT * FROM Users WHERE Username = {0}", username).FirstOrDefault();


Executing non-query commands

For commands that do not return results, opt for ExecuteSqlRaw() or ExecuteSqlInterpolated(). The latter gives interpolated SQL syntax for added safety:

// We're cleaning house and old users need to step outside! 🏠🧹
dbContext.Database.ExecuteSqlInterpolated($"DELETE FROM Users WHERE LastLogin < {thresholdDate}");


Effortlessly blending native SQL with EF

Advanced SQL scenarios

Need to perform operations like working with XML or complex stored procedures? Incorporate native SQL for your expressions.

Handling performance requirements

In cases where performance is vital or for bulk operations, native SQL commands sometimes can outperform your everyday ORM operations.

Maintaining EF context awareness

Ensure your EF context is aware of changes in your database due to your SQL queries. Manage this via context.ChangeTracker.QueryTrackingBehavior appropriately.

Leverage native SQL selectively

Stick to EF's in-built functionalities wherever possible to reap its best benefits. Keep native SQL as your last line of defense for when EF's features are not enough.

Connection handling with native SQL

Direct execution and commands

Running SQL commands directly using EF necessitates caution while handling database connection states. EF manages connection states for all LINQ operations automatically, but for native SQL, you need to open and close connections manually:

// Note the special care we take with the connection. It's like a toddler. 🧒🍼
using (var context = new MyDbContext())
{
    var connection = context.Database.GetDbConnection();
    connection.Open();
    // Our native SQL's playground is ready!
    connection.Close();
}


The endless possibilities with native SQL

Building a SQL repository

Consider creating a generic repository to encapsulate your common native SQL actions. This way, the rest of your application gets to live happily, unaware of the complexity below!

CRUD with ObjectSet

If you're rollin' with an older version of EF, and using ObjectSet for CRUD operations, you can use native SQL for that too:

// Aged but not obsolete. Your seniors can still show you how it's done. 👴💪
var numberAffected = context.ObjectSet.ExecuteStoreCommand("DELETE FROM Users WHERE Age < 18");


Working with tricky data types

EF overcomes its limitations with certain data types (like XML) by embedding native SQL, allowing us to handle them without losing EF for the rest of your application.