Just trying to figure out how to execute a raw sql query with EF Core 18.104.22.168
I used to be able to create a connection and command like so:
var connection = _context.Database.GetDbConnection(); var command = connection.CreateCommand() command.CommandText = "..."; // etc etc
but that doesn't seem to be available anymore. I couldn't find anything obvious in the DbContext either
It looks like you can do the following:
var blogs = context.Blogs .FromSql("YOUR QUERY HERE") .ToList();
Alternatively if you don't have a model you want to use, you should be able to do:
context.Database.ExecuteSqlCommand("YOUR QUERY HERE");
There are a couple of limitations to be aware of when using raw SQL queries:
SQL queries can only be used to return entity types that are part of your model. There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.
The SQL query must return data for all properties of the entity type.
The column names in the result set must match the column names that properties are mapped to. Note this is different from EF6.x where property/column mapping was ignored for raw SQL queries and result set column names had to match the property names.
The SQL query cannot contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).
You can use Dapper liberary for complex raw sql.