0

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?

bugfixr
  • 7,997
  • 18
  • 91
  • 144

1 Answers1

2

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;
Charlino
  • 15,802
  • 3
  • 58
  • 74
  • This could work, and very nicely, if it weren't for the fact that it causes an additional database hit. The object will be used in a gridview scenario... if the grid has say 50 records, each of those records will be querying the database and will cause some big delays. – bugfixr May 12 '09 at 14:45
  • Depending on how you want the database to perform you can either 'eager load' the related entities (see my edited answer for example) or you could 'lazy load' the entities using a combination of .Load() & .IsLoaded() ... only you know which the best way would be. – Charlino May 12 '09 at 22:46