Linq To SQL Join Question

Kevin Marois

I have this method that returns a List. Each start date can have 1-n revisions. So after I get the start dates I then loop through them all and get the revisions for each one. My question is this... is it possible to somehow do this all in one query without having to loop and get the child records? Can I somehow select the joined records into entities in the same query as the start dates?

public List GetJobStartDates(int jobId)
{
using (var dc = GetDataContext())
{
var results = (from jsd in dc.JobStartDates
where jsd.JobId == jobId
select new JobStartDateEntity
{
Id = jsd.Id,
StartDateId = jsd.StartDateId ?? 0,
JobId = jobId,
ProjectStartDateId = jsd.ProjectStartDateId,
Caption = jsd.Caption,
Description = jsd.Description,
Sequence = jsd.Sequence,
DaysOffset = jsd.DaysOffset,
StartDate = jsd.StartDate
}).ToList();

foreach (var result in results)
{
    var startDateRevisions = (from sdr in dc.JobStartDateRevisions
                                where sdr.Id == result.Id
                                select new JobStartDateRevisionEntity
                                { 
                                    Id = sdr.Id,
                                    JobId = sdr.JobId,
                                    StartDate = sdr.StartDate,
                                    Revision = sdr.Revision
                                }).OrderByDescending(x => x.Revision).ToList();

    result.StartDateRevisions = startDateRevisions;
}

return results;

}
}

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Richard Deeming

Assuming you have a navigation property, you may be able to do something like this:

var jobStartDates = dc.JobStartDates.AsNoTracking().Include(jsd => jsd.Revisions).Where(jsd => jsd.JobId == jobId).ToList();
var results = jobStartDates.Select(jsd => new JobStartDateEntity
{
Id = jsd.Id,
...
StartDateRevisions = jsd.Revisions.Select(sdr => new JobStartDateRevisionEntry
{
Id = sdr.Id,
...
}).OrderByDescending(sdr => sdr.Revision).ToList(),
});

Alternatively, you can do it with two queries, rather than N+1 queries:

var results = (from jsd in dc.JobStartDates
where jsd.JobId == jobId
select new JobStartDateEntity
{
...
}).ToList();

var jobStartDateIds = results.Select(jsd => jsd.Id).ToList();

var revisions = dc.JobStartDateRevisions
.Where(sdr => jobStartDateIds.Contains(sdr.Id))
.Select(sdr => new JobStartDateRevisionEntity { ... })
.ToList();

forech (var result in results)
{
result.StartDateRevisions = revisions
.Where(sdr => sdr.Id == result.Id)
.OrderByDescending(sdr => sdr.Revision)
.ToList();
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Nathan Minier

Without a navigation property, wouldn't a join be easier and faster?

"Never attribute to malice that which can be explained by stupidity." - Hanlon's Razor