Is it possible to execute custom SQL query using EF7

Question

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

I have only one problem: it seems like I can't execute custom SQL query in order to fetch some objects from DB.

DatabaseFacade object (which can be accessed via DbContext.Database property) provides an extension method ExecuteSqlCommand (that returns nothing), But it does not provide SqlQuery<T> method that allows to fetch objects. Both methods were available in good old EF 6, but new EF 7 declares only first one.

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

Of course it is absolutely possible to solve it in a verbose way (using SqlCommand and its ExecuteReader method) but I prefer to avoid it.

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 this;

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 which structure like structure of your SQL query returns.

Example: Your query is: "select a.A, b.B from A,B where a.b_id=b.id". The fake table will contain 2 column like A,B. In your program, you can do this:

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