Linq performance problems with deferred execution causing multiple selects against the DB

We have some really good performance tests that run on every checkin providing
the team with an excelent view of how the performance of the software changes
due to different changes in the code base. We recently saw a drop in performance
and we tracked it down to a problem in our data layer.

The problem we encountered was within LINQ to SQL but will be a problem with
other types of LINQ if your not careful.

Personally i consider LINQtoSQL to be dangerous for a number of reasons and
would actually prefer not to be using it but we are where we are and we as a
team just need to be weary of LINQToSQL and its quirks.

This quirk is when the deferred execution of a linq to sql enumeration is
causing multiple selects against the DB.

As this code demonstrates.

public IList<IndustrySector> GetIndustrySectorsByArticleId(int articleId)
{
  var industrySectorsIds = GetIndustrySectorIds(articleId);
  return ByIds(industrySectorsIds);
}

private IEnumerable<int> GetIndustrySectorIds(int articleId)
{
  var articleIndustrySectorsDaos = databaseManager.DataContext.ArticleIndustrySectorDaos.Where(x => x.ArticleID == articleId);
  return articleIndustrySectorsDaos.Select(x => x.IndustrySectorID);
}

public IList<IndustrySector> ByIds(IEnumerable<int> industrySectorIds)
{
  return All().Where(i => industrySectorIds.Contains(i.Key)).Select(x => x.Value).ToList();
}


public IEnumerable<IndustrySector> All()
{
  //work out all the industry sectors valid for this user in the system, this doesn't make a DB call
}
So in the end this all causes an number of identical queries to be fired against the DB,
industrySectorsIds.count number of calls to the DB to be precise.
This is the select we were seeing:

exec sp_executesql N'SELECT [t0].[IndustrySectorID]
FROM [dbo].[tlnk_Article_IndustrySector] AS [t0]
WHERE [t0].[ArticleID] = @p0',N'@p0 int',@p0=107348
By forcing the ByIds() method to retreive all the ids from the DB before iterating All()
will mean that they are loaded into memory once only.

public IList<IndustrySector> ByIds(IEnumerable<int> industrySectorIds)
{
  var sectorIds = industrySectorIds.ToList();
  return All().Where(i => sectorIds.Contains(i.Key)).Select(x => x.Value).ToList();
}
now you only get one call to the DB, thanks LINQtoSQL, your great.

Linq to Sql many to many delete

My current project is using linq2sql, we had a small problem deleting a record from a many to many relationship that used a link table which only contained 2 required foreign key columns.

product with productId
order with orderId
productOrder with 2 foreign keys productId and orderId

System.Data.Linq.DuplicateKeyException : Cannot add an entity with a key that is already in use.
System.InvalidOperationException : An attempt was made to remove a relationship between a product and a productOrder.

However, one of the relationship's foreign keys (productOrder.productId) cannot be set to null.

The trick was to add DeleteOnNull="true" to the association

to delete modify collections (many to many link tables)

<Table Name="dbo.product" Member="product">
  <Type Name="product">
    <Column Name="productId" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
    <Column Name="Title" Type="System.String" DbType="VarChar(255)" CanBeNull="true" />
    <Association Name="product_productOrder" Member="productOrders" ThisKey="productId" OtherKey="productId" Type="productOrder" DeleteOnNull="true"/>
  </Type>
</Table>

<Table Name="dbo.productOrders" Member="productOrders">
  <Type Name="productOrder">
    <Column Name="productId" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    <Column Name="orderId" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    <Association Name="product_productOrder" Member="productId" ThisKey="productId" OtherKey="productId" Type="product" IsForeignKey="true" DeleteOnNull="true"/>
    <Association Name="order_productOrder" Member="order" ThisKey="orderId" OtherKey="orderId" Type="order" IsForeignKey="true" DeleteOnNull="true"/>
  </Type>
</Table>

product.ProductOrders.Clear();
product.ProductOrders.AddRange(myNewProductOrders);

I found help on this matter here: http://blogs.msdn.com/b/bethmassi/archive/2007/10/02/linq-to-sql-and-one-to-many-relationships.aspx

Brief intro to LINQ

Ive recently given a breif tutorial to the guys on our team on LINQ (Language integrated query)

As with Generics, LINQ is a large subject that i cant write up properly without spending quite a lareg amount of time on, so i'll give a breif overview with an example of a common usage in the context of a generic list.

Overview

LINQ is a technology (or language construct) that allows you to process data using functional programming logic to perform an operation. The LINQ API is an attempt to provide a consistent programming model that allows you to manipulate data. Using LINQ we are able to create directly within the C# programming language entities called query expressions, these query expressions are created from numerous query operators that have been intentionally designed to look and feel similar (but not identical) like SQL. Using LINQ you can interact with many sources of data, listed below:

LINQ implementations
LINQ to objects
Linq to SQL
Linq to entities
Linq to XML
There are other LINQ enabled technologies but these are the most common, for a grater list look online.

All of this means that you can interact with an XML dom in the same manner as you would with a list of objects or an ADO record set without having to learn different syntax for either.

LINQ Example

List names = new List(){"George", "William Smith", "bob", "Fred"};
IEnumerable subSetNames = from n in names
where n.Length >= 4
order by n
select n;
foreach(string name in subSetNames)
{
Console.Write(name);
}

The code above firstly creates a list of strings and places then in the names variable
Then it takes that collection filters it where the length is greater than or equal to 4
Sorts it based on the string
Puts the resulting collection in the variable subSetNames?
Finally it loops through all the strings in subSetNames? and prints them out

The resulting string is "Fred George William Smith" In this example the collection being acted on was a simple list of strings but it could be a collection of any type of object, or indeed an ADO record set, or an XML document, its a very powerful technique and allows for very clear functional logic (no complex nested loops and temporary collections for ordering etc).

This topic is massive and so im not going to go into it in any more depth, but in the most part ive not used any LINQ that is anymore complex than the example listed above. more advanced features such as lambdas and extension methods start to make things complex and ive so far managed to keep clear of using these techniques, for more info search google for the answer.

LINQ Grouping

By way of an example this code below demonstrates the power of LINQ used with a simple grouping command

string[] names = {"Albert", "Burke", "Connor", "David", "Everett", "Frank", "George", "Harris"};

//Defining the GroupBy logic (here it is lengthwise)
var groups = names.GroupBy(s => s.Length);

//Iterate through the collection created by the Grouping
foreach(IGrouping group in groups)
{
//Branch on the condition decided
Console.WriteLine("Strings of Length {0}", group.Key);
//Actual results
foreach(string value in group)
Console.WriteLine(" {0}", value);
}


Produces this output

Strings of Length 6
Albert
Connor
George
Harris
Strings of Length 5
Burke
David
Frank
Strings of Length 7
Everett

LINQ Resources
MSDN 101 LINQ samples