How to execute raw SQL query using Entity Framework without having to use a model?

I am trying to learn C# ASP.NET MVC 5. And I am trying to use Entity Framework for everything I do.

However, I need to run a raw SQL query and return the results into an array.

Here is what I have done so far.

I created my context class which allows me to connect to a server and it also allows me to change the database at run time.

Here is my context class

using ScripterEngine.Models;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace ScripterEngine.DataAccessLayer
{
    public class BaseContext : DbContext
    {
        protected string connectionName;
        public DbSet<Campaign> Campaign { get; set; }

        /**
         * Created the connection to the server using the giving connection string name
         * 
         * @param connName
         */
        public BaseContext(string connName = "BaseConnection")
            : base(connName)
        {
            connectionName = connName;
        }

        /**
         * Changes the default database
         * 
         * @param databaseName
         */
        public BaseContext setDatabase(string databaseName)
        {
            var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);

            //change the database before creating the new connection
            builder.InitialCatalog = databaseName;

            string sqlConnectionString = builder.ConnectionString;

            return new BaseContext(sqlConnectionString);
        }
    }
}

And how to make the connection here is what I do

BaseContext db1 = new BaseContext("server1");
var db1New = db1.setDatabase("someTableName");
string tableName = "SomeTableName";

var results = db1New.Database.SqlQuery("SELECT LOWER(column_name) AS column_name FROM information_schema.columns WHERE table_name = @tableName", tableName).ToArray();

This throws an error

The type arguments for method ‘System.Data.Entity.Database.SqlQuery(string, params object[])’ cannot be inferred from the usage. Try specifying the type arguments explicitly. C:.NET ProjectsScripterEngineScripterEngineControllersCampaignController.cs 42 27 ScripterEngine

How can I execute this raw query?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Specify string as the type argument.

var results = db1New.Database.SqlQuery<string>("SELECT LOWER(column_name) AS column_name FROM information_schema.columns WHERE table_name = @p0", tableName).ToArray();
                                       ^^^^^^

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0