Using partial-classes in Entity Framework with custom properties

Question

How should I handle a custom property in a situation where I use partial classes with the EF-generated classes?

Here's the setup:

TimeSheet Table - this stores an employee's hours
- TimeSheetID (auto, int, PK)
- EntryDate (DateTime)
- Hours (int)
- EmployeeID (int)

EmployeeHourlyRate table - this stores an employee's current hourly rate. Historical rates are stored here as well.
- RateID (int, PK)
- EffectiveDate (int, PK)
- Rate (double)

This is a one to many relationship from TimeSheet to EmployeeHourlyRate. In order to find an Employee's rate, I would select the max effectiveDate less than the timeSheet's EntryDate.

In order to facilitate things, I've made a partial class called TimeSheet and added a new property called "Rate" to that class. What I would like to do is populate that myself from the same query that populates my collection of TimeSheets. I just know of no easy and clean way to handle this.

For example, I could do it this way:

var list = from ts in Context.TimeSheets
          ....
          select new TimeSheet() {
                 TimeSheetID = ts.TimeSheetID,
                 EntryDate = ts.EntryDate,
                 Hours = ts.Hours,
                 EmployeeID = ts.EmployeeID,
                 Rate = SomeRate   //real code has been omitted 
          };

This in theory should work, but for some reason the EF complains at run-time that I'm re-using an entity generated class (no idea why -- it works fine if I create my own custom class). However, even if it did work, I've still got to maintain a list of fields and keep on mapping from my EF to a single class -- i.e, maintenance becomes a problem when/if I add new fields to the TimeSheet table. It is also silly to have to re-type all that info.

So my question is, how do people generally handle this scenario? Is there a way to do something in the datamodel that would be able to effectively know my join rule (about selecting the correct effective date based on my EntryDate) and handle this?

Answer 1

I would like to see your full LINQ query (including the 'SomeRate' code) to see exactly what you are trying to achieve, but maybe something like this could work:

WARNING: Air code.

public partial class TimeSheet
{
    public double Rate
    {
        get //Calculate your rate here... e.g.
        {
            if ((this.Employee == null) || (this.Employee.EmployeeHourlyRates.Count == 0))
                //throw an exception

            EmployeeHourlyRate maxRate;
            foreach (EmployeeHourlyRate rate in this.Employee.EmployeeHourlyRates)
            {
                if ((rate.EffectiveDate <= this.EntryDate)
                    && ((maxRate == null) || (maxRate.EffectiveDate < rate.EffectiveDate)))
                {
                    maxRate = rate;
                }
            }

            if (maxRate == null)
                //throw exception
            else
                return maxRate.Rate;
        }
    }
}

EDIT: Adding example of eager loading to avoid database round trips.

var list = from ts in Context.TimeSheets.Include("Employee.EmployeeHourlyRate")
           where blah blah
           select ts;