C# SQL query to asp repeater.

Question

I'm working on a report, that will take all visits that happened in any given month and display some basic information. I have every part working with the exception of the FirstName and LastName of the people who attended the visit. There can be anywhere from 0 to infinite amount of visitors per visit.

There are 3 SQL tables that matter for this. dbo.CaseNotes, dbo.VisitAttendance, and dbo.Persons

They are linked from dbo.CaseNotes[CaseNoteID] on dbo.VisitAttendance[CaseNoteID] dbo.VisitAttendance[CasePersonID] on dbo.Persons[PersonID]

From dbo.Persons, I can get the FirstName and LastName from the PersonID.

The error comes from the "ThosePresent". any help would be greatly appreciated.

Here is my current code:

private void Services_BindGrid(DateTime begin, DateTime end)
    {
        using (var db = new FormsDataContext())
        {

            var caseID = Convert.ToInt32(Request.QueryString["CaseID"]);

            var _visitAttend = db.VisitAttendances.Where(v => v.CaseID == caseID);

            var query = db.CaseNotes.Where(c => c.CaseID == caseID && c.VisitDate >= begin && c.VisitDate <= end)

                         .Select(c=>new 
                {
                    VisitDate = c.VisitDate,
                    StartTime = c.StartTime,
                    EndTime = c.EndTime,
                    Duration = c.Duration,
                    TypeOfContact = db.DropDowns.SingleOrDefault(d => d.DropDownID == c.TypeOfContact).DisplayText,
                    LocationOfVisit = db.DropDowns.SingleOrDefault(d => d.DropDownID == c.LocationOfVisit).DisplayText,
                    VisitPunctuality = c.VisitPuncuality,
                    ThosePresent = db.VisitAttendances.SingleOrDefault(v => v.CaseNoteID == c.CaseNoteID).CasePersonID.ToString()
                });


            rptContacts.DataSource = query.ToList();
            rptContacts.DataBind();
        }

    }

Answer 1

I'm assuming you are using Linq2Sql due to the suffix DataContext. Note that SingleOrDefault will return null if no records are found, and will throw an exception if more than one is found, it is thus inadvisable to access CasePersonID without first checking for null.

Although you could probably do something unwieldy like:

ThosePresent = db.VisitAttendances.SingleOrDefault(v => v.CaseNoteID == c.CaseNoteID) != null
             ? db.VisitAttendances.SingleOrDefault(v => v.CaseNoteID == c.CaseNoteID).CasePersonID.ToString()
             : "No One"

the performance would be even worse than it is now. Note that at present that the calls db.DropDowns.SingleOrDefault and db.VisitAttendances.SingleOrDefault will be executed once for every anonymous object projected by your query.

Can I recommend instead that you use a combination of eager loading and the navigations across foreign keys to get Sql to do the join in the database. Do this by adding this to just after creating the context:

 var options = new DataLoadOptions();
 options.LoadWith<CaseNotes>(x => x.VisitAttendances);
 db.LoadOptions = options;

And then you can do like so:

 ThosePresent = c.VisitAttendances
    .SingleOrDefault(v => v.CaseNoteID == c.CaseNoteID)

(although again, you will need to check the outcome for null before dereferencing). The real solution then is to use an intermediate projection, materialize (after filtering), and then to do the final projection on the intermediate projection:

.Select(c => new 
{
   ...
   tmpThosePresent = c.VisitAttendances
    .SingleOrDefault(v => v.CaseNoteID == c.CaseNoteID)
   ...
}
.ToList()
.Select(c => new
{
    ...
    ThosePresent = c.tmpThosePresent != null
       ? c.tmpThosePresent.CasePersonID.ToString()
       : "No One"
    ...
});

Similarly for the drop downs, if they have navigable foreign keys from the domain objects, you can also eager load with LoadsWith, or alternatively, if they are constant data, load them up into a static Dictionary cache keyed with DropDownID.