LINQ to SQL caching problem

Question

Hi, this should be pretty simple.

I have LINQ to SQL (C# 3.5 / SQL Server) working well, with a simple relationship in place between two tables Conferences and ConferenceAttendees.

I have already created and committed a Conference, then I add 3 ConferenceAttendees using the following code (repeated 3 times):

ConferenceAttendee NewAttendee = new ConferenceAttendee();
NewAttendee.ConferenceId = ConferenceID;
NewAttendee.Name = ContactName;
NewAttendee.Email = ContactEmail;
db.ConferenceAttendees.InsertOnSubmit(NewAttendee);
db.SubmitChanges();

Works great and I see the 3 new attendees appear in the database.

UPDATE: Then, using a brand new datacontext, I try the following:

string Names = String.Empty;
Conference RequestedConference = db.Conferences.FirstOrDefault(x => x.ConferenceId == ConferenceID);
   foreach (ConferenceAttendee Attendee in RequestedConference.ConferenceAttendees)
      Names += Attendee.Name + ", ";

But it doesn't have any related Attendees attached to it! (They definitely exist in the database and have been committed). But RequestedConference.ConferenceAttendees always has a count of 0 so the loop is never entered..

foreach (ConferenceAttendee Attendee in this.ConferenceAttendees)
{ Names += Attendee.Name; }

I'm doing this inside a Partial Class Conference, with a method I've made called PrintAllAttendeeNames().

What am I doing wrong and why doesn't this fresh datacontext easily see these related objects via LINQ when they ARE in the database committed already?

(Note: I've already tried calling

db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues);

to no avail..)

Many thanks

CONFESSION UPDATE

I am an absolute moron. I had the dataContext setup as static. I never normally do that, that's why I never normally have problems with lazy loading.

I had this:

public static MyDataContext db = new MyDataContext(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString);

Of course, changing it to this solved the problem!!!

public MyDataContext db = new MyDataContext(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString);

Answer 1

A couple things. First, you should only submit your changes when you have made all the changes. You don't need to call SubmitChanges every time you create a new attendee. Create all three first, InsertOnSubmit each one, then make a single call to SubmitChanges. LINQ to SQL will generate more efficient, chunkier calls that way.

Second, I'm a little confused about the attendee loading issue. They should lazy load on the first access of the Attendee property. Each individual Conference would incur a separate query when its property is first accessed, so this isn't the most efficient way to handle it. You can tell L2S to pre-load all attendees for each Conference object that you load. You would do that with the DataLoadOptions class:

using (var context = new SomeContext(...))
{
    var options = new DataLoadOptions();
    options.LoadWith<Conference>(c => c.ConferenceAttendees);

    context.LoadOptions = options;

    var conferencesWithAttendees = from c in context.Conferences
                                   where c.Year = DateTime.Now.Year
                                   select c;

    foreach (var conferenceWithAttendee in conferencesWithAttendees)
    {
        conferenceWithAttendee.PrintAllAttendeeNames();
    }
}