I'm using LLBLGen Pro 2.6 with adapter and .NET 3.5/c# connecting to a SQL Server 2008 dB.
I have a charge table/entity that has a one to many relationship to a rate table/entity as multiple rates can exist for each for charge. For an individual user selecting a charge, only one of the rates is valid based on their location and the date. So when fetching a charge I am also querying the rate table.
What I'd like to know is, how can I populate my charge entity object and its related rate entity object from my single query that performs a join and selects the top record?
I'm familiar with prefetchs but as I am already querying the rate table this seems like an unnecessary overhead (plus a simple prefetch will return all the related rates).
public ChargeEntity Fetch(Guid chargeId, DateTime validDate)
{
ChargeEntity charge = null;
using (DataAccessAdapter adapter = new DataAccessAdapter(m_ConnectionString))
{
LinqMetaData metaData = new LinqMetaData(adapter);
var charges = (from ac in metaData.Charge
join rt in metaData.Rate on ac.ChargeId equals rt.ChargeId
orderby rt.AreaType descending
where ac.ChargeId == chargeId
&& ((rt.AreaId == m_CompanyId && rt.AreaType == (byte)AreaType.Company)
|| (rt.AreaId == m_StoreId && rt.AreaType == (byte)AreaType.Store))
&& rt.ValidFrom <= validDate
&& rt.ValidTo >= validDate
select ac)
.TakePage(1, 1) // first page, one record
.WithPath(
chargePath => chargePath
.Prefetch(ac => ac.Rate));
charge = charges.ToList()[0];
}
return charge;
}