Find it and lose find it and lose it

Periodically, I need the ability to fetch any single item from an arbitrary Linq to SQL ITable based on the primary key value. I've seen this code elsewhere on the Internet, but every time I go looking for it I struggle to find it. So now I'm saving it here once for all. In this case the expectation is that my primary key ID is an integer, but int could easily be replaced by GUID or anything else.

public static class ITableHelpers
{
  public static object SingleOrDefaultByID(this ITable table, int id)
  {
    var param = Expression.Parameter(table.ElementType, "e");
    var predicate = Expression.Lambda(
      Expression.Equal(
        Expression.Property(param, table.PrimaryKey().Name),
        Expression.Constant(id)
      ),
      param
    );

    var call = Expression.Call(typeof(Queryable), "SingleOrDefault", new Type[] { table.ElementType }, table.Expression, predicate);
    return table.Provider.Execute(call);
  }

  public static System.Reflection.PropertyInfo PrimaryKey(this ITable table)
  {
    var matchingProperties = table.ElementType.GetProperties().Where(p => p.GetCustomAttributes(true).OfType<System.Data.Linq.Mapping.ColumnAttribute>().Any(c => c.IsPrimaryKey));

    if (matchingProperties.Count() != 1)
      throw new NotSupportedException(String.Format("Class '{0}' does not contain exactly one property that is a Linq to SQL primary key.", table.ElementType.FullName));

    return matchingProperties.Single();
  }
}

9 LINQ to SQL Tips by Dinesh Kulkarni

1. How to map an enum
2. How to use common base class for all entities
3. Deferred (lazy) or eager loading of related objects with stored procs
4. Use DeleteOnNull if you want to delete object with null FK
5. Using stored procs that return multiple results
6. How to refresh given DataContext won't stomp on your objects
7. Minimal update when you don't want optimistic concurrency check
8. How to (and why) create a partial class in the designer to augment generated code
9. Understanding DataContext's internal caching

Dinesh Kulkarni is one of the developers behind my favorite .NET 3.5 feature, linq to sql (aka dlinq). He has a series of tips that are not organized so I decided to organize them here. Anyone who uses or plans to use linq to sql should read every tip because these tips address the most common questions he has been asked.

SQL Like in LinqToSql

The more I learn about Linq and it's SQL Server specific variant LinqToSql the more I giggle with glee. I recently learned how functionality similar to SQL's Like clause is better expressed from linq to sql.

I have known about the use of SqlMethods.Like(...) for a while, but only recently realized a better way after reading another's code. Here's the SqlMethods.Like() way:

list.Where(o => SqlMethods.Like(o.Name, "%" + Name + "%"))

Here is that same statement more appropriately expressed:

list.Where(o => o.Name.Contains(Name))

Clever eh? Just like I'd bake it if I were using LinqToObject. Now I'm not constrained to the LinqToSql context. Good news continues with the string class' .StartsWith(...) and .EndsWith(...) methods working exactly as one might expect.

This also goes to show just how far the LinqToSql team went to blend into the Linq paradigm.