Using raw SQL with Entity Framework

When using Entity Framework, most people use LINQ-to-Entities to query the database. LINQ-to-Entities is easier and nicer to use than raw SQL. However, there are times you might want to use raw SQL instead, such as using an existing stored procedure, control the generated SQL for complex queries (LINQ-to-Entities can be very slow for complex queries) or using SQL functions not yet supported by Entity Framework.

In this post, I’m going to show how to use raw SQL with Entity Framework. Let’s say we have these models

    public class Blog
    {
        [Key]
        public Guid BlogId { get; set; }
        public string Name { get; set; }

        public ICollection<Post> Posts { get; set; } = new List<Post>();
    }

    public class Post
    {
        [Key]
        public Guid PostId { get; set; }
        public string Title { get; set; }
        public DateTime PublishedDate { get; set; }

        public Guid BlogId { get; set; }
        public Blog Blog { get; set; }
    }

And that we need to query posts by title or by the blog’s name. We have a PostsSearchParameter class that looks like this

    public class PostsSearchParameter
    {
        public string? PostTitle { get; set; }
        public string? BlogName { get; set; }
        public DateTime PublishedDate { get; set; }
    }

Then we have a method to search the posts using raw SQL as followed

    public async Task<IEnumerable<Post>> SearchPosts(PostsSearchParameter searchParameter)
    {
        var sql = new StringBuilder(@"
        select pt.*
        from dbo.Posts       pt
        inner join dbo.Blogs bl on bl.BlogId = pt.BlogId
        where pt.PublishedDate >= @PublishedDate");

        var parameters = new List<SqlParameter>
        {
            new SqlParameter("@PublishedDate", searchParameter.PublishedDate)
        };

        if (!string.IsNullOrWhiteSpace(searchParameter.BlogName))
        {
            sql.Append(" and bl.Name like @BlogName");
            parameters.Add(new SqlParameter("@BlogName", $"%{searchParameter.BlogName}%"));
        }

        if (!string.IsNullOrWhiteSpace(searchParameter.PostTitle))
        {
            sql.Append(" and pt.Title like @PostTitle");
            parameters.Add(new SqlParameter("@PostTitle", $"%{searchParameter.PostTitle}%"));
        }

        IQueryable<Post> posts = _bloggingContext.Posts
            .FromSqlRaw(sql.ToString(), parameters.ToArray())
            .Include(x => x.Blog);

        return await posts.ToListAsync();
    }

That’s it. It returns the posts with the Blog included.

One issue I ran into when using FromSqlRaw the first time was I got the below exception.

"The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects"

This exception happens if you have reference to System.Data.SqlClient instead of Microsoft.Data.SqlClient. Make sure you reference the right one.