Is it possible to execute a custom SQL query using EF7

Question

I decided to migrate one of my projects to Entity Framework 7 (rc1-final). I am targeting SQL Server and the EntityFramework.MicrosoftSqlServer package EntityFramework.MicrosoftSqlServer also installed.

I have only one problem: it seems that I cannot execute a custom SQL query to get some objects from the database.

An object

DatabaseFacade (which can be accessed through the DbContext.Database property) provides the ExecuteSqlCommand extension ExecuteSqlCommand (which returns nothing), but it does not provide the SqlQuery<T> method, which allows you to retrieve objects. Both methods were available in the good old EF 6, but the new EF 7 announces only the first.

So, is the SqlQuery<T> method still here (but moved / renamed) or completely removed from the new EF implementation?

Of course, it can be solved in some detail (using SqlCommand and its ExecuteReader method), but I prefer to avoid this.

Answer 1

EntityFramework.Relational exposes the following extension method:

 public static IQueryable<TEntity> FromSql<TEntity>( [NotNull] this IQueryable<TEntity> source, [NotNull] [NotParameterized] string sql, [NotNull] params object[] parameters) where TEntity : class 

So you can do it;

 myDatabaseContext.Customers.FromSql("SELECT * FROM Customers WHERE Name='p0'", "timothy"); 

Answer 2

To execute a custome SQL query in EF7, you can create a "fake" table that returns a structure similar to the structure of your SQL query.

Example:. Your request: "select aA, bB from A, B, where a.b_id = b.id". A fake table will contain 2 columns of type A, B. In your program, you can do this:

 var result = myDatabaseContext.FakeTables.FromSql("select aA, bB from A,B where a.b_id=b.id"); 

Answer 3

It looks like you can use the Sql method for the migrationBuilder object, which is passed to the Up and Down methods. At least in rc2.

 protected override void Up(MigrationBuilder migrationBuilder) { ... migrationBuilder.Sql("<Sql here>"); }