Execute Raw SQL in Entity Framework Core

From time to time your ORM is going to let you down in some way. No matter how good an ORM is there will always be some situations that the queries produced will not meet a performance requirement. Entity Framework Core will not be any different. This post is going to cover a couple of ways to execute raw SQL if the need arises.

Table Definition

The examples in this post are dealing with a Contacts table that contains columns for Id, Name, Address, City, State and Zip code. The following Entity Framework Core  DbContext is defined for access to the Contacts table and is accessed via  _context in all of the examples.

DbSet FromSql

From any DbSet there is a  FromSql function that will take raw SQL or a stored procedure that will be used instead of the SQL Entity Framework would have generated. The following example is using  FromSql to load contacts based on the above  DbContext and  DbSet.

FromSql takes a query followed by a list of parameters that should be used in the query’s execution. For example in the above  @p0 maps to  name1 and  @p1 maps to  name2. I recommend always using parameters instead of manually adding the filters to the SQL string in order to help prevent SQL injection attacks. Note that the  @p followed by increasing numbers seems to be the only parameters that  FromSql works with.

The great thing about this method is just like querying with via Entity Framework with LINQ execution is delayed and it returns an  IQueryable so more filters can be added on as needed.

ADO.NET

Another options it to grab a connection from the  DbContext using Database.GetDbConnection() and use ADO.NET to perform what ever operations are needed. Here is an example of connecting to the database and getting the name of the first contact. This is not a good example of a query that would need to be run this way, but provides a simple example of how a query would be executed.

Since this is down to ADO.NET you can do pretty much anything that is needed including readers, adapters, data tables, etc. Hopefully the need for this option will be minimal, but it is important to know that it exists.

4 thoughts on “Execute Raw SQL in Entity Framework Core”

    1. Jon,

      As far as I know the issue you were having would still be a problem. EF is not going to know that it should update its in memory data based on some raw SQL that was executed. When using the FromSql function should be fine, but not the ADO level functions.

      I am also not seeing a reload function like the one you used in your SO answer.

Leave a Reply

Your email address will not be published. Required fields are marked *