Is it possible to use Linq-SQL without drag-and-drop?

Question

If i want to use Linq-SQL i also have to drag the DB Table unto the designer surface to create the entity classes.

I always like full control in my application and do not like the classes created by dotnet.

Is it possible to provide this connection between Linq and the DB using my own Data Access Layer Entity classes?

How can i get it done?

Answer 1

To avoid drag & drop you can take a look at SqlMetal.exe.

However, it sounds like you really are requesting Persistence Ignorance, and I'm not sure that this is possible with L2S - it certainly isn't possible with LINQ to Entities until .NET 4...

I once wrote a blog post on using SqlMetal.exe and subsequently modifying the generated schema - perhaps you will find it useful, although it has a different underlying motivation.

Answer 2

You can write your own classes very easily using Linq-to-SQL - just involves painting your classes with some Attributes.

For Example, this is a very simple table I have in one of my projects, and it works with Linq-to-SQL just fine:

[Table(Name = "Categories")]
public class Category : IDataErrorInfo
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int Id { get; set; }
    [Column] public string Name { get; set; }
    [Column] public string ExtensionString { get; set; }
}

The code was very easy, especially if you make your property names line up with your table names (you don't have to).

Then you just need a Repository to connect to the DB:

class CategoryRepository : ICategoryRepository
{
    private Table<Category> categoryTable;
    public CategoryRepository(string connectionString)
    {
        categoryTable = (new DataContext(connectionString)).GetTable<Category>();
    }
}

Of course there is more to it, but this shows you the very basics and it is not hard to do once you understand it. This way you have 100% control over your classes and you can still take advantage of Linq-to-SQL.

I learned this approach from Pro ASP.NET MVC Framework, an awesome book.

If you want to see more, all of my Linq-to-SQL classes were written from scratch on one of my projects you can browse here.

Answer 3

I've got a couple tutorials up on CodeProject that walk through how to do this, including how to handle the relationships (M:M, 1:M, M:1) in an OO way and keep them in synch as you make updates:

A LINQ Tutorial: Mapping Tables to Objects

A LINQ Tutorial: Adding/Updating/Deleting Data