Linq to SQL Attribute Based Mapping

23 בספטמבר 2007

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();

 

Linq to SQL Attribute Based Mapping

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.

Add comment
facebook linkedin twitter email

3 comments

  1. Daniel12 בדצמבר 2007 ב 19:05

    for updates' if there are lots of properties to update, can I just write
    C1 = C2, like this… ?
    public void Update(Customer UpdatedCustomer)
    {
    Customer Original = ctx.Customers.Single(c=>c.Id == UpdatedCustomer.Id);
    Original = UpdatedCustomer;
    ctx.SubmitChanges();
    }

  2. TONNAM1 בדצמבר 2008 ב 3:16

    งง อ่ะ ขอ ไทยได้ป่ะ

  3. SM1 ביוני 2009 ב 23:03

    Hi,
    Lets say you generate your maping from the dbml file. Is there a way to make sure it always maps to the same class name? For example, Orders always map to Order class instead of Orders (Orders is the default option when you drag and drop the table).
    The reason I ask is lets say some one deleted the Orders table and added it again. The code will get regenerated and overwrite the mapping.
    I was wondering if there is a solution for that.

    Thanks,
    SM

Comments are closed.