How can I control the creation of database indexes when using DataContext.CreateDatabase()

Question

I am new to LINQ to SQL, but have done a lot of database development in the past.

The software I just started working on uses:

// MyDataContext is a sub class of DataContext, that is generated with SqlMetal
MyDataContext db = new MyDataContext (connectionString);
db.CreateDatabase();

to create the database when it is first run.

I need to add some indexes to the tables....
How can I tell the DataContext what indexes I want?

Otherwise how do I control this?

(I could use a sql script, but I like the ideal that db.CreateDatabase will always create a database that matches the data access code)

(For better, or worse the software has full access to the database server and our software often create databases on the fly to store result of model runs etc, so please don’t tell me we should not be creating databases from code)

---

I seem not to be the only person hitting limts on DataContext.CreateDatabase() see also http://csainty.blogspot.com/2008/02/linq-to-sql-be-careful-of.html

Answer 1

As far as I know the DataContext.CreateDatabase method can only create primary keys.

When you look at the DBML directly, you will see that there are no elements for defining an index. Therefore it is, IMHO, save to assume that CreateDatabase cannot do it.

So the only way I can think of for creating indexes "automatically" is by first calling DataContext.CreateDatabase and then calling DataContext.ExecuteCommand to add the indexes to the tables that were just created.