Linq to SQL Attribute Based Mapping
Linq to SQL Attribute Based Mapping
Linq to SQL has two mapping sources to map entities to tables in the database: Xml Mapping Source and Attribute Mapping Source. Developers use the graphic Object Relational Designer to design a model, and Visual Studio generates the code of the entities along with the attributes with mapping information.
In order to understand the mapping capabilities and how it works, I decided to start from an empty project, and create some entities without using the designer at all. This post is a step by step walkthrough of what I did in order to get things to work just like they would have worked with the designer and default generated code.
To get started, I created a new Console Application and added references to System.Data.Linq with contains the typed and extensions for using Linq to SQL. I also created a connection to Northwind sample database and copied the connection string.
Creating a Simple Entity
I created a new file with an empty class for the Order entity, and mapped it to the Orders table in the database.
[Table(Name="Orders")]
class Order
{
}
Notice that this entity has no fields in it, so I cannot really query by any field. In order to test that this mapping is working, I displayed the number of rows in the table.
string connectionString = "...";
DataContext ctx = new DataContext(connectionString);
var ordersTable = ctx.GetTable<Order>();
Console.WriteLine(ordersTable.Count());
Adding Entity Fields
The simplest way to map a property to a column is using the Column Attribute:
[Column(Name="OrderID")]
public int Id { get; set; }
and the property will be mapped to the table column. Very similar for a column that can except null values:
[Column(Name="OrderDate", CanBeNull=true)]
public DateTime? OrderDate { get; set; }
If the column has more complex DbType, it can be also expressed. Such as:
[Column(Name="CustomerID", DbType= "NChar(5)")]
public string CustomerId { get; set; }
With these properties in place, and the column mapping on top of them, we can query the Orders table:
DataContext ctx = new DataContext(connectionString);
var query = from order in ctx.GetTable<Order>()
where order.CustomerId == "ALFKI"
select order;
foreach (Order order in query)
{
Console.WriteLine(order.Id + " " + order.OrderDate + " " + order.CustomerId);
}
Insert, Update, Delete Support
Now that I queried for entities, I want to be able to add new entities, update entities and delete them.
If I try to insert a new order:
Order newOrder = new Order { CustomerId = "ALFKI", OrderDate = DateTime.Now };
ctx.GetTable<Order>().Add(newOrder);
ctx.SubmitChanges();
Console.WriteLine(newOrder.Id);
I get an exception: "Can't perform Create, Update or Delete operations on 'Table(Order)' because it is read-only." This is because the Order entity has no primary key mapping. Adding the required mapping and setting the primary key value to be database generated:
[Column(Name="OrderID", IsPrimaryKey=true, IsDbGenerated=true)]
public int Id { get; set; }
The insert command succeeds, and the generated Id is printed to the console.
Trying to delete an entity (the newly added order):
ctx.GetTable<Order>().Remove(newOrder);
ctx.SubmitChanges();
or trying to update an existing entity:
Order existing = ctx.GetTable<Order>().First();
existing.OrderDate = existing.OrderDate - new TimeSpan(1, 0, 0, 0);
ctx.SubmitChanges();
The commands succeed.
Relations (Association) with Other Entities
Each order is related to a single customer, according to the Customer ID column. In Linq to SQL, we would like to navigate between entities easily, and not selecting them again and again.
When associating one entity to another (1:1 relationship), we use a private member of type System.Data.Linq.EntityRef<T>, even though the property has the type of the target entity:
private EntityRef<Customer> _customerRef;
[Association(ThisKey = "CustomerId", IsForeignKey = true)]
public Customer Customer
{
get { return this._customerRef.Entity; }
set
{
this._customerRef.Entity = value;
}
}
where the Customer entity looks like:
[Table(Name="Customers")]
public class Customer
{
[Column(Name = "CustomerID", IsPrimaryKey = true, CanBeNull = false)]
public string CustomerId { get; set; }
}
And in order to query for a single order with its customer:
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Order>(ord => ord.Customer);
ctx.LoadOptions = dlo;
Order order = ctx.GetTable<Order>().First();
Conclusion
Linq to SQL Attribute Based Mapping is very straight forward and easy to get started with. It provides a simple way of mapping between the POCO entities (Plain Old CLR Objects) and database tables.