LINQ to SQL

In my previous two articles, I talked you through some solutions for handling many-to-many relationships in LINQ-to-SQL. We examined solutions ranging from simple IEnumerable across the junction table, to a fully-fledged IList complete with Add/Insert and Remove/Delete support.

Of course, many-to-many relationships aren't just a problem for ORM. They're a diverse minefield of misunderstanding - and nobody is more at risk of sinking than your users. In this article, I examine some of the different ways that these complex relationships are presented to users.

Last time, I examined one of the limitations of LINQ to SQL - the lack of automatic tools to cut out the junction table in a many-to-many relationship. I examined the reasons behind this limitation and concluded that providing automation for the simplest case ( foreach (Product p in order.Products) ) left us with two very important issues:

• We currently don't have a way of sending changes to the object model back to the database - order.Products.Add( ... won't result in a new record being added to the junction table.
• We need a way to access properties of the relationship beyond references to the two related entities (e.g. "Quantity" in the Order_Detail table relating Orders and Products).

In this article, I examine ways to solve these issues.

LINQ is a new component in .NET 3.5 that has a lot of .NET developers very excited. It provides a simple and flexible method of querying object-encapsulated data. The LINQ to SQL provider in particular has been getting a lot of attention since it can be used to query SQL Server databases using the same simple syntax that is used to query collections in memory - and furthermore can send updates made to the data back to the database. This feature, combined with very slick integration into Visual Studio 2008, provides a very strong motivation for developers to make the leap to .NET 3.5.

Unfortunatley, LINQ to SQL has a few limitations, the most obvious of which is the inability to directly handle the common representation of a many-to-many relationship. In this series, I look at the issue and some of the ways of circumventing it.