How to execute RAW SQL Query with EF Core?

Question

Basically the problem i have is that i want to run a query in a database that it's not a representation of my model.

This is my code to create the connection to another database:

public static OtherContext GetNewContextGeneric(string connectionString)
        {
            var builder = new DbContextOptionsBuilder();
            builder.UseSqlServer(connectionString);

            OtherContext db = new OtherContext(builder.Options);

            return db;
        }

And this is my code to execute the query:

public List<IQueryble> Query (string connectionString, string query)
        {
            try
            {
                using(var contextGeneric = ContextFactory.GetNewContextGeneric(connectionString))
                {
                    //I want something like this
                    return contextGeneric.Query(query).ToList();
                }
            }
            catch(System.Data.SqlClient.SqlException ex)
            {
                throw new SQLIncorrectException(ex);
            }
            catch(System.InvalidOperationException ex)
            {
                throw new NotImplementedException();
            }   
        }

Can somebody help me?

Answer 1

You can use DbDataReader:

using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "SELECT * From Make";
    context.Database.OpenConnection();
    using (var reader = command.ExecuteReader())
    {
        // Do something with result
        reader.Read(); // Read first row
        var firstColumnObject = reader.GetValue(0);
        var secondColumnObject = reader.GetValue(1);

        reader.Read(); // Read second row
        firstColumnObject = reader.GetValue(0);
        secondColumnObject = reader.GetValue(1);
    }
}

Here you can learn more how to read values from DbDataReader.

Alternatively you could use FromSql() method, but that works only on predefined DbSet of some entity, which is not the solution you wanted.

Answer 2

In the question you say:

Basically the problem i have is that i want to run a query in a database that it's not a representation of my model.

and then in comments you add:

Because i don't know how is created the database, i don't know what tables are in the database i want to insert the sql query

Well, if you don't know the database, then you cannot use Entity Framework, as it requires you to have a detailed knowledge of the database you are connecting to.
You should use plain ADO.NET (or Dapper if you want to map results back to a known class) for this.

Answer 3

Your use case of an external database can still be achieved using EF Core, there is no need to resort to ADO.Net, this solution is based on this generic solution for EF Core by @ErikEj. (Note that some functions and namespaces changed for EF Core 3, so and remain in .Net 5+ but the same generic concept can still be applied)

public static IList<T> Query<T>(string connectionString, string query, params object[] parameters) where T : class
{
    try
    {
        using (var contextGeneric = new ContextForQuery<T>(connectionString))
        {
            return contextGeneric.Query<T>().FromSql(query, parameters).ToList();
        }
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
        throw new SQLIncorrectException(ex);
    }
    catch (System.InvalidOperationException ex)
    {
        throw new NotImplementedException();
    }
}

private class ContextForQuery<T> : DbContext where T : class
{
    private readonly string connectionString;

    public ContextForQuery(string connectionString)
    {
        this.connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString, options => options.EnableRetryOnFailure());

        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<T>().HasNoKey();
        base.OnModelCreating(modelBuilder);
    }
}

Then the usage of this requires a concrete type definition, to add support for anonymous types is a fair bit more effort, but creating a concrete type for this is not a bad thing, the whole point here is to try you towards more declarative code styles as they enhance the readability and inspection of the code as well as providing documentation and other extended configuration like related entities.

public class NamedObject
{
    public int Id { get; set; }
    public string Name { get; set; }
}

...

var connectionString = "Insert your connection string here...";
var data = Query<NamedObject>(connectionString, "SELECT TOP 10 Id, FullName as Name FROM Employee");
foreach (var emp in data)
{
    Console.WriteLine(emp.Name);
}

---

Background

In EF 6 (.Net Framework) we could use DbContext.Database.FromSQL<T>() to execute ad-hoc SQL that would be automatically mapped to the specified type of T. This functionality was not replicated in EF Core because the result of FromSQL was inconsistent with the rest of EF, the result was a single use IEnumerable<T>. You could not further compose this query to Include() related entities nor could you add a filter to the underlying query.

In EF Core to Execute Raw SQL the type T that you want to return needs to be defined in the DbContext as a DbSet<T>. This set does not need to map to a table in the database at all, in fact since EF Core 2.1 we do not event need to specify a key for this type, it is simply a mechanism to pre-define the expected structure instead of executing Ad-Hoc requests on demand, it offers you the same functionality as the legacy FromSQL but also allows for you to define a rich set of navigation properties that would enable further composition of the query after your RawSQL is interpolated with the LINQ to SQL pipeline.

Once the type is defined in the context you simply call DbSet<T>.FromSqlRaw(). The difference is that now we have an IQueryable<T> that we can use to futher compose to include related entities or apply filters that will be evaluated within the database.

The solution posted in this response doesn't allow for composition, but uses the EF runtime in the expected sequence to give the same behaviours as the original EF 6 implementation.

In more recent versions of EF Core, and now in .Net 5+ the following subtle change need to be applied:

• Core 2.1: return contextGeneric.Query<T>().FromSql(query, parameters).ToList();
• Core 3+: return contextGeneric.Set<T>().FromSqlRaw(query, parameters).ToList();

Answer 4

You can use context.Database.ExecuteSqlRaw("select 1")

Don't forget to import the right namespace : using Microsoft.EntityFrameworkCore;

Answer 5

It worked like this:

private void SqlCommand (string connectionString, string query)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        var a = reader[0];
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
        }

Or

using (var connection = ContextFactory.GetNewContextGeneric(connectionString).Database.GetDbConnection())
                {
                    connection.Open();
                    DbCommand command = connection.CreateCommand();
                    command.CommandText = query;

                    using (var reader = command.ExecuteReader())
                    {
                        // Do something with result
                        reader.Read(); // Read first row
                        var firstColumnObject = reader.GetValue(0);
                        /*var secondColumnObject = reader.GetValue(1);

                        reader.Read(); // Read second row
                        firstColumnObject = reader.GetValue(0);
                        secondColumnObject = reader.GetValue(1);*/
                        connection.Close();
                        return firstColumnObject.ToString();
                    }
                }