Translate 'The member' to SQL so it doesnt throw the 'has no supported translation to SQL'

Question

I have been upgrading my database recently and got this error

Basically what I have done is the following:

I had a Participants table, which have a Name, First Name, E-mail address, ... Now I have refactored this to a Persons table and a Participants table. Each participant is linked to a person via an UUID. I have removed the Name and First Name from the participants table and they are now in the Persons table.

In my Participants Partial Class:

public partial class Participant {
    public string Name {
        get {
            return this.Person.Name;
        }
    }

    public string FirstName {
        get {
            return this.Person.FirstName;
        }
    }

}

So now my whole project can still find the name, and I don't have to edit lots of code at once.

However the following query got me into trouble:

      _db.Participants.Where(q => whatever).OrderBy(q => q.Name).ThenBy(q => q.FirstName).ToList();

This throws the infamous The member 'xxx.Models.Participants.Name' has no supported translation to SQL

Is there a way to simply tell the SQL generator that Participants.Name is actually Participants.Person.Name ?

Answer 1

If I have to edit the Query anyway I might as well use an Extension as such:

public static class MyExtensions {
    public static IQueryable<Participant> InDefaultOrder(this IQueryable<Participant> source) {
        return source.OrderBy(q => q.Person.Name).ThenBy(q => q.Person.FirstName);
    } 
}

Then my query will simply be: _db.Participants.Where(q => whatever).InDefaultOrder().ToList();

If it ever changes, It'd be easy to edit and maintain.

--- edit ---

I also had to add this

public static class MyExtensions {
    public static IEnumerable<Participant> InDefaultOrder(this IEnumerable<Participant> source) {
        return source.OrderBy(q => q.Person.Name).ThenBy(q => q.Person.FirstName);
    } 
}

Answer 2

Disclaimer: if you're doing this to be able to use previously-written queries without modification, you're sorely out of luck. However, if you're doing this for encapsulation and code management, read on.

---

There is a way, but it's a little clunky.

First, you're going to have to add expressions to your Participant class (because LINQ and EF work with expressions, not already-compiled code):

public partial class Participant
{
    public static readonly Expression<Func<Participant, string>>
        NameExpression = p => p.Person.Name;

    public static readonly Expression<Func<Participant, string>>
        FirstNameExpression = p => p.Person.FirstName;

You can continue to use your properties the way they're currently written:

    [NotMapped]
    public string Name
    {
        get
        {
            return this.Person.Name;
        }
    }

Or, to make the code repeat less, you can reference the static expressions:

    [NotMapped]
    public string FirstName
    {
        get
        {
            return Participant.FirstNameExpression.Compile().Invoke(this);
            // if you do this, you might want to consider caching the delegate
            // returned by Expression.Compile()
        }
    }
}

Finally, when you're creating a LINQ query, you'll have to use the lambda syntax, but you can use the expressions you've crafted in the place of ad-hoc expressions written directly into the query:

IEnumerable<Participant> participants = _db.Participants
//  .Where(q => whatever)
    .OrderBy(Participant.NameExpression)
    .ThenBy(Participant.FirstNameExpression)
    .ToList();