Using raw SQL queries and stored procedures

Entity Framework Core (EF Core) provides an abstraction layer over database interactions, simplifying data access in .NET applications. However, there are situations where bypassing this abstraction and directly executing raw SQL queries or stored procedures becomes necessary or advantageous. This tutorial explores how to execute raw SQL queries and stored procedures using EF Core.

Introduction to Raw SQL Queries

Raw SQL queries allow you to directly execute SQL statements against the database. This is useful for complex queries, performance optimization, or when EF Core's LINQ provider doesn't fully support a specific database feature.

EF Core provides methods like FromSqlRaw and ExecuteSqlRaw to interact with the database using raw SQL.

Executing a Raw SQL Query with FromSqlRaw

FromSqlRaw is used to execute a SQL query and map the results to an entity type. In this example, we're retrieving all blogs whose URL contains 'example.com'.

1. Define your DbContext and Entities (Blog and Post in this case).
2. Use context.Blogs.FromSqlRaw() to execute the SQL query. The result is then converted to a list of Blog objects.
3. Iterate through the resulting blogs and print their details.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options) { }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Example
{
    public static void Run(BloggingContext context)
    {
        // Execute raw SQL to retrieve blogs
        var blogs = context.Blogs
            .FromSqlRaw("SELECT * FROM Blogs WHERE Url LIKE '%example.com%'")
            .ToList();

        foreach (var blog in blogs)
        {
            Console.WriteLine($"BlogId: {blog.BlogId}, Url: {blog.Url}");
        }
    }
}

Executing a Raw SQL Query with Parameters

Parameterized queries are crucial for preventing SQL injection vulnerabilities. EF Core allows you to include parameters in your raw SQL queries.

1. Pass the parameter as an argument to FromSqlRaw.
2. EF Core will handle parameterization, ensuring the value is properly escaped.

Important: Always use parameterized queries to avoid SQL injection attacks.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options) { }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Example
{
    public static void Run(BloggingContext context)
    {
        // Execute raw SQL with parameters to prevent SQL injection
        string urlFilter = "%example.com%";
        var blogs = context.Blogs
            .FromSqlRaw("SELECT * FROM Blogs WHERE Url LIKE {0}", urlFilter)
            .ToList();

        foreach (var blog in blogs)
        {
            Console.WriteLine($"BlogId: {blog.BlogId}, Url: {blog.Url}");
        }
    }
}

Executing a Raw SQL Command with ExecuteSqlRaw

ExecuteSqlRaw is used to execute SQL commands that don't necessarily return entities, such as UPDATE, INSERT, or DELETE statements. It returns the number of rows affected by the command.

1. Use context.Database.ExecuteSqlRaw() to execute the SQL command.
2. Pass any necessary parameters as arguments.
3. Check the return value (rowsAffected) to determine the success of the operation.

using Microsoft.EntityFrameworkCore;

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options) { }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Example
{
    public static void Run(BloggingContext context)
    {
        // Execute raw SQL to update a blog's URL
        string newUrl = "https://newexample.com";
        int blogId = 1;  // Assuming BlogId 1 exists
        int rowsAffected = context.Database
            .ExecuteSqlRaw("UPDATE Blogs SET Url = {0} WHERE BlogId = {1}", newUrl, blogId);

        Console.WriteLine($"Rows affected: {rowsAffected}");
    }
}

Calling Stored Procedures

Stored procedures are precompiled SQL statements stored in the database. They can encapsulate complex logic and improve performance. EF Core allows you to call stored procedures using FromSqlRaw or ExecuteSqlRaw.

Note: You need to create the stored procedure in your database first. For example:

CREATE PROCEDURE dbo.GetBlogsByUrlLike
    @UrlLike NVARCHAR(200)
AS
BEGIN
    SELECT * FROM Blogs WHERE Url LIKE @UrlLike
END

1. Use FromSqlRaw to call the stored procedure. The stored procedure should return a result set that maps to an entity.
2. Pass any necessary parameters as arguments to FromSqlRaw.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options) { }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Example
{
    public static void Run(BloggingContext context)
    {
        // Call a stored procedure that returns blogs
        var blogs = context.Blogs
            .FromSqlRaw("EXECUTE dbo.GetBlogsByUrlLike {0}", "%example.com%")
            .ToList();

        foreach (var blog in blogs)
        {
            Console.WriteLine($"BlogId: {blog.BlogId}, Url: {blog.Url}");
        }
    }
}

Concepts Behind the Snippet

The snippets demonstrate the core principles of using raw SQL and stored procedures with EF Core:

• Direct Database Interaction: Bypassing the LINQ provider for specific tasks.
• Parameterized Queries: Protecting against SQL injection.
• Mapping Results: Mapping the result sets from SQL queries or stored procedures to entity types.

Real-Life Use Case

Imagine you have a complex reporting requirement that is difficult to achieve using EF Core's LINQ provider. You could create a stored procedure that performs the complex calculations and then call that stored procedure from your C# code using FromSqlRaw, mapping the results to a DTO (Data Transfer Object).

Another common use case is bulk data import. Using raw SQL with the SqlBulkCopy class often provides significantly better performance than inserting records one-by-one through EF Core.

Best Practices

• Always use parameterized queries: Prevent SQL injection attacks.
• Use raw SQL sparingly: Prefer LINQ when possible for type safety and maintainability.
• Keep SQL concise and well-documented: Raw SQL can be harder to maintain than LINQ queries.
• Handle database errors: Wrap your raw SQL calls in try-catch blocks.
• Test thoroughly: Ensure your raw SQL queries and stored procedures function correctly.

Interview Tip

Be prepared to discuss the trade-offs between using LINQ and raw SQL. Explain when you would choose one over the other, and why. Highlight the importance of security when using raw SQL queries, particularly the use of parameterized queries to prevent SQL injection attacks. Also, be prepared to discuss scenarios where stored procedures provide performance benefits.

When to Use Them

Use raw SQL queries or stored procedures when:

• You need to optimize performance for specific queries.
• You require database-specific features not supported by EF Core's LINQ provider.
• You have existing stored procedures you want to leverage.
• You need to perform bulk operations efficiently.
• LINQ queries become overly complex and difficult to understand.

Memory Footprint

The memory footprint of using raw SQL queries and stored procedures depends on the size of the result set. If you're retrieving a large number of rows, consider using techniques like pagination to reduce memory consumption.

Alternatives

Alternatives to raw SQL and stored procedures include:

• EF Core's LINQ Provider: Prefer LINQ when possible for type safety and maintainability.
• Compiled Queries: Can improve the performance of frequently executed LINQ queries.
• Database Views: Can simplify complex queries.

Pros

• Performance: Potential for significant performance gains in specific scenarios.
• Flexibility: Access to database-specific features.
• Existing Code: Leverage existing stored procedures.

Cons

• Security Risks: SQL injection vulnerabilities if not handled carefully.
• Maintainability: Raw SQL can be harder to maintain than LINQ queries.
• Type Safety: Loss of type safety compared to LINQ.
• Database Dependency: Tightly coupled to the specific database system.