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.

score 6 · Accepted Answer · answered Dec 17 '15 at 09:00

6

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

answered Dec 17 '15 at 09:00

Stafford Williams

9,696
8
50
101

3

Thanks, but I know about that method already, but, unfortunately it needs IQueryable as input parameter. As I understand that method should be used together with some DbSet that, in turn, should be defined inside DbContext. My case is little different - I want to get objects which not present in my DbContext (i.e. just execute query and create object of type T for each returned row). – rtf_leg Dec 17 '15 at 10:19
In that case just use the underlying SqlCommand like in [this answer](http://stackoverflow.com/a/34191033/469777). – Stafford Williams Dec 17 '15 at 10:22
Actually I am using that approach now (Command + ExecuteReader). It is verbose in compare with missing SqlQuery, but it, of course, works. Btw, thank you for your time and for your answer - it is definitely useful - appreciate it. – rtf_leg Dec 17 '15 at 22:55

score 2 · Answer 2 · answered Sep 29 '16 at 07:37

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

score 0 · Answer 3 · answered Jun 05 '16 at 13:15

0

It looks like you can use the Sql method on the migrationBuilder object that is passed into the Up and Down methods. At least in rc2.

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

answered Jun 05 '16 at 13:15

Des Horsley

1,858
20
43

1

This method 'migrationBuilder.Sql' doesn't seem to exist in the EF Core RTM version. Anyone know of the equivalent? – Reza Jul 16 '16 at 19:05

Is it possible to execute custom SQL query using EF7

3 Answers3