SQL Server connection string with EF

Question

I just converted a MySQL database to SQL Server. I'm complete new to SQL Server, I want to use it with Entity Framework 4, so I shouldn't be a biggie.

I think I don't get the concepts about objects in DB and so on. In SQL Server Mgmt Studio I can execute this query

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [id]
  FROM [db].[dbo].[mytable]

And it works fine, however if I use this connection string

Data Source=MYPC-PC\;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30

The query will execute fine, but it will exclude the [db], so it will not return any row. This is the query from Entity Framework.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [id]
  FROM [dbo].[mytable]

If I change my connection string to include a Database like this

Data Source=MYPC-PC\;Integrated Security=SSPI;Database=db;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30

I get this error

There is already an object named 'MyTable' in the database.

But there is only one table called Table. I have no idea of the concepts behind this. What am I doing wrong? I'm using SQL Server 2012

Update 1

I use code first.

        var result = db.MyTable.Where(x => x.Prop == Prop)
            .OrderBy(x => x.Something)
            .Take(10);

        var data = result.ToList();

My DbContext class

public class Context : DbContext
{
    
     public Context()
    : base("Context")
  {
    // Get the ObjectContext related to this DbContext
    var objectContext = (this as IObjectContextAdapter).ObjectContext;

    // Sets the command timeout for all the commands
    objectContext.CommandTimeout = 12000;
  }

    public DbSet<Models.MyTable> MyTable{ get; set; }


}

And in web.config

  <connectionStrings>
    <add name="Context" providerName="System.Data.SqlClient" connectionString="connectionString"  />

Which EF approach are you using? Code-first? Database-first with a visual model? — marc_s
– marc_s, Commented Nov 17, 2012 at 14:48

Alex · Accepted Answer · 2012-11-17 15:36:27Z

2

Your table MyTable already exists in the database before first migration. So you can just delete this table and then it will be recreated at first application run.

EDIT: If you don't want to remove your table and if you use at least EF 4.3 then
run from the Console Add-Migration InitialMigration -IgnoreChanges
and then run Update-Database

edited Nov 17, 2012 at 15:36

answered Nov 17, 2012 at 15:13

Alex

8417 silver badges11 bronze badges

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

4 Comments

Simon Edström Over a year ago

Ok, but MyTable contains a lot of data, so I don't want to delete it. Will the database be marked as migrated? Or can I force it do be marked so it just continue?

Simon Edström Over a year ago

But that's not going to work into my production server. Couse I don't have Visual Studio on that. How can I solve that?

Alex Over a year ago

For the production server it's better to disable automatic migration by setting AutomaticMigrationsEnabled = false in your code.

Alex Over a year ago

Also you can copy __MigrationHistory table from your development server to the production server.

Collectives™ on Stack Overflow

SQL Server connection string with EF

Update 1

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update 1

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related