One of the most exciting things I've been working with recently has been LINQ to SQL, which is available in VS2008 (orcas) .net 3.5 beta.
Summary
LINQ to SQL is Microsoft's sql mapping framework, created by Anders Hejlsberg (the lead architect of C#, and if my memory serves me correctly, Borland Delphi, both great technologies).
The idea of LINQ to SQL is that you can effectively query in memory and database mapped objects, with deferred loading available. You can provide a series of classes, and through either attributes or an xml mapping file, map these classes to your database.
Some new syntax
Before we start talking about the database side of things lets have a look at some syntax. Suppose you have a List<Order> where the list of orders is populated somehow, and you wanted a new list of orders where order.IsOpen == true.
The .net 2 way of doing it would be:
List myOpenOrders = new List();
foreach (Order o in MyOrderList)
{
if (o.IsOpen == true)
{
myOpenOrders.Add(o);
}
}
or the more concise
List myOpenOrders = orders.Find(delegate (Order o)
{ return o.IsActive == true});
Anyway, LINQ provides an SQL Query like method to query a list.
var myOpenOrders = from order in orders
where order.IsOpen == true
select order
var is a new typesafe inferred type, checked at compile time. So in this instance, myOpenOrders represents an IEnumerable<Order>, but you could just as easily declare
var myInt =5;
But why not just declare an int, or an IEnumerable<Order> I hear you ask! - because using another new feature of .net 3.5, you can also create types on the fly:
var MyNewType = new {Name = "Chris", Age="32" };
MyNewType.Name = "Chris Buckett";
This is useful if we consider the myOpenOrders query again:
var myOpenOrders = from order in orders
where order.IsOpen == true
select new {
OrderNum = order.OrderNumber,
OrderDate = order.OrderDate
}
This creates an IEnumerable of our new type, which you could access like:
myOpenOrders[0].OrderNum
Or more likely, databind the result to some output.
Finally, we can also use the new "Lambda" syntax to return just one item.
Order myOrder = orders.Single(o => o.OrderNumber == 123);
Single is an Extension Method - another new feature whereby you can add functionality to a class without inheriting from it. I'll probably blog about that at some other point. Suffice to say that good old intellisense gives you a nice list of provided extension methods when you type
order.
The Lambda syntax (o=>o.OrderNumber ...) in the context of single is more or less a shorthand for the following:
foreach (Order o in orders)
{
if (o.OrderNumber == 123) { return o; }
}
Except that the Single method will generate an exception if nothing is found. If you'd like to return null, rather than generate an exception, then use SingleOrDefault().
Back to LINQ to SQL
So, we can see we can query a list of objects - this is where linq to SQL comes in. By defining a DataContext (either using the drag and drop visual designer, or creating your classes and mapping them to the database), and then using that datacontext to retrieve your list of objects, you can easily query the database to retrieve the objects you require. The datacontext also has useful caching, so only the objects that aren't already in the cache are pulled from the database.
For example:
MyDataContext dc = new MyDataContext(myConnectionString);
var myOpenOrders = from order in dc.Orders
order.IsOpen == true
select order;
And to select a single record:
Order order = dc.Orders.Single(order => order.OrderNumber = 123)
And finally, we can modify that order and save the changes back to the database with the following:
order.IsOpen = false;
dc.SubmitChanges();
One other useful thing that LINQ to SQL provides is the ability to access child objects, for example:
var myExpensiveOrderItems = from orderItem in order.OrderItems
orderItem.Value > 500
select orderItem;
Or
OrderItem orderItem = order.OrderItem.Single(oi=>oi.ItemCode == "ABC123");
And you can reference the order from that order item using
orderItem.Order
And so on. There's a load more, and I would recommend either
Scott Gu's descriptions, or
Mike Taulty's videos to give you some better insight.
Getting back to the point.
So, the thing that excites me today is the promised functionality in the upcoming Beta2, specifically Entity Object Validation. By creating a partial class for the entity type (say Order, which has been auto-created by the drag and drop designer, for example), you can declare the following (and yes, this is very similar to
Scott Gu's example, as it is what I've just read:
public partial class Order
{
partial void OnValidate()
{
if (Order.OrderDate < DateTime.Today)
{
throw new Exception("Order date is before today");
}
}
}
When you call SubmitChanges , OnValidate is called. If the exception is thrown, everything is rolled back (because all LINQ queries are executed within their own transactions).
Note another new piece of syntax: partial methods. This allows you to develop a class with a method stub, e.g.
public partial class MyClass
{
partial void DoSomething(string someValue);
public string MyPrivateProperty
{
get
{
return _something;
}
set
{
_something = value;
DoSomething(value);
}
}
}
}
and then in another file, you can use:
public partial class MyClass
{
partial void DoSomething(string someValue)
{
//Some code here
}
}
The main benefit I can see of this is that you can easily use generated code, such as that from the LINQ visual designer, and then extend that code in separate files buy using partial classes and partial methods. That way, if you regenerate the generated code, all you hard work doesn't get wiped out by an over-eager code generator.
Further reading:
Linq tutorials from Scott Gu
More talk about partial methods
There is also quite a good piece on
phoen blog about whether technologies such as LINQ will affect database best practices - essentially making that point that with LINQ technologies, developers steered towards using the database simply as a "bit bucket" or object repository.
One final useful link for those TDD inclined is
Ian Coopers post with a useful example of testing your LINQ classes without accessing the database.
[tags]LINQ, Orcas, vs2008, .net 3.5, Database[/tags]