Raw SQL Query with Entity Framwork Core 1.1.1.0

entity-framework-core

Question

Just trying to figure out how to execute a raw sql query with EF Core 1.1.1.0

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

1
1
3/8/2017 3:55:02 AM

Accepted Answer

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");

Sources: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql https://msdn.microsoft.com/en-us/library/system.data.entity.database.executesqlcommand(v=vs.113).aspx

2
3/8/2017 11:51:42 PM

Popular Answer

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).

https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

You can use Dapper liberary for complex raw sql.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow