Linq Expressions on an Interface

One thing I found myself doing quite a bit an often causing logic errors was comparing
dates in Linq queries. For example, if I want to get games that occur between a date range, I might be
doing something like this :

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
				where comp.Date >= startDate && comp.Date <= endDate
				select comp);
	}

I then want to get a list of tournaments that occur between a date range, and I might
essentially rewrite this code using a couple different variables. Rather, I can implement
a common interface on these classes and have an expression applied to all classes that
implement this interface.

	public interface IDate
	{
		DateTime Date { get; set; }
	}

	public partial class Competition : DataBase, IDate
	{
		...
	}

	public static class Expressions
	{
        public static Expression<Func<T, bool>> GetBetweenDates<T>(DateTime start, DateTime end)
            where T : IDate
        {
            return (t => t.Date >= start && t.Date <= end);
        }
	}

Then, rather than rewriting this for each class with a date, I can just apply this expression
to my queries.

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
			select comp).Where(
                             Expressions.GetBetweenDates<Competition>(startDate, endDate));
	}

Using Custom Properties Inside LINQ to SQL Queries

One thing that initially caused me some trouble with Linq to SQL was that properties
and functions defined on the object cannot be translated to SQL. For example, this code throws an
“The member ‘Table1.DisplayName’ has no supported translation to SQL.” exception when executed.

public string DisplayName
   {
      get
      {
         return this.NameFirst + " " + this.NameLast;
      }
}

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.DisplayName);
}

One simple solution is to just define your sort option each time it needs used.

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.NameFirst + " " + t.NameLast);
}

This option is pretty tedious to write and maintain, especially if you’re going to be using the function often.

Another option is to define a System.Linq.Expression, which is a lambda expression that can be converted to SQL.

static Expression<Func<Table1, string>> DisplayNameExpr = t => t.NameFirst + " " + t.NameLast;

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(Table1.DisplayNameExpr);
}

Note that if you do choose this way, the “DisplayName” is essentially defined in two different places. To solve this problem, use the expression to define the property.

{
   get
   {
      var nameFunc = DisplayNameExpr.Compile(); // compile the expression into a Function
      return nameFunc(this); // call the function using the current object
   }
}

Note this method can also be used to define functions that accept arguments,  so the following code also works as expected.

static Expression<Func<Table1, bool>> HasReq(int numReq)
{
   return (t => (t.IntItem + t.AnotherInt) > numReq);
}

public static IQueryable<Table1> GetWithReq(int req)
{
   return (from t in DataContext.Table1s
             select t).Where(Table1.HasReq(req));
}