Best Practices for Managing Linq to SQL Dbml Files?

Question

I've just started using Linq to SQL, and I'm wondering if anyone has any best practices they can share for managing dbml files.

• How do you keep them up to date with the database?
• Do you have a single dbml file for the entire database, or is it split into multiple logical units?
• How does managing this file work in a team environment?

Any other tips and tricks welcome.

Answer 1

Have you looked at SqlMetal? It's officially supported, although not promoted too much. You can use it to build dbmls from the commandline - we've used it as part of a db's continous integration updates (make sure you have really good code separation if you do this though - partial classes are a saviour - as the dbml will get overwritten).

If I recall correctly it doesn't have quite the same features as the model designer in Visual Studio (I think it handles pluralisation differently). There a good post about it on Ben Hall's blog.

Answer 2

The fact that the L2S designer doesn't support syncing with the database structure is a huge limitation in my mind. However, there is an add-in available that provides some re-sync capabilities:

http://www.huagati.com/dbmltools/

Unfortunately, it's no longer free.

Answer 3

Since you asked for other tips and tricks for managing DBML...

When DBML files are refreshed from the database, there are certain schema settings which they don't pick up on, such as default column values, forcing you to manually change the setting. This can lead to lost hours every time you refresh the DBML without realizing or remembering where you need to make manual adjustments, and your code starts failing.

To guard against this, one trick is to write a unit test which uses reflection to check the LINQ metadata for those (manual) settings. If the test fails, it gives a descriptive error message, instructing the user to make the proper change to the column properties. It's not a perfect solution, and it might not be convenient if you have many manual settings, but it can help avoid some major pain for yourself and your team.

Here's an example of an nunit test to check that a column is set to auto-generate from the DB.

    [Test]
    public void TestMetaData()
    {
        MyObj my_obj = new MyObj()
        {
            Foo = "bar",
        };

        Type type = MyObj.GetType();
        PropertyInfo prop = type.GetProperty("UpdatedOn");
        IEnumerable<ColumnAttribute> info = (IEnumerable<ColumnAttribute>)prop.GetCustomAttributes(typeof(ColumnAttribute), true);
        Assert.IsTrue(
            info.Any<ColumnAttribute>(x => x.IsDbGenerated == true), 
            "The DBML file needs to have MyObj.UpdatedOn AutoGenerated == true set. This must be done manually if the DBML for this table gets refreshed from the database."
        );
    }

Answer 4

PLINQO is a set of code generation templates generating LINQ to SQL. It supports syncing with the database and splitting entities into multiple classes along with many other features that make LINQ to SQL easy to use.

Check out the PLINQO site at http://www.plinqo.com as well as the intro videos.

Answer 5

Here is a link that provides good information about LINQ to SQL best practices

http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20Best%20Practice.aspx