LINQ to SQL : Need Different Usernames for Prod and Dev DBML On Table Attribute

Question

This is a similar problem to this question

When I deployed my dev app to the prod server I was getting an error:

System.Data.SqlClient.SqlException: Invalid object name 'dbo.Login'.

The table attribute on the linq mapping looks like [Table(Name="dbo.Login")]

When I loaded the prod db schema into the vs2008 server explorer and refreshed my dbml entirely with that one, the application on prod works, but now the application on dev does not work.

The table attribute on the linq mapping now looks like [Table(Name="prodDbUsername.Login")]

On the dev server I now get

System.Data.SqlClient.SqlException: Invalid object name 'prodDbUsername.Login'.

What is the best way to manage these different user prefixes between dev and prod?

Answer 1

It sounds like you have have identical tables named differently in your different environments. The simplest way to fix your problem is to use identical schema for all environments (the data can be different, but you are asking for all kinds of problems if the schema is not the same).

EDIT: With your further clarification, the tables are being created either with a different owner or a within a different schema. See http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server for further clarification on the difference.

I would recommend that you try creating your tables with the following syntax:

CREATE TABLE [dbo].[MY_TABLE_NAME]...

Answer 2

If you're using a single schema (i.e. not multiple schemas in a single db), you can just remove the schema prefix on your tables.

E.g. change:

<Table Name="dbo.person" Member="Persons">

To:

<Table Name="person" Member="Persons">