[asp.net-mvc] System.Data.SqlClient.SqlException: Invalid object name 'dbo.Projects'

My MVC app is returning SqlExceptions when trying to access any table in my database.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.Projects'.

My app us linq for the data layer.

If I use an old dll it works fine, (so doesn't seem to be a problem with the DB) just this latest app dll that I've uploaded.

details

[SqlException (0x80131904): Invalid object name 'dbo.Projects'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader() +12
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +975
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +113
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +344
System.Data.Linq.DataQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +35 System.Linq.Buffer1..ctor(IEnumerable1 source) +247 System.Linq.<GetEnumerator>d__0.MoveNext() +108
System.Linq.Buffer1..ctor(IEnumerable1 source) +259
System.Linq.<GetEnumerator>d__0.MoveNext() +108
System.Collections.Generic.List1..ctor(IEnumerable1 collection) +7665172 System.Linq.Enumerable.ToList(IEnumerable1 source) +61 Mezza_crm.Controllers.ProjectsController.GetProjectList(NameValueCollection form) in C:\mezza_crm\mezza_crm\Controllers\ProjectsController.cs:164 Mezza_crm.Controllers.ProjectsController.List() in C:\mezza_crm\mezza_crm\Controllers\ProjectsController.cs:53
lambda_method(ExecutionScope , ControllerBase , Object[] ) +39
System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +178
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +24
System.Web.Mvc.<>c__DisplayClassa.b__7() +52 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +254
System.Web.Mvc.<>c__DisplayClassc.<InvokeActionMethodWithFilters>b__9() +19 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +192
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +399
System.Web.Mvc.Controller.ExecuteCore() +126
System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +27 System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7
System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +151 System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +57
System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +7
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

TLDR: Check that you don't connect to the same table/view twice.

FooConfiguration.cs    
builder.ToTable("Profiles", "dbo");
...

BarConfiguration.cs
builder.ToTable("profiles", "dbo");

For me the issue was that I was trying to add an entity that connected to the same table as some other entity that already existed.

I added a new DbSet with entity and config, thinking we don't have it in our solution yet, however after searching for table name through all solution I found another place where we already connected to it.

Switching to use existing DbSet and removing my newly added one solved the issue.

Check the Initial Catalog parameter in your connection string. It may be that your code is looking in the wrong database for the Projects object.

For example, if you have database syncing setup in such a way that only a subset of the master-database's tables are transferred, you can encounter this error if Linq to SQL is expecting all tables to be in the database pointed to by the connection string.

Try to do this way

protected override void OnModelCreating(DbModelBuilder modelBuilder) 
{ 
     modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}

or if you use .net core try it

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Usuario>().ToTable("Usuario");
}

Replace Usuario for your Entity Name, like a DbSet<<EntityName>> Entities without Plural

The problem I had was because I had made a database in my LocalDb.
If that's the case then you have to write is as shown below:

    "SELECT * FROM <DatabaseName>.[dbo].[Projects]"

Replace with your database name.
You can probably also drop the "[ ]"

This maybe due to an incorrect table name from where you are fetching the data. Please verify the name of the table you mentioned in asmx file and the table created in database.

I have seen that the new versions when you define the resulting entities better define them in the following way if you handle a different scheme, I had a similar problem

You must add System.ComponentModel.DataAnnotations.Schema

using System.ComponentModel.DataAnnotations.Schema;


 [Table("InstitucionesMilitares", Schema = "configuracion")]

Do you have access to the SQL Server you are querying? Can you see a Table or View called dbo.Projects there? If not, that would be a good place to look.

Linq to SQL creates an object map between the database and the application. If your new DLL that you're deploying doesn't match with the database anymore, then this is the sort of error you'd expect to get.

Do you perhaps have different database schemas between your development environment and the deployment environment?

I had the same error. The cause was that I had created a table with wrong schema(it ought to be [dbo]). I did the following steps:

1. I dropped all tables which does not have a prefix "dbo."

2. I created and run this query:

CREATE TABLE dbo.Cars(IDCar int PRIMARY KEY NOT NULL,Name varchar(25) NOT NULL,    
CarDescription text NULL)
GO

I have tried almost all the answers from below list, but did not work for me. But read the exception well and then tried rename my Dbset name TransactionsModel to Transactions and it work for me.

old Code:

public class MyContext : DbContext
    {
        //....
        public DbSet<Models.TransactionsModel> TransactionsModel { get; set; }
    }

New Code:

public class MyContext : DbContext
    {
        //....
        public DbSet<Models.TransactionsModel> Transactions { get; set; }
    }

Delete _MigrationHistory table in (yourdatabseName > Tables > System Tables) if you already have in your database and then run below command in package manager console

PM> update-database

The cause of this problem could be a property setting of the database (Sql2008R2 with .NET4).

• Problem is reproducible at will when changing the COLLATION value of a database.

To display COLLLATION, use the Sql Server Mgmt Studio.

• Right-click the database and select Properties -> General, Then look under Maintenance for the COLLATION value

To change COLLATION, (still) use the Sql Server Mgmt Studio.

• Right-click the database and select Properties -> Options, From there, you can change the COLLATION value

If you use two databases you can add another DataClasses.dbml and map the second database into it.
It works.

If you are in that phase of development where you have an method inside your context class that creates testdata for you, don't call it in your constructor, it will try to create those test records while you don't have tables yet. Just sharing my mistake...