Problem refreshing tables in the LINQ to SQL designer

Question

I have been using LINQ to SQL for a while, and there is one thing that has always bothered me. Whenever I modify the schema of a table, in order to refresh it in the designer, I have to delete it and then add it back. That's fine, but this means I have to actually find the table in the designer. I have about 100+ tables in my database, and every time I do this, it's like finding a needle in a haystack. Well, maybe it's not that bad, but seriously, it takes way longer than it should.

Is there another option for refreshing tables that I am unaware of?

Answer 1

I have had simliar issues with the designer - the best thing I can suggest is creating multiple contexts for different areas of your data access - I broke mine down to as few a related tables as I could get away with for each functional area. You can re-use tables across contexts so it isn't a big deal.

Answer 2

I personally detest using the designer, and I've had various issues with it whenever I've dared to use it.

I mostly use LINQ for very simple CRUD (no linked entities or anything), and if that's the case with you, it might be worth straying from the designer crutch. Especially since defining LINQ-to-SQL entities is as easy as this:

[Table("dbo.my_table")]
public class MyTable
{
[Column("id", AutoSync = AutoSync.OnInsert, IsDbGenerated = true, IsPrimaryKey = true)]
public Int32 Id { get; set; }

[Column("name", DbType="NVarChar(50) NOT NULL")]
public String Name { get; set; }
}

This way, all your entities have their own files, which makes finding them much easier, though you'll still have to add/update the properties manually.

Of course, if you'd refactor 100+ tables, that might not be an option ;)

Answer 3

There's a template for VS 2008 that replaces the designer, it should ease refreshing your LINQtoSQL classes: http://damieng.com/blog/2008/09/14/linq-to-sql-template-for-visual-studio-2008

Answer 4

There are a couple of other options:

1. Edit the .dbml file that the designer uses to draw the tables and generate the code. I've used this approach when the changes are small (adding a couple of columns, creating a simple table)
2. Use sqlmetal to create the required xml for the changed tables and move the declarations by hand to the .dbml file. This one is better for when the changes are either more complex or larger.

Answer 5

This is not possible using the VS linq to sql designer.

You can do this using LLBLGEN PRO, a third party tool, instead of the built-in linq to sql designer. It isn't free but it does do a ton of other stuff as well, which of course you may or may not need.

LLBLGEN PRO is actually a full set of ORM tools, but also includes an enhanced linq-to-sql designer with 'refresh model from SQL' functionality.

See here for description of the issue - http://weblogs.asp.net/fbouma/archive/2008/05/01/linq-to-sql-support-added-to-llblgen-pro.aspx And here for the tool - http://www.llblgen.com/

Answer 6

Some people use SqlMetal to 'refresh/update' their Linq2Sql designer. The designer does not have support for refreshing the schema, when the DB changes. You have to manually drop the table and re-add it back in.

ADO Entity Framework i believe can refresh. I've not used it, but I think I saw this at a TechEd demo this year.

Helpful Info: Google's results for SqlMetal.

Answer 7

I don't do any customization of the content on the designer so after table changes I just hit CTRL+A followed by DEL. Then shift-select all of my tables and slap them back onto the designer. I don't have 100s of tables yet so not sure if things slow down at some point but with 20+ tables it just takes a second.

Answer 8

I have written an add-in that can do that (in both directions; database -> DBML or DBML- -> SQL-DDL diff script).

Unlike SQLMetal (or EF's "update model from database") mentioned in another reply, the add-in does a true sync/refresh; applying changes corresponding only to the differences between the model and the underlying db.

That means any customizations (renamed properties/navigation properties etc) that you have made in other areas of your model will not be removed/overwritten unless they are in conflict with the underlying db schema. (in which case you can still preserve them by adding them to the add-in's "exclusion list")

You can download it and get a free 30-day trial license from http://www.huagati.com/dbmltools/

Answer 9

I have a similar comment, thought it might fit in here for anybody out there Googling a solution to this issue...

When I change the columns that are returned by a stored procedure, deleting the procedure from the designer and re-adding it does not work. The custom return type entity that the designer generates does not reflect the changes to the SP.

I've tried disconnecting the DB in the server explorer, even deleting and re-adding the connection.

The only solution I've found is this: 1. Delete the SP from the designer. 2. Save the dbml file (or the whole solution, whatever) 3. Completely close Visual Studio. 4. Re-open Visual Studio and your solution. 5. Re-add the stored procedure to the designer.

I think that qualifies as a blue ribbon pain in the rump. Anybody got a simpler solution?

PS- To those of you with 100+ tables: Go get a real (real == mature) ORM tool. I personally vote for NetTiers. It rocks. Used it for years with no (or at least very few) complaints. You'll probably have to buy CodeSmith to use it effectively, but it's worth it. The templates are open source. And there are templates for nHibernate as well. But I've found that I don't really dig on Java ports. If I'm gonna code on MS platforms I want code that was "born" there...

...editorial complete. :P