# Tuesday, 25 September 2007

jaoo2007_1 Developers != plumbers is a saying that Anders Hejlsberg has. We are spending too much time translating between multiple paradigms, be it XML, SQL, or objects. LINQ is the answer to this problem. We have two parts of LINQ that go to the database: LINQ to SQL and LINQ to Entities with LINQ to SQL as the simplest way of going to the database. LINQ to Entities is intended for the enterprise and provides more mapping features. LINQ to SQL works for both SQL Server 2000 and 2005.

So how does LINQ to SQL work? You basically write your query in the application and when you enumerate the query it will be converted to SQL and executed against the database. Adversely when you update everything is stored in the DataContext and is sent to the database on a call to the method SubsmitChanges.

How do you map a class to a table? You create your class like you would normally and decorate it with attributes. For a class you specify [Table(Name = "TableName"]. For properties you specify [Column] and additionally specify which property is the primary key like so [Column(IsPrimaryKey=true)]. Optionally you can store the mapping information in an XML file, though for simplicity and less errors keep the mapping info in attributes. No special base class is needed for the magic to work.

With the mapping done we create a strongly typed database context by inheriting a different class from DataContext where you can specify your tables. Running a query against the DataContext is where the mapping magic happens. You simply do a

var query = from c in db.Customers;

foreach ( Customer c in query ) // mapping magic happens here, not using the Table<Customer> type but the Customer type

{

// do stuff

}

Adding a where-clause will cause all the parameters in that clause will be generated to SQL parameters in order to avoid SQL injection. Generated queries are kept as simple as possible as to not confuse developers. Performing a select you can represent relationships which you cannot do in SQL; with SQL you can do only a flat resultset but with LINQ you can get back more natural OO hierarchies as results which are easier much to work with. The LINQ query language is a more expressive language than SQL and as such you are able to express every SQL query with LINQ. Only the properties you actually ask for are returned by the generated SQL query which is nice.

As an alternative to the attribute mapping you can use a graphical designer which allows you to drag and drop tables from the server explorer. It then creates the classes for you. the tools allows for overriding the generated values for the class name and various other stuff. It also allows for mapping to a stored procedure. Relationships are inferred from the database schema so get those foreign key constraints in place today :) Usually relationships are expressed by the object graph but in some cases the link is missing so you'll have to crack open the JOIN functionality of LINQ to get your job done.

One piece of LINQ to SQL that I'm not particularly fond of is the fact that interface based separation is not supported right out of the box so you need to enforce something like the Repository pattern and not allow direct access to the DataContext if you need that kind of separation.

Updates and deletions are very easy to work with. You basically update and create new instances of your objects like you would normally. When creating a new instance you also need to add it to either the DataContext or to the parent object to which it belongs. The only difference is that you must remember to call SubsmitChanges on your DataContext. Concurrency is handled via optimistic concurrency is used for updates, errors are raised if you encounter changed data. Autogenerated values like identities are automatically populated from the database into the object. If you want to use a stored procedures for selects, updates, or inserts you can configure a stored procedure to do so by modifying the behavior of the object in the designer. You simply drag the stored procedure you want to use into the designer and then configure it for the particular object. With the stored procedure is dragged into the designer you can also optionally call it directly on the DataContext.

For stored procedures returning a resultset you will default get a new type generated for the resultset of the proc. What's very cool is that you can configure the proc to not use the generated type and use a domain type instead. Additionally you can perform a query on top of the resultset returned by a stored procedure, something that is impossible with straight SQL.

An interesting feature I noticed in Visual Studio 2008 during the demo is a document preview when you're switching between open documents in the environment. Should make it much easier to find the particular code file you want.

Funny guy.

posted on Tuesday, 25 September 2007 16:50:48 (Romance Daylight Time, UTC+02:00)  #    Comments [0] Trackback
Related posts:
Virtualized Development
JAOO 2008 Day 3
JAOO 2008 Day 2
JAOO 2008 Day 1
ReSharper 4.x Nuggets
The C# Keyword var is Evil
Comments are closed.