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.

Myrlmyrle

What is _isNew? Can't is be an expression that can be translated? – Rope

_isNew is an Expression that can be translated to SQL (e.g. for cars Expression<Func<Vehicle, bool>> _isNew = (v) => v.Age <2; for motorcycles Expression<Func<Vehicle, bool>> _isNew = (v) => v.Age <1; (P.S.: Age is a DB column). My main issue is how can I choose the correct _isNew to be invoked on Vehicle depending on the inherited class. Calling the function IsNew will cause a SQL translation error and I can't decide on which _IsNew to invoke without checking the type and casting every single time. – Myrlmyrle

I guess this won't work at all because to call your override of IsNew a concrete instance of either Car or Motorcycle will be necessary. As a result EF would have to query the items first to create those instances. – Liking

What is exact error message? – Subcortex

I'd declare method IsNew as: public abstract bool IsNew(int age);. Then implementation: public override bool IsNew(int maxAge){return this.Age<maxAge;}. Finally, usage: Car c = new Car(){Age = 9}; Console.WriteLine("Car.Age={0}. Is new={1}", c.Age, c.IsNew(5)); – Subcortex

@Liking True, but all of my expressions receive a concrete instance as a parameter. Is there any other way to handle linq-to-sql translation of expressions/methods? – Myrlmyrle

All methods I try either cause a "has no supported translation to sql" or runs it as linq-to-entities causing the fetch of an entire table and an enormous speed drop. – Myrlmyrle

I'm not quite sure about it but have you tried to use functions inside the setter getter of IsNew, mark them has virtual and ovveride the implementation in the child classes? – Enplane

Sorry for the poor grammar, it was "as" and "override" obviously – Enplane

@Myrlmyrle it would be nice if your question included a statement that cause the error. – Uranian

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.

Thy

Thank you for the answer. My main issue with your proposal is that I don't always know the type, so I will end up adding typeof in many places and casting before I run the IsNew expression. I reached the conclusion that I simply can't do it with LINQ alone but managed to find a somehow dirty workaround: I used LINQKit ExpandableQuery and extended the entire logic to choose the correct Expression. This way, the class Vehicle is never sent to LINQ and only Car and Motorcycle are treated there. Not a very clean solution though – Myrlmyrle

I strongly agree however with you about the computed columns, stored procedures, views to handle all in SQL Server. But I am working on an existing big project with all the Expressions already defined in LINQ. It will take months to re-write those and therefore preferred to find a LINQ method. – Myrlmyrle

I cant help but feel there was some way reflection could have helped you in some way, it's a pretty confusing aspect of C#, if you don't already have experience with it, it might not be time efficient to use. Though I personally have found that tough structural problems such as this can often be solved with reflection. – Thy

Answer 2

The abstract class allows you to force inherited class to implement your IsNew property. It is just a base class. You have to use an implementation of Car or Motorcycle and for that, you should cast your Vehicule object as Car or as Motorcycle so the right IsNew property would be called.

var vehicule = new Car();

Eritrea

The question is about linq-to-sql translation of two classes with mapping to database and a parent abstract class. It is not just a normal inheritance/abstract class. – Myrlmyrle