Some point in time, when starting a new project you'll ask some questions:
- "What data access layer library am I going to use?"
- "Should I develop my own?"
- "And how about an O/R mapper?"
These are important questions and you should answer them. But the answer is now a little more simple to find. Since the release of .NET 3.5 last week, we now have built-in the framework a nice feature called LINQ.
LINQ or Language INtegrated Query is a set of language and framework features that allow you to write queries directly in C#. LINQ drastically cuts the plumbing code required for database applications while reducing runtime errors (LINQ queries are statically type-checked by the compiler). LINQ can also query local in-memory collections and XML trees. Framework 3.5 ships with a lightweight new XML DOM designed for this purpose...LINQ and C# 3.0 are set to make a huge impact: LINQ to SQL alone will halve the cost of writing and maintaining a data access layer. Further, a single new query syntax works across databases, local collections, XML documents, datasets, as well as third party products. [via albahari.com]
One example (taken from LINQPad) using the northwind database for LINQ to SQL:
| LINQ | SQL |
| from p in Products
let spanishOrders = p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")
where spanishOrders.Any()
orderby p.ProductName
select new
{
p.ProductName,
p.Category.CategoryName,
Orders = spanishOrders.Count(),
TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
} | SELECT [t0].[ProductName], [t1].[CategoryName], (
SELECT COUNT(*)
FROM [OrderDetails] AS [t4]
INNER JOIN [Orders] AS [t5] ON [t5].[OrderID] = [t4].[OrderID]
WHERE ([t5].[ShipCountry] = @p0) AND ([t4].[ProductID] = [t0].[ProductID])
) AS [Orders], (
SELECT SUM([t8].[value])
FROM (
SELECT [t6].[UnitPrice] * (CONVERT(Decimal(29,4),[t6].[Quantity])) AS [value], [t7].[ShipCountry], [t6].[ProductID]
FROM [OrderDetails] AS [t6]
INNER JOIN [Orders] AS [t7] ON [t7].[OrderID] = [t6].[OrderID]
) AS [t8]
WHERE ([t8].[ShipCountry] = @p0) AND ([t8].[ProductID] = [t0].[ProductID])
) AS [TotalValue]
FROM [Products] AS [t0]
LEFT OUTER JOIN [Categories] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [OrderDetails] AS [t2]
INNER JOIN [Orders] AS [t3] ON [t3].[OrderID] = [t2].[OrderID]
WHERE ([t3].[ShipCountry] = @p0) AND ([t2].[ProductID] = [t0].[ProductID])
)
ORDER BY [t0].[ProductName] |
Now...that's cool :) (I know that you could do the same query with a better SQL...)
But you could probably say that you prefer to stay with your own DAL or even with the one that you bought. But EVERYONE should learn about LINQ before starting a new project. There's no need to invent the wheel over and over. If you're going to develop in .NET (VB, C#, etc) you should consider LINQ, because it's a DAL, an O/R mapper, and it was tuned by a real smart guy that works in a big company. Building your DAL with LINQ also adds the value of technical support and bug fixes with no cost. Keep in mind that LINQ can also be used to query local collections, XML, datasets or anything else that implements extension methods.
If you want to learn how to use LINQ, there's no better way than trying. So, check this out. (I must say that LINQPad rocks. Remember that you need to install .NET 3.5 first)