4

I'm using entity framework code first to create my tables. Please note - create the tables, not the DB, since I'm working on a hosted environment and I don't have a user that is allowed to create db's.

Committing a DB update works fine, but retrieving data gives the exception:

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

I've read that it happens because I'm not using EF Code First to create the DB. That's fine, but how do I elegantly solve this?

All the generated tables do not have a prefix like dbo. A solution like this doesn't work, and isn't elegant at all:

[Table("EventHosts", Schema = "")]
Cœur
  • 37,241
  • 25
  • 195
  • 267
Jochen van Wylick
  • 5,303
  • 4
  • 42
  • 64
  • Hi - sorry for being unclear - it is MS SQL and the query that's being executed it SELECT something something FROM dbo.EventhHosts. Which is wrong obviously, because the table name is only EventHosts. So either I have to make sure Entity Framework doesn't put dbo. in front of the table name, or tables need to be generated with a prefix. – Jochen van Wylick May 06 '12 at 20:26
  • 1
    The prefix is actually schema and dbo is a default one. What schema is default for your database? You will have to use that schema in the mapping. – Ladislav Mrnka May 06 '12 at 20:36
  • @LadislavMrnka - Thanks! So what I did was: - I ran exec sp_columns EventHosts (in Visual Studio) - Got the name from the TABLE_OWNER column - Used that in the attribute [Table("EventHosts", Schema = "TABLE_OWNER_HERE")] on the entity That solves the problem. If anyone has an idea how I can set this for all entities with one line - as opposed to adding this attribute to all entities - please let me know. – Jochen van Wylick May 06 '12 at 20:56
  • Can you just have the table(s) moved to the dbo schema? It's the default schema, and unless there's some strong reason to use other schemas, things will be easier for you just leaving everything in the dbo schema. See this related SO thread: http://stackoverflow.com/questions/9562883/can-i-change-the-default-schema-name-in-entity-framework-4-3-code-first – James Manning May 06 '12 at 23:48
  • @JamesManning: That is not the case in hosted environment where you almost always have separate schema for your tables. – Ladislav Mrnka May 07 '12 at 09:34

5 Answers5

6

Ok, for me issue was that I had a table called dbo.UserState and in C# EF was trying to access dbo.UserStates because of pluralization.

The solution was to put Table attribute above class and specify the exact table name:

[Table("UserState")]
public class UserState
{
    [Key]
    public int UserId { get; set; }
}
Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
2

To answer your first question: use the schema created for you by your hosting provider.

To answer your second question: No there is currently no direct way to change the default schema globally because you cannot modify existing conventions or create new conventions. You can try to hack it.

For example you can override OnModelCreating and use reflection to get all DbSet<> properties declared in your context. Than you can just use simple loop on these properties and create ToTable mapping call with name of the property as table name and your custom schema. It will require some playing with reflection to make this work.

Alternatively you can try to do some reusable approach by implementing custom conventions. You can find many different articles about using your own conventions with EF. Some examples:

My high level untested idea is following same principle and create assembly level attribute which will be processed by the convention mechanism and applied on all your entities.

Cirem
  • 840
  • 1
  • 11
  • 15
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • i have similar issue with MySql. If possible please have a look at http://stackoverflow.com/questions/28044340/table-dbname-dbo-tablename-doesnt-exist-entity-framework-6 – Arjun Vachhani Jan 20 '15 at 11:39
0

Try to set default schema name to 'dbo' in SQL SERVER.

http://msdn.microsoft.com/en-us/library/ms173423.aspx

Eray Aydogdu
  • 240
  • 1
  • 4
  • 16
0

On of the reason for this error is the table named "EventHosts" may not Exist or that table is renamed to some other name please check with that..

AishwaryaKasi
  • 83
  • 7
0

https://stackoverflow.com/a/12808316/3069271

I had same issue, it was pluralize problem between mapping and db.

Community
  • 1
  • 1
mrTurkay
  • 642
  • 1
  • 7
  • 13