Raw SQL Queries in .NET

This tutorial explores how to execute raw SQL queries within .NET applications, providing flexibility and control over your database interactions.

Introduction to Raw SQL

Object-Relational Mappers (ORMs) like Entity Framework provide high-level abstractions for database operations. However, there are scenarios where direct SQL execution is necessary or more efficient. This might include complex queries, performance optimizations, or leveraging specific database features.

Executing Raw SQL with Entity Framework Core

Entity Framework Core (EF Core) provides straightforward methods to execute raw SQL commands. These methods allow you to pass SQL strings directly to the database provider.

Executing Non-Query SQL (INSERT, UPDATE, DELETE)

For commands that do not return a result set (e.g., INSERT, UPDATE, DELETE), you can use the ExecuteSqlRaw or ExecuteSqlInterpolated methods.

Using ExecuteSqlRaw

ExecuteSqlRaw allows you to pass a SQL string with parameter placeholders.

using (var context = new MyDbContext())
{
    var userId = 1;
    var newEmail = "new.email@example.com";

    // Using parameter placeholders (safer)
    context.Database.ExecuteSqlRaw(
        "UPDATE Users SET Email = {0} WHERE Id = {1}",
        newEmail, userId);
}
        

Using ExecuteSqlInterpolated

ExecuteSqlInterpolated is a more convenient way to embed parameters directly into the interpolated string. EF Core will automatically create parameters for these values, mitigating SQL injection risks.

using (var context = new MyDbContext())
{
    var productId = 101;
    var newPrice = 29.99m;

    // Using interpolated string with parameters
    context.Database.ExecuteSqlInterpolated($"UPDATE Products SET Price = {newPrice} WHERE Id = {productId}");
}
        

Executing SQL Queries that Return Data (SELECT)

To retrieve data from raw SQL queries, you can use FromSqlRaw or FromSqlInterpolated.

Using FromSqlRaw

This method is used to execute a SQL query and project the results into an entity type.

using (var context = new MyDbContext())
{
    var searchKeyword = "Analytics";

    // Retrieving data with parameters
    var products = context.Products.FromSqlRaw(
        "SELECT * FROM Products WHERE Name LIKE '%' + {0} + '%'",
        searchKeyword);

    foreach (var product in products)
    {
        Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
    }
}
        

Using FromSqlInterpolated

Similar to ExecuteSqlInterpolated, this method provides a cleaner syntax for embedding parameters.

using (var context = new MyDbContext())
{
    var minPrice = 50.00m;
    var maxPrice = 150.00m;

    // Retrieving data with interpolated parameters
    var expensiveProducts = context.Products.FromSqlInterpolated($@"
        SELECT * FROM Products
        WHERE Price BETWEEN {minPrice} AND {maxPrice}");

    foreach (var product in expensiveProducts)
    {
        Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
    }
}
        

Executing Raw SQL with Dapper

Dapper is a popular micro-ORM that excels at executing raw SQL with excellent performance. It simplifies the process of mapping query results to C# objects.

Installation

Install Dapper via NuGet Package Manager:

Install-Package Dapper
        

Basic Usage

using System.Data.SqlClient;
using Dapper;

// Assuming you have a connection string
string connectionString = "Your_Connection_String";

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    // Querying for a single object
    var user = connection.QuerySingleOrDefault<User>(
        "SELECT Id, Name, Email FROM Users WHERE Id = @UserId",
        new { UserId = 1 });

    if (user != null)
    {
        Console.WriteLine($"User: {user.Name}, Email: {user.Email}");
    }

    // Querying for a list of objects
    var products = connection.Query<Product>(
        "SELECT Id, Name, Price FROM Products WHERE Price > @MinPrice ORDER BY Price DESC",
        new { MinPrice = 100.00m });

    foreach (var product in products)
    {
        Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
    }

    // Executing non-query
    int affectedRows = connection.Execute(
        "UPDATE Orders SET Status = @NewStatus WHERE OrderId = @Id",
        new { NewStatus = "Shipped", Id = 500 });

    Console.WriteLine($"Rows affected by update: {affectedRows}");
}

// Example User and Product classes (for demonstration)
public class User { public int Id { get; set; } public string Name { get; set; } public string Email { get; set; } }
public class Product { public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } }
        

When to Use Raw SQL

• Performance Critical Scenarios: When ORM overhead is unacceptable or specific query tuning is required.
• Complex Queries: Stored procedures, advanced JOINs, or database-specific functions that are difficult to express in LINQ.
• Legacy Databases: Interfacing with existing databases where schema design might not be ideal for ORMs.
• Bulk Operations: Efficiently performing large batch inserts, updates, or deletes.
• Reporting and Analytics: Generating complex reports that often benefit from direct SQL.

Best Practices for Raw SQL

• Parameterization: Always use parameterized queries to prevent SQL injection vulnerabilities. This is the most critical security practice.
• Readability: Keep SQL queries concise and well-formatted within your C# code. Consider using a separate file for complex SQL if applicable.
• Maintainability: Document your raw SQL queries, especially complex ones, to explain their purpose and logic.
• Database Agnosticism (if possible): Write SQL that is as portable as possible across different database systems if you anticipate switching providers. However, for specific features, you might need to write database-specific SQL.
• Testing: Thoroughly test your raw SQL queries against your database to ensure correctness and performance.