I’ve finished getting the basic features of Suteki Shop running with NHibernate. Anyone who’s been following this project knows that it was originally written with Linq-to-SQL. Linq-to-SQL worked fine for the initial iterations of Suteki Shop, but there are lots of things I wanted to do with the project that would have been hard or impossible to do without the move.

Linq-to-SQL not a true ORM in the sense that it doesn’t support persistence ignorance, it’s more a object based projection of a relational database. Each class directly represents a table row, including foreign keys. One of the main pain points in the transition was removing all the application code that referenced these foreign keys and replacing them with object references instead. So for example, instead of changing the int value of Contact.CountryId I would change the actual referenced entity, Contact.Country.

The other place where I had to do lots of work was in my Linq queries. The NHibernate trunk now has a native Linq provider written by Steve Strong, so you can write:

var customer = session.Query<Customer>().Where(c => c.Name == "Fred").FirstOrDefault();

The main difference between the Linq-to-SQL provider and the NHiberante one is in the way they treat expressions that they don’t understand. Linq-to-SQL will translate as much of the expression that it can to SQL, get the result, and then allow Linq-to-objects to do the rest. The NHibernate provider will throw an exception saying that it doesn’t understand the expression. There are pros and cons to both approaches. Linq-to-SQL will always give you what you want, but maybe in an inefficient way you weren’t expecting, whereas NHibernate will force you to think about how you structure your queries. There are no surprises, but it’s a mark against persistence ignorance. Right now, I’m liking the NHibernate no surprises approach.

Whilst doing the conversion I ran into a problem with the OfType<T>() Linq extension method. I was using it in several places but NHibernate complained that it didn’t support it. I tweeted about it and almost immediately Steve Strong replied that it should be easy to implement. The next day he committed it to the NHibernate trunk and my queries all worked.

[画像:steve-strong]

Steve Strong, you are an awesome open source hero!

One of my major regrets with the way that I originally wrote Suteki Shop is binding views to entities, rather than using a view model. At first this can seem like an easy-win, but doing this work showed how can soon lead to all sorts of difficulties. Displaying the view model in the view is not so much of a problem - except in entity loading overhead. The problems mostly manifest themselves at the binding stage. I extended the ASP.NET MVC DefaultModelBinder to work with NHibernate entities, and it mostly works, but I spent far too much time resolving tricky binding issues with complex object graphs. I would have made my life much easier by binding to a nice flat view model and then manually mapping that to my entities.

Persistence ignorance is a dream, a nirvana, where we’d love to be, but no ORM will give you true persistence ignorance. NHibernate is far better in this regard than Linq-to-SQL, but you still have to do things the NHibernate way. Your ORM will hugely influence the way you build your application and although the persistence ignorance ideal should mean you can swap them with little difficulty, moving from Linq-to-SQL to NHibernate was a major piece of work, far more than I expected.

Web applications often have pages that show a table of items. Often these items are filtered by some search criteria. We should be able to show a page of items at a time (usually called paging) and it's nice to be able to sort the items by clicking on the column headers (usually called column ordering). The one thing you really don't want to do when faced with these requirements is get the entire set back from the database and then filter,page and sort them either on the web server or the client.

Today I want to show you a simple framework for doing this with the MVC Framework. The MvcContrib project already has an excellent grid HTML helper written by Jeremy Skinner, so I've merely used the power of LINQ and extended the Grid to provide paging and column ordering.

Here's the page I want to produce. It shows orders from the Northwind database, you can filter by customer and shipper, click on the columns to order by the column's contents and page through the results. They all work together, so the pager remembers the filter and the column order and the column orderer remembers the page and filter:

[画像:pagingSorting1]

Here's the SQL that produced this page:

exec sp_executesql N'SELECT [t3].[OrderID], [t3].[CustomerID], [t3].[EmployeeID], 
[t3].[OrderDate], [t3].[RequiredDate], [t3].[ShippedDate], [t3].[ShipVia], 
[t3].[Freight], 
[t3].[ShipName], [t3].[ShipAddress], [t3].[ShipCity], [t3].[ShipRegion], 
[t3].[ShipPostalCode], [t3].[ShipCountry]
FROM (
 SELECT ROW_NUMBER() OVER (ORDER BY [t2].[CompanyName]) AS [ROW_NUMBER], 
[t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], 
[t0].[RequiredDate], 
[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], 
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], 
[t0].[ShipCountry]
 FROM [dbo].[Orders] AS [t0]
 LEFT OUTER JOIN [dbo].[Shippers] AS [t1] ON [t1].[ShipperID] = [t0].[ShipVia]
 LEFT OUTER JOIN [dbo].[Customers] AS [t2] ON [t2].[CustomerID] = [t0].[CustomerID]
 WHERE ([t1].[ShipperID]) = @p0
 ) AS [t3]
WHERE [t3].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t3].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=140,@p2=20

The filtering (by Shipper), paging and ordering are all executed on the database. Only twenty rows are returned by the query.

The key to making this work is a re-useable definition of the columns we want to display. Here's my one for this page:

public class OrderTableInfo : TableInfo<Order>
{
 public OrderTableInfo()
 {
 AddColumn(order => order.OrderID, "Order Id");
 AddColumn(order => order.Customer.CompanyName, "Customer");
 AddColumn(order => order.Shipper.CompanyName, "Shipper");
 AddColumn(order => order.OrderDate, "Order Date");
 }
}

I've created a new TableInfo class. Simply inherit this and add the columns you want to be displayed in the constructor.  Each column definition is simply a Expression<Func<T, TParam>> that returns the property of the root object that we want to display. The expressions are reused both to grab the value of the property we want to display and as input to the OrderBy clause that does the column ordering.

Here's the controller action for this page:

[HandleError]
public class HomeController : Controller
{
 private readonly NorthwindDataContext db = new NorthwindDataContext();
 private const int pageSize = 20;
 public ActionResult Index(string customerId, int? shipperId, FormCollection form)
 {
 // hack because property binder favors queryString over form
 if (form["CustomerId"] != null) customerId = form["CustomerId"];
 if (form["ShipperId"] != null) shipperId = (form["ShipperId"].Length == 0) ? 
 (int?)null : 
 int.Parse(form["ShipperId"]);
 var criteria = new OrderSearchCriteria
 {
 CustomerId = customerId,
 ShipperId = shipperId
 };
 var orderTableInfo = new OrderTableInfo();
 var orders = db.Orders
 .ThatMatch(criteria)
 .SortColumns(orderTableInfo, Request.QueryString)
 .ToPagedList(Request.QueryString.PageNumber(), pageSize);
 ViewData["Title"] = "Orders";
 var customers = db.Customers;
 var shippers = db.Shippers;
 return View("Index", new NorthwindViewData()
 .WithOrders(orders)
 .WithOrderTableInfo(orderTableInfo)
 .WithOrderSearchCriteria(criteria)
 .WithCustomers(customers)
 .WithShippers(shippers));
 }
 public ActionResult About()
 {
 ViewData["Title"] = "About Page";
 return View();
 }
}

As you can see we create a new instance of the OrderTableInfo that describes the table we want to create. We then get the orders to display from the database. The SortColumns(orderTableInfo, Request.QueryString) extension method looks in the query string for a key called  'sortby' and matches its value to the column name described in OrderTableInfo. If it finds a matching value it uses the expression from OrderTableInfo in an OrderBy clause. ToPagedList(Request.QueryString.PageNumber(), pageSize) appends Skip() and Take() clauses to do the paging. We then pass the list of orders and the orderTableInfo to the view.

Here's the section of the view that renders the table:

[画像:pagingSortingView1]

Pager is an extension method on HtmlHelper that uses the IPagedList from MvcContrib to render the  pager. The table of orders is rendered by the MvcContrib grid, but instead of specifying our columns here we use the column specification from OrderTableInfo. I've created a new HtmlHelper extension method 'CreateGridColumnBuilder' that creates the Action<IRootGridColumnBuilder<T>> that defines the columns for the Grid.

You'll notice that the 'Order Date' shown in the screen shot above is a short date time (in UK format). So what happens when you want to add formatting to the column? Maybe adding a link column for example. I've provided an overloaded version of TableInfo's AddColumn method that takes a full MvcContrib grid column definition that you can use like this:

public class OrderTableInfo : TableInfo<Order>
{
 public OrderTableInfo()
 {
 AddColumn(order => order.OrderID, "Order Id");
 AddColumn(order => order.Customer.CompanyName, "Customer");
 AddColumn(order => order.Shipper.CompanyName, "Shipper");
 AddColumn(order => order.OrderDate, 
 col => col.For(order => order.OrderDate.Value.ToShortDateString()), 
 "Order Date");
 }
}

Note the Order Date now defines both the property we want to sort by and the full column definition. The problem here is that there's nothing to stop you from providing totally different values for these, so take care :)

So to sum up, just follow these steps:

1. Create a definition for your table by inheriting from TableInfo<T>
2. Chain SortColumns and ToPaged list to your IQueryable<T> expression that returns your items.
3. Pass the table definition and items to the view
4. Use the Pager and MvcContrib Grid to render the view. Pass Html.CreateGridColumnBuilder(<table definition>) to the Grid.

You can download the full source code for this example here:

http://static.mikehadlow.com/Mike.PagingSorting.zip

LINQ has revolutionised the way we do data access. Being able to fluently describe queries in C# means that you never have to write a single line of SQL again. Of course LINQ isn't the only game in town. NHibernate has a rich API for describing queries as do most mature ORM tools. But to be a player in the .NET ORM game you simply have to provide a LINQ IQueryable API. It's been really nice to see the NHibernate-to-LINQ project take off and apparently LLBLGen Pro has an excellent LINQ implementation too.

Now that we can write our queries in C# it should mean that we can have completely DRY business logic. No more duplicate rules, one set in SQL, the other in the domain classes. But there's a problem: LINQ doesn't understand IL. If you write a query that includes a property or method, LINQ-to-SQL can't turn the logic encapsulated by it into a SQL statement.

To illustrate the problem take this simple schema for an order:

[画像:queriable_scema]

Let's use the LINQ-to-SQL designer to create some classes:

[画像:queriable_classes]

Now lets create a 'Total' property for the order that calculates the total by summing the order lines' quantities times their product's price.

public decimal Total
{
get
{
 return OrderLines.Sum(line => line.Quantity * line.Product.Price);
}
}

Here's a test to demonstrate that it works

[Test]
public void Total_ShouldCalculateCorrectTotal()
{
const decimal expectedTotal = 23.21m + 14.30m * 2 + 7.20m * 3;
var widget = new Product { Price = 23.21m };
var gadget = new Product { Price = 14.30m };
var wotsit = new Product { Price = 7.20m };
var order = new Order
{
 OrderLines =
 {
 new OrderLine { Quantity = 1, Product = widget},
 new OrderLine { Quantity = 2, Product = gadget},
 new OrderLine { Quantity = 3, Product = wotsit}
 }
};
Assert.That(order.Total, Is.EqualTo(expectedTotal));
}

Now, what happens when we use the Total property in a LINQ query like this:

[Test]
public void Total_ShouldCalculateCorrectTotalOfItemsInDb()
{
var total = dataContext.Orders.Select(order => order.Total).First();
Assert.That(total, Is.EqualTo(expectedTotal));
}

The test passes, but when we look at the SQL that was generated by LINQ-to-SQL we get this:

SELECT TOP (1) [t0].[Id]
FROM [dbo].[Order] AS [t0]
SELECT [t0].[Id], [t0].[OrderId], [t0].[Quantity], [t0].[ProductId]
FROM [dbo].[OrderLine] AS [t0]
WHERE [t0].[OrderId] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
SELECT [t0].[Id], [t0].[Price]
FROM [dbo].[Product] AS [t0]
WHERE [t0].[Id] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
SELECT [t0].[Id], [t0].[Price]
FROM [dbo].[Product] AS [t0]
WHERE [t0].[Id] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
SELECT [t0].[Id], [t0].[Price]
FROM [dbo].[Product] AS [t0]
WHERE [t0].[Id] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [3]

LINQ-to-SQL doesn't know anything about the Total property, so it does as much as it can. It loads the Order. When the Total property executes, OrderLines is evaluated which causes the order lines to be loaded with a single select statement. Next each Product property of each OrderLine is evaluated in turn causing each Product to be selected individually. So we've had five SQL statements executed and the entire Order object graph loaded into memory just to find out the order total. Yes of course we could add data load options to eagerly load the entire object graph with one query, but we would still end up with the entire object graph in memory. If all we wanted was the order total this is very inefficient.

Now, if we construct a query where we explicitly ask for the sum of order line quantities times product prices, like this:

[Test]
public void CalculateTotalWithQuery()
{
var total = dataContext.OrderLines
 .Where(line => line.Order.Id == 1)
 .Sum(line => line.Quantity * line.Product.Price);
Assert.That(total, Is.EqualTo(expectedTotal));
}

We get this SQL

SELECT SUM([t3].[value]) AS [value]
FROM (
SELECT (CONVERT(Decimal(29,4),[t0].[Quantity])) * [t2].[Price] AS [value], [t1].[Id]
FROM [dbo].[OrderLine] AS [t0]
INNER JOIN [dbo].[Order] AS [t1] ON [t1].[Id] = [t0].[OrderId]
INNER JOIN [dbo].[Product] AS [t2] ON [t2].[Id] = [t0].[ProductId]
) AS [t3]
WHERE [t3].[Id] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

One SQL statement has been created that returns a scalar value for the total. Much better. But now we've got duplicate business logic. We have definition of the order total calculation in the Total property of Order and another in the our query.

So what's the solution?

What we need is a way of creating our business logic in a single place that we can use in both our domain properties and in our queries. This brings me to two guys who have done some excellent work in trying to solve this problem: Fredrik Kalseth and Luke Marshall. I'm going to show you Luke's solution which is detailed in this series of blog posts.

It's based on the specification pattern. If you've not come across this before, Ian Cooper has a great description here. The idea with specifications is that you factor out your domain business logic into small composable classes. You can then test small bits of business logic in isolation and then compose them to create more complex rules; because we all know that rules rely on rules :)

The neat trick is to implement the specification as a lambda expression that can be executed against in-memory object graphs or inserted into an expression tree to be compiled into SQL.

Here's our Total property as a specification, or as Luke calls it, QueryProperty.

static readonly TotalProperty total = new TotalProperty();
[QueryProperty(typeof(TotalProperty))]
public decimal Total
{
get
{
 return total.Value(this);
}
}
class TotalProperty : QueryProperty<Order, decimal>
{
public TotalProperty()
 : base(order => order.OrderLines.Sum(line => line.Quantity * line.Product.Price))
{
}
}

We factored out the Total calculation into a specification called TotalProperty which passes the rule into the constructor of the QueryProperty base class. We also have a static instance of the TotalProperty specification. This is simply for performance reasons and acts a specification cache. Then in the Total property getter we ask the specification to calculate its value for the current instance.

Note that the Total property is decorated with a QueryPropertyAttribute. This is so that the custom query provider can recognise that this property also supplies a lambda expression via its specification, which is the type specified in the attribute constructor. This is the main weakness of this approach because there's an obvious error waiting to happen. The type passed in the QueryPropertyAttribute has to match the type of the specification. It's also very invasive since we have various bits of the framework (QueryProperty, QueryPropertyAttribute) surfacing in our domain code.

These days simply everyone has a generic repository and Luke is no different. His repository chains a custom query provider before the LINQ-to-SQL query provider that knows how to insert the specification expressions into the expression tree. We can use the repository like this:

[Test]
public void TotalQueryUsingRepository()
{
var repository = new RepositoryDatabase<Order>(dataContext);
var total = repository.AsQueryable().Select(order => order.Total).First();
Assert.That(total, Is.EqualTo(expectedTotal));
}

Note how the LINQ expression is exactly the same as one we ran above which caused five select statements to be executed and the entire Order object graph to be loaded into memory. When we run this new test we get this SQL:

SELECT TOP (1) [t4].[value]
FROM [dbo].[Order] AS [t0]
OUTER APPLY (
SELECT SUM([t3].[value]) AS [value]
FROM (
 SELECT (CONVERT(Decimal(29,4),[t1].[Quantity])) * [t2].[Price] AS [value], [t1].[OrderId]
 FROM [dbo].[OrderLine] AS [t1]
 INNER JOIN [dbo].[Product] AS [t2] ON [t2].[Id] = [t1].[ProductId]
 ) AS [t3]
WHERE [t3].[OrderId] = [t0].[Id]
) AS [t4]

A single select statement that returns a scalar value for the total. It's very nice, and with the caveats above it's by far the nicest solution to this problem that I've seen yet.

I've recently been asked to architect and build my first major Silverlight based application. The requirement is for an on-line accommodation booking service for a large multi-site educational institution. As a fully paid up member of the ORM mafia I really wanted to have the same persistence ignorance on the client that I enjoy on the server. The only way I could see to achieve this was building some kind of generic web service front end on top of my repository model along with a client side proxy that behaved in much the same way. It looked like a lot of work.

Enter ADO.NET Data Services. The first thing I'll say about it, is that the name is really crap. I can't see how this has anything remotely to do with ADO.NET. Fundamentally it's a combination of a REST style service over anything that provides an IQueryable implementation, plus client side proxies that expose an IQueryable API. Here's a diagram:

[画像:ado_net_architecture]

The Data Context can be any class that has a set of properties that return IQueryable<T> and that implements System.Data.Services.IUpdatable and System.Data.Services.IExpandProvider. The IQueryable<T> properties give the entities that are exposed by the REST API, IUpdatable supports (as you'd expect) posting updates back to the Data Context and IExpandProvider supports eagerly loading your object graph. The REST API has lazy load semantics by default, with all entity references being expressed as REST URLs.

It's very nicely thought out and easy to use. You simply create a new DataService<T> where T is your Data Context and the service auto-magically exposes your data as a RESTfull web service. I've been playing with the Northwind example. You just create a new Web Application in Visual Studio, add a "LINQ to SQL classes" item to your project and drag all the Northwind tables onto the LINQ-to-SQL designer. Next add an "ADO.NET Data Service" to the project then it's just a question of entering the name of the NorthwindDataContext class as the type parameter of the DataService:

[画像:ado_net_DataService]

Note I've also added a ServiceBehaviour to show exception details in faults. Now when I hit F5 I get this error:

[画像:ado_net_ds_request_error]

The way that ADO.NET Data Services works an entity's primary key is pretty simplistic. It just looks for properties that end with ID and gets confused by CustomerCustomerDemos. Shawn Wildermuth explains about this issue here. So I'll just remove everything from the designer except for tables with an obvious Id, and now I get this:

[画像:ado_net_toplevel]

It's easy to explore the model by playing with the URL...

Currently only the much loved Entity Framework and LINQ to SQL provide a Data Context that you can use with this. David Hayden shows how to set up a LINQ to SQL project here. However, Shawn Wildermuth has been doing some great work exposing LINQ to NHibernate as a Data Service friendly Data Context. You can read about it here, here, here and here. His NHibernateContext is now part of the LINQ to NHibernate project. My project is using NHibernate as it's ORM, so I'll posting more about this soon.

You can download my sample using LINQ to SQL and Entity Framework here:

http://static.mikehadlow.com/Mike.DataServices.zip

One of the requirements of Suteki Shop is that admin users should be able to create various new entities (categories, products, content etc) before they are published on the public web site. To this end I created a simple interface, IActivatable:

public interface IActivatable
{
 bool IsActive { get; set; }
}

As you can see it's got a single property 'IsActive'. Any entity that needs to be active/inactive implements it. So my product class' partial class definition looks like this:

public partial class Product : IOrderable, IActivatable, IUrlNamed
{
 ....
}

I'll explain IOrderable and IUrlNamed later, but they do similar things. Each entity has a matching table in the database and each entity that implements IActivatable also has a bit column IsActive. Here are Category and Product:

Product_Category_DB

When I drag these tables onto the LINQ-to-SQL design surface, Product and Category classes are created with IsActive properties. I can make their matching partial classes implement IActivatable and the designer IsActive property satisfies the implementation requirement.

Now I can write a reusable LINQ query extension method for any IActivatable to filter only active items:

public static IQueryable<T> Active<T>(this IQueryable<T> items) where T : IActivatable
{
 return items.Where(item => item.IsActive);
}

Now every time I need a list of active items I can just chain the query with Active(),

var products = productRepository.GetAll().Active();

Remember the IOrderable and IUrlNamed interfaces that the Product partial class implemented? They work in exactly the same way. I explained IOrderable in a previous post, it's used to create a generic service to order and re-order lists of entities. IUrlNamed provides a generic way of querying entities given a unique string from the URL. For example, Suteki Shop has nice URLs for products: http://sutekishop.co.uk/product/East_End_Trilby. So there's an extension method:

public static T WithUrlName<T>(this IQueryable<T> items, string urlName) where T : IUrlNamed
{
 T item = items
 .SingleOrDefault(i => i.UrlName.ToLower() == urlName.ToLower());
 if (item == null) throw new ApplicationException("Unknown UrlName '{0}'".With(urlName));
 return item;
}

That allows us to look up products like this:

public ActionResult Item(string urlName)
{
 Product product = productRepository.GetAll().WithUrlName(urlName);
 return View("Item", ShopView.Data.WithProduct(product));
}

var dataContext = new NorthwindDataContext();
var theOrder = dataContext.GetTable<Order>().Single(order => order.OrderID == 10248);
Console.WriteLine("Order Date: {0}\r\nShipper: {1}\r\nCustomer Name: {2}\r\n", 
 theOrder.OrderDate, 
 theOrder.Shipper.CompanyName,
 theOrder.Customer.ContactName);
Console.WriteLine("Customer Demographic:");
foreach (var customerCustomerDemo in theOrder.Customer.CustomerCustomerDemos)
{
 Console.Write("{0}, ", customerCustomerDemo.CustomerDemographic.CustomerDesc);
}
Console.WriteLine("\r\n");
foreach (var orderDetail in theOrder.Order_Details)
{
 Console.WriteLine("Product Name: {0},\r\nSuppler Name: {1},\r\nCategory {2},\r\nQuantity {3}\r\n\r\n",
 orderDetail.Product.ProductName,
 orderDetail.Product.Supplier.CompanyName,
 orderDetail.Product.Category.CategoryName,
 orderDetail.Quantity);
}

using System;
using System.Data.Linq;
using System.Linq;
namespace Mike.Northwind
{
 class Program
 {
 static void Main(string[] args)
 {
 var dataContext = new NorthwindDataContext();
 var options = new DataLoadOptions();
 options.LoadWith<Order>(order => order.Shipper);
 options.LoadWith<Order>(order => order.Customer);
 options.LoadWith<Customer>(customer => customer.CustomerCustomerDemos);
 options.LoadWith<CustomerCustomerDemo>(ccd => ccd.CustomerDemographic);
 options.LoadWith<Order>(order => order.Order_Details);
 options.LoadWith<Order_Detail>(orderDetail => orderDetail.Product);
 options.LoadWith<Product>(product => product.Supplier);
 options.LoadWith<Product>(product => product.Category);
 dataContext.LoadOptions = options;
 var theOrder = dataContext.GetTable<Order>().Single(order => order.OrderID == 10248);
 Console.WriteLine("Order Date: {0}\r\nShipper: {1}\r\nCustomer Name: {2}\r\n", 
 theOrder.OrderDate, 
 theOrder.Shipper.CompanyName,
 theOrder.Customer.ContactName);
 Console.WriteLine("Customer Demographic:");
 foreach (var customerCustomerDemo in theOrder.Customer.CustomerCustomerDemos)
 {
 Console.Write("{0}, ", customerCustomerDemo.CustomerDemographic.CustomerDesc);
 }
 Console.WriteLine("\r\n");
 foreach (var orderDetail in theOrder.Order_Details)
 {
 Console.WriteLine("Product Name: {0},\r\nSuppler Name: {1},\r\nCategory {2},\r\nQuantity {3}\r\n\r\n",
 orderDetail.Product.ProductName,
 orderDetail.Product.Supplier.CompanyName,
 orderDetail.Product.Category.CategoryName,
 orderDetail.Quantity);
 }
 }
 }
}

I thought it would be nice to be able to produce a CSV file by doing something like this:

string ordersCsv = orderRepository.GetAll().Select(o => new 
{ 
 OrderId = o.OrderId,
 Email = o.Email,
 OrderStatus = o.OrderStatus.Name,
 CreatedDate = o.CreatedDate,
 Total = o.Basket.Total
}).AsCsv();

So here's an extension method to do just that:

public static string AsCsv<T>(this IEnumerable<T> items)
 where T : class
{
 var csvBuilder = new StringBuilder();
 var properties = typeof (T).GetProperties();
 foreach (T item in items)
 {
 string line = properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray().Join(",");
 csvBuilder.AppendLine(line);
 }
 return csvBuilder.ToString();
}
private static string ToCsvValue<T>(this T item)
{
 if (item is string)
 {
 return "\"{0}\"".With(item.ToString().Replace("\"", "\\\""));
 }
 double dummy;
 if (double.TryParse(item.ToString(), out dummy))
 {
 return "{0}".With(item);
 }
 return "\"{0}\"".With(item);
}

It's work with anything that implements IEnumerable<T>, that includes the results of LINQ-to-SQL queries , arrays, List<T> and pretty much any kind of collection. Here's it's unit test:

[TestFixture]
public class EnumerableExtensionsTests
{
 [Test]
 public void GetCsv_ShouldRenderCorrectCsv()
 {
 IEnumerable<Thing> things = new List<Thing>()
 {
 new Thing
 {
 Id = 12,
 Name = "Thing one",
 Date = new DateTime(2008, 4, 20),
 Child = new Child
 {
 Name = "Max"
 }
 },
 new Thing
 {
 Id = 13,
 Name = "Thing two",
 Date = new DateTime(2008, 5, 20),
 Child = new Child
 {
 Name = "Robbie"
 }
 }
 };
 string csv = things.Select(t => new { Id = t.Id, Name = t.Name, Date = t.Date, Child = t.Child.Name }).AsCsv();
 Assert.That(csv, Is.EqualTo(expectedCsv));
 }
 const string expectedCsv = 
@"12,""Thing one"",""20/04/2008 00:00:00"",""Max""
13,""Thing two"",""20/05/2008 00:00:00"",""Robbie""
";
 public class Thing
 {
 public int Id { get; set; }
 public string Name { get; set; }
 public DateTime Date { get; set; }
 public Child Child { get; set; }
 }
 public class Child
 {
 public string Name { get; set; }
 }
}

A while ago I talked about using the IRepository<T> pattern with LINQ to SQL. One of the methods of my repository is GetById. The slight difficulty here is that we need to discover the primary key property of the (generated) type at runtime so we can't use a vanilla lambda expression in the Where clause. Before I used the DynamicQueriable helper library, but I've just re-written the function using an explicit expression tree which removes the need to reference the helper library.

public T GetById(int id)
{
 var itemParameter = Expression.Parameter(typeof(T), "item");
 var whereExpression = Expression.Lambda<Func<T, bool>>
 (
 Expression.Equal(
 Expression.Property(
 itemParameter,
 typeof(T).GetPrimaryKey().Name
 ),
 Expression.Constant(id)
 ),
 new ParameterExpression[] { itemParameter }
 );
 return dataContext.GetTable<T>().Where(whereExpression).Single();
}

Here is the extension method that finds the primary key:

public static PropertyInfo GetPrimaryKey(this Type entityType)
{
 foreach (PropertyInfo property in entityType.GetProperties())
 {
 ColumnAttribute[] attributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), true);
 if (attributes.Length == 1)
 {
 ColumnAttribute columnAttribute = attributes[0];
 if (columnAttribute.IsPrimaryKey)
 {
 if (property.PropertyType != typeof(int))
 {
 throw new ApplicationException(string.Format("Primary key, '{0}', of type '{1}' is not int",
 property.Name, entityType));
 }
 return property;
 }
 }
 }
 throw new ApplicationException(string.Format("No primary key defined for type {0}", entityType.Name));
}