Invalid object name when using Entity Framework to read SQL Server table

Question

I'm trying to set up a database connection using Entity Framework in an ASP.NET Core application, but each time I attempt to read from my data context, it fails stating that the table I'm trying to get is invalid.

I'm relatively new to EF with ASP.NET Core, so I could be missing something obvious. How can I get EF to connect/find my object?

I have my connection string as follows:

Data Source=DbServer;Initial Catalog=AIT;Integrated Security=True

And the table that I'm trying to read from is on DbServer:

AIT.processing.orderProcessing

The error I'm getting when the DB context attempts to connect is this:

SqlException: Invalid object name 'processing.orderProcessing'.

I've looked at the context itself when it runs, and it is connecting to the correct server. I've also validated that the database is correct in the connection string and that it contains the processing.orderProcessing table. I've also tried to explicitly tell EF that it should be connecting to a specific object by using the Table property on my c# class:

[Table("AIT.processing.orderProcessing")]

This just causes my error message to change to:

SqlException: Invalid object name 'AIT.processing.orderProcessing'.

As far as I know, if it is connecting to DBServer, it should be able to find that table as it definitely exists, and it is spelled correctly. I just copy/pasted it from SSMS to make sure.

Edit: The below answer was what fixed it for me. Changing my Table attribute to this:

[Table("orderProcessing", Schema = "processing")]

Caused EF to be able to connect.

Probably account used for your app do not have privileges this table. — Piotr Palka
– Piotr Palka, Commented Jun 6, 2019 at 23:57
If you database is: AIT and your table name is: processing.orderProcessing than your mapping should be: [Table("processing.orderProcessing")]. — Flavio Francisco
– Flavio Francisco, Commented Jun 7, 2019 at 6:48
I do have admin level permissions for the database I'm trying access. — jackfrost5234
– jackfrost5234, Commented Jun 7, 2019 at 13:25

Ahmet Arslan · Accepted Answer · 2019-06-07 07:00:46Z

4

If your database name is "AIT" and schema is "processing" you must express EF core your schema name.

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.HasDefaultSchema("processing");
}

answered Jun 7, 2019 at 7:00

Ahmet Arslan

6,2903 gold badges36 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Konrad Viltersten Over a year ago

I have the opposite problem. I used HasDefaultSchema("blopp") and the tables are created like blopp.Customers etc. However, when I try to read from the tables by Context.Customers.Count(), I get the error that it't not there. Care to suggest a reason/solution? It's like OnModelCreating doesn't kick in when I use the context for operations (but is effective when creating migrations).

Ahmet Arslan Over a year ago

@KonradViltersten I called Count() function and it worked. How you named your table in EF ? private void ConfigureMyClass(EntityTypeBuilder<MyClass> builder) { builder.ToTable("MyClassTable"); }

Konrad Viltersten Over a year ago

I didn't call the table explicitly, so I assume that it was called as the class name is (pluralized, though). And when I set the default schema to dbo (or not set it explicitly at all), it works, so somehow, my classes during reading lose the set schema.

Ahmet Arslan Over a year ago

Afaik table names are pluralized versions as default. Is your problem solved by setting the table name explicitly?

Konrad Viltersten Over a year ago

I'm not sure if I'd call it solved - it's more of an ugly hack. I'd rather understand why the specified default schema is used when migrating but not while reading from the DB. In my experience, such discrepancies are often a sign of something much worse not known yet but heading your way. :)

|

Ryan · Accepted Answer · 2019-06-07 03:00:40Z

1

So AIT is database name,processing is schema name and orderProcessing is the table name.

Try to set table name and scheme name separately using Data Annotations like:

[Table("orderProcessing", Schema = "processing")]

Or in DbContext OnModelCreating using:

 modelBuilder.Entity<MyEntity>().ToTable("orderProcessing", processing);

answered Jun 7, 2019 at 3:00

Ryan

20.3k10 gold badges44 silver badges68 bronze badges

1 Comment

jackfrost5234 Over a year ago

This worked, specifically setting the Schema as a separate property inside the Table property.

Collectives™ on Stack Overflow

Invalid object name when using Entity Framework to read SQL Server table

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related