How to: Use LINQ to SQL without using the Object Relational Designer

CoreyRoth — Tue, 18 Mar 2008 00:54:20 GMT

LINQ to SQL has already proved to be extremely easy to use to create object relational mappings when you have an existing database schema using the Object Relational Designer. This designer is good, but you may not want something that is autogenerating your domain classes. You may want to generate your domain classes yourself. This is actually quite easy and works in a similar manner to other OR/Ms such as ActiveRecord. The thing I like about it is that your domain objects do not have to inherit from some base class that has all of the underlying logic to access the database. Instead you create a custom class separate from your domain objects that inherits from DataContext.

We'll create a simple example of a products table for an e-Commerce web site. Let's start by looking at the domain object. Before you create your domain object start by adding a reference to System.Data.Linq to your class library if it is not already present. You will then need to add a using statement in each domain class for System.Data.Linq.Mapping.

[Table(Name="Products")]
public class Product
{
    [Column]
    public string Name;
 
    [Column(IsPrimaryKey=true, Name="Id")]
    public int ProductId;
 
    [Column(Name="Price")]
    public double Price;
}

The first thing you do in your domain class is decorate it with a Table attribute. An optional parameter here specifies the name of the underlying database table. In this case my domain object it Product but my database table is named Products. I then defined three properties representing columns in the table. The Column attribute specifies that the property will have a corresponding column in a database table. The IsPrimaryKey parameter specifies that the column is a primary key in the database. The Name parameter here also allows you to specify a different column name in the database.

That is really all that is required to create a domain object. You can define a class for each domain object you want and you can also create relations between them (but I won't be covering that here today). Once you have your domain object created, you will need to create a DataContext class to actually be able to query your domain objects. This is also pretty simple. You just expose a property with the generic type of Table<> for each one of your domain objects. The name of the property is what you will use with the DataContext when you are querying with LINQ.

public class StoreDataContext : DataContext
{
    public Table<Product> Products;
 
    public StoreDataContext(string connection)
        : base(connection)
    {
    }
}

Now that you have your domain objects written you will need to create the SQL tables that they represent. You can do this manually, or you can have LINQ create the whole database for you. Just create an instance of your DataContext and call the CreateDatabase method. This method infers the name of the database given the connection string you used. If you did not specify the database, you need to add a Database attribute with the name to your class.

StoreDataContext myDataContext = new StoreDataContext(myConnectionString);
myDataContext.CreateDatabase();

Alright, so now your domain objects and database are created, now you just need to query something with it.

var products = from product in myDataContext.Products
               where product.Price > 49.99f
               select product;

This simple query simply returns any product with a price greater than 49.99. So, LINQ to SQL doesn't have to be completely domain driven. This gives you a lot of flexibility and makes it easy to add additional things to your domain logic if you want to. The downside to this of course it that, when your database schema changes, your domain object is not going to get updated at the click of a button. If you are building your domain objects in this manner though, this is probably not a concern to you though.

Corey Roth [MVP] : ASP.NET, LINQ to SQL, LINQ

How to: Use LINQ to SQL without using the Object Relational Designer