MVC error- An exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Question

I`m new to MVC (and asp.net). when I execute the code this error message occurs:

 An exception of type 'System.Data.SqlClient.SqlException' occurred in   
 EntityFramework.SqlServer.dll but was not handled in user code
 Additional information: Invalid object name 'delays_table'.

This is my Controller:

namespace public_transportaion.Controllers
{
public class StatisticsController : Controller
{
    private StatisticDBContext db = new StatisticDBContext();

    // GET: Statistics
    public ActionResult Index()
    {
        string query = "SELECT agency_name, COUNT(*) "
   + "FROM delays_table "
   + "WHERE late_type = 'miss' "
   + "GROUP BY agency_name";

        IEnumerable<Statistic> data = db.Database.SqlQuery<Statistic>(query);

        return View(data.ToList());

    }

This is my Model:

namespace public_transportaion.Models
{
public class Statistic
{
    public int ID { get; set; }
    public string agency_name { get; set; }
    public int missCount { get; set; }
}

public class StatisticDBContext : DbContext
{
    public DbSet<Statistic> Statistics { get; set; }
}
}

And here is the database information:

CREATE TABLE [dbo].[delays_table] 
[ID]                     INT            IDENTITY (1, 1) NOT NULL,
[tripID]                 BIGINT         NOT NULL,
[planned_arrival_timeID] DATETIME       NOT NULL,
[route_id]               BIGINT         NOT NULL,
[agency_name]            NVARCHAR (MAX) NULL,
[startDate]              DATETIME       NOT NULL,
[endDate]                DATETIME       NOT NULL,
[late_type]              NVARCHAR (MAX) NULL,
[duration]               REAL           NOT NULL,
CONSTRAINT [PK_dbo.delays_table] PRIMARY KEY CLUSTERED ([ID] ASC)

I have also tried to use ‘FROM dbo.delays_table ‘ or ‘FROM delays_table (dbo)’ in the query, but it did not help.

Thank you.

Answer 1

It sounds like one of two things could be occurring here: an issue with your connection string pointing to the wrong database or your SQL query not being able to discern that delays_table is in fact a table:

Check Your Connection String

Your Connection String for your Data Context is pointing to a different database than the one that you are expecting (consider checking your web.config file to ensure that it is correct):

<connectionStrings>
    <add name="DefaultConnection" connectionString="{check-me}" providerName="System.Data.SqlClient" />
</connectionStrings>

You'll primarily be looking to check that your server and database names are correct :

Data Source={your-database-server};Initial Catalog={your-database-name};

Explicitly Indicate You Are Using A Table

You could try explicitly wrapping your table name in square brackets to let SQL know that you are targeting a table :

// The [delays_table] will explicitly indicate you are targeting a table with that name
var query = "SELECT agency_name, COUNT(*) FROM [delays_table] WHERE late_type = 'miss' GROUP BY agency_name";

Answer 2

You also need to define delays_table

Something like this:

namespace public_transportaion.Models //or ORM
{
    [Table("delays_table", Schema = "YourSchemaHere")]
    public class delays_table
    {
        [Key]
        public string agency_name { get; set; },
        public int COUNT { get; set; }
    }
}