Mapping expressions in LINQ-to-sql abstract class

Question

I have an abstract class that is inherited by two classes. Both classes represent tables in the database. I am having troubles mapping expressions though in the abstract class and therefore I keep getting exceptions that it cannot be translated to SQL. All questions I found in Stackoverflow are talking about columns which is already working for me.

Below is a very simple code that shows what I mean. Car and Motorcycle have completely separate implementations of _isNew Expression.

public abstract class Vehicle  {
     public abstract boolean IsNew;
}

public partial class Car: Vehicle {
      public override boolean IsNew {
         get { _isNew.Invoke(this); }
      }
}
public partial class Motorcycle: Vehicle {
      public override boolean IsNew {
         get { _isNew.Invoke(this); }
      }
}

I would like to be able to call either _isNew expression or IsNew property on an IQueryable and yet it runs the _isNew of the Car class in case Vehicle is type of Car. Is there anyway I can accomplish that? All solutions I tried caused an exception that it cannot be translated to SQL.

Answer 1

Before i get into your question, you should probably check up on the best practices of C# properties concerning exceptions.

You could just eager load your list, and then call your IsNew property afterward, which will eliminate the Linq-to-SQL error. But i understand that can cause performance issues if you are relying on IsNew to filter on a large set of data within.

I think your problem is really due to you wanting to use an instance of a vehicle to get the "IsNew" property. But you simply can't do that because linq-to-sql will justifiably complain when you are trying to use a property that is not mapped to a column.

So if you can't use an instance, what's the next best thing?

Well maybe i'd settle on a static expression

public partial class Motorcycle : Vehicle
{
    public static Expression<Func<Vehicle, bool>> IsNew { get { return (v) => v.Age <= 1; } }
}

public partial class Car : Vehicle
{
    public static Expression<Func<Vehicle, bool>> IsNew { get { return (v) => v.Age <= 2; } }
}

Which you can use like

var newCars = db.Cars.Where(Car.IsNew).ToList();
var newMotorcycles = db.Motorcycles.Where(Motorcycle.IsNew).ToList();

Or you could pull the logic outside your application and do it in SQL Server as a computed column, which i personally think is your best option.