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?
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;