Will manual Linq-To-Sql mapping with Expressions work?

Question

I have this problem:

The Vehicle type derives from the EntityObject type which has the property "ID".

I think i get why L2S can't translate this into SQL- it does not know that the WHERE clause should include WHERE VehicleId == value. VehicleId btw is the PK on the table, whereas the property in the object model, as above, is "ID".

Can I even win on this with an Expression tree? Because it seems easy enough to create an Expression to pass to the SingleOrDefault method but will L2S still fail to translate it?

I'm trying to be DDD friendly so I don't want to decorate my domain model objects with ColumnAttributes etc. I am happy however to customize my L2S dbml file and add Expression helpers/whatever in my "data layer" in the hope of keeping this ORM-business far from my domain model.

Update:

I'm not using the object initialization syntax in my select statement. Like this:

private IQueryable<Vehicle> Vehicles() 
{
    return from vehicle in _dc
        select new Vehicle() { ID = vehicle.VehicleId };
}

I'm actually using a constructor and from what I've read this will cause the above problem. This is what I'm doing:

private IQueryable<Vehicle> Vehicles() 
{
    return from vehicle in _dc
        select new Vehicle(vehicle.VehicleId);
}

I understand that L2S can't translate the expression tree from the screen grab above because it does not know the mappings which it would usually infer from the object initialization syntax. How can I get around this? Do I need to build a Expression with the attribute bindings?

Answer 1

I have decided that this is not possible from further experience.

L2S simply can not create the correct WHERE clause when a parameterized ctor is used in the mapping projection. It's the initializer syntax in conventional L2S mapping projections which gives L2S the context it needs.

Short answer - use NHibernate.

Answer 2

Short answer: Don't.

I once tried to apply the IQueryable<.IEntity> to Linq2Sql. I got burned bad.

As you said. L2S (and EF too in this regard) doesn't know that ID is mapped to the column VehicleId. You could get around this by refactoring your Vehicle.ID to Vehicle.VehicleID. (Yes, they work if they are the same name). However I still don't recommend it.

Use L2S with the object it provided. Masking an extra layer over it while working with IQueryable ... is bad IMO (from my experience).

Otherway is to do .ToList() after you have done the select statement. This loads all the vehicles into your memory. Then you do the .Where statment against Linq 2 Object collections. Ofcourse this won't be as effecient as L2S handles all of the query and causes larger memory usage.

Long story short. Don't use Sql IQueryable with any object other than the ones it was originally designed for. It just doesn't work (well).