Raw store access APIs

[divega]

In previous versions of EF we had handy "raw SQL" methods such as SqlQuery and ExecuteSqlCommand that allowed to drop down to the underlying relational provider to execute SQL queries directly (i.e. bypassing the EF mapping) but still take advantage of some of the services provided by EF, e.g. connection management, transaction handling, object materialization and change tracking.

In EF7 since we support relational as well as non relational providers we should figure out how to extend this concept to specific providers through a consistent pattern, e.g. for Azure Table Storage it probably makes sense to provide access to the underlying objects from the .NET storage client library, while for relational stores being able to access the underlying connection and having something like SqlQuery and ExecuteSqlCommand defined as extension methods probably makes sense.

[RickyLin]

hmm, any progress on this?

[rowanmiller]

@RickyLin - We haven't started on this yet. It's a fairly isolated feature, so it will be something we work on a little later in the cycle.

[mikary]

We have added a FromSql method for relational DbSet<T>s that allows for materialization, change tracking, and additional querying off of a custom SQL statement. See #1845 and #1871 for more details and examples

[NicoJuicy]

In the meantime, you could probably use a extension method like this

public static int ExecuteSqlCommand(this RelationalDatabase database, string sql)
    {
        using (var connection = database.Connection)
        {
            using (var command = connection.DbConnection.CreateCommand())
            {
                command.CommandText = sql;
                return command.ExecuteNonQuery();
            }
        }
    }

[bricelam]

@NicoJuicy Looks eerily familiar... 😉

[mikary]

@rowanmiller most of the functionality in this issue is covered by a combination of FromSql, #1862, and #1863

The only other facet is using a raw command on a non-relational store. Should we still keep this issue open as a reminder on the backlog, or is this something we can close?

[rowanmiller]

@mikary yes I agree that those cover the remaining cases from this item

[rowanmiller]

non-relational providers can do whatever makes sense for that particular store, so I don't think we need a general item.

[kant2002]

Would be good to provide async version of #1863 (ExecuteSqlCommandAsync) as it was earlier. It's strange to have all API's Async but for this one only sync version.

[rowanmiller]

Clearing milestone so that we can discuss in triage. I think we have done almost everything here though we should discuss the async request from @kant2002

[divega]

@rowanmiller agreed. The lack of async ExecuteSqlCommand is an oversight (thanks @kant2002!). I will just re-open #1863 and assign it to @mikary to track that. We can probably just close this one now.