do { Engine.BlogAbout(".NET","Silverlight"); } while ( alive );  

Mark Monster

Mark Monster  

Linq to SQL doing it manually: Part 1 - Table and column creation from C#

Mark Monster
November 3rd, 2008
, , ,

I was very interesting in doing Linq to SQL manually. Yes manually, not making use of the dbml-designer. And why should I want to do this? I thought it would be interesting to do Linq to SQL without a database being available yet.

I’ve also heard the rumor that Linq to SQL is dead, or almost dead. I’m not sure about this, but I still think Linq to SQL can be appropriate for simple situations, where Linq to Entities is for the more advanced scenario’s it’s also more complex to apply.

Attribute-Based mapping

What I need is Attribute-Based mapping I guess. The documentation talks about 9 different attributes:

  • DatabaseAttribute
  • TableAttribute
  • ColumnAttribute
  • AssociationAttribute
  • InheritanceMappingAttribute
  • FunctionAttribute
  • ParameterAttribute
  • ResultTypeAttribute
  • DataAttribute

In part 1, I will concentrate on the TableAttribute and the ColumnAttribute.

The TableAttribute

The TableAttribute makes sure a class gets mapped to a database table. As we have no table yet, we want to have as less code as possible. The following code makes sure a class gets mapped to a table. The class Tag gets mapped to the table named Tag.

[Table]
public class Tag
{}

Sometimes you want to have some control on the naming of the table. This is possible by just applying the Name property. This is just as simple as the following code. The class Tag gets mapped to the table named Tags.

[Table(Name="Tags")]
public class Tag
{}

This is all I have to say about the TableAttribute.

The ColumnAttribute

The ColumnAttribute is a little more advanced than the TableAttribute. But it can also be as simple as the TableAttribute. This is because all the primitive types are automatically mapped to a database type. But where do we start? Just very simple just add the ColumnAttribute to a property. The property Name gets mapped to the database column named Name.

[Column]
public string Name { get; set; }

If you want the Name property to be mapped to the database column CustomerName we can do the following.

[Column(Name="CustomerName")]
public string Name { get; set; }

It’s possible to have some influence on the type that’s used in the database for a column. I think this can be handy sometimes. For example if you want the Name field to be maximized to 25 characters and also not nullable, you can use this.

[Column(DbType="NVarChar(25) NOT NULL")]
public string Name { get; set; }

But I like the feature that Linq to SQL does the mapping himself. The following diagram is derived from MSDN and shows what mappings are possible.

Mapping for SQL and CLR Types

The default mapping for the CLR types are the following:

CLR Type Default SQL Type
System.Enum INT
System.Boolean BIT
System.Byte TINYINT
System.Int16 SMALLINT
System.Int32 INT
System.Int64 BIGINT
System.SByte SMALLINT
System.UInt16 INT
System.UInt32 BIGINT
System.UInt64 DECIMAL (20)
System.Decimal DECIMAL (29,4)
System.Single REAL
System.Double FLOAT
System.Char NCHAR(1)
System.String NVARCHAR(4000)
System.Char[] NVARCHAR(4000)
Custom type implementing Parse() and ToString() NVARCHAR(MAX)
System.DateTime DATETIME
System.DateTimeOffset DATETIMEOFFSET
System.TimeSpan TIME
System.Date.Linq.Binary VARBINARY(MAX)
System.Byte VARBINARY(MAX)
System.Runtime.Serialization.ISerializable VARBINARY(MAX)
System.Guid UNIQUEIDENTIFIER
System.Object SQL_VARIANT

We also want some columns to be identifying. This can be done by setting the IsPrimaryKey property that’s part of the ColumnAttribute. The following example makes the property Id the primary key. It’s possible to have multiple properties to define a composed primary key.

[Table]
public class Tag
{
    [Column(IsPrimaryKey = true)]
    public Guid Id { get; set; }

    [Column]
    public string Name { get; set; }
}

Next article will be about the actual generation of the tables and columns from the class definition.

2 Responses to “Linq to SQL doing it manually: Part 1 - Table and column creation from C#”

  1. Mark Monster » Blog Archive » Linq to SQL doing it manually: Part 2 - Database creation Says:
    November 9th, 2008 at 13:17

    [...] Linq to SQL doing it manually: Part 1 - Table and column creation from C# [...]

  2. Mark Monster » Blog Archive » Linq to SQL doing it manually: Part 3 - Relationships Says:
    December 6th, 2008 at 16:04

    [...] column mapping we did in Part 1 of the "Linq to SQL doing it manually" series. We also want to have relationships, this [...]

Leave a Reply