Creating a queryable property for a linq to sql entity

Question

I have a simple table structure in SQL Server:

One table storing a list of Bikes (Bikes) and a second table storing a historical list of the bike's owners (Owners).

Using Linq 2 SQL I generated a model that gives me a 'Bike' entity with a 1 to many relationship to 'Owner' called 'HistoricalOwners'.

So far so good...

Now I have many queries where I am interested in the current owner. The current owner being the most recent entry in the owners table.

repo.Bikes().Where(b => b.HistoricalOwners.OrderByDescending(o => o.Date).First().Name == "Joe")

This works, but it is a bit ugly, and doesn't really say what I am asking. It would be nice to create a 'CurrentOwner' property on my Bike entity and query against that.

public Owner CurrentOwner
{
    get
    {
        return HistoricalOwners.OrderByDescending(o => o.Date).First();
    }
}    

Then I could query like this:

repo.Bikes().Where(b => b.CurrentOwner.Name == "Joe")

This compiles, but of cources at runtime it will fail with:

The member 'CurrentOwner' has no supported translation to SQL.

Which makes total sense.

Does any one know how I might be able to create a CurrentOwner in a way that would let this query work?

I did some research with getting CurrentOwner to return an Expression tree instead of an instance, but I never got anywhere I was really happy with.

Then I wondered if I could add an extra relationship to my linq 2 sql repository for CurrentOwner and constrain it to be one to one. But I don't seem to be able to do this with linq 2 sql. I think I can do it with Linq 2 entities, but that isn't an option at this time.

Anyone got any ideas how I might make this work?

Thanks in advance,

rmk

Answer 1

Have you tried:

public Owner CurrentOwner
{
    get
    {
        return HistoricalOwners.OrderByDescending(o => o.Date).FirstOrDefault();
    }
}  

to see if that translates to SQL?

Answer 2

I don't think you can really do this the way you want to be able to.

My suggestion:

Create a new database column, CurrentOwnerID, on the Bike table, which is a FK to OwnerID on the Owner table. Map this as an association in your model, then you can query CurrentOwner the way you want to.

The drawback is that you have to manage the value of CurrentOwner as per any other mapped property - you can't derive it using another query as in your original example, so it may not be appropriate for your application.

However that's the way I've solved this sort of problem in the past, and it gives more flexibility to your model - you can choose who the current owner is, rather than rely on a derived result, and it's simple to understand.