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