LINQ: Many paths to the same end

One of the real draws to using LINQ is that it affords many solutions to a single problem.  This can be good when finding the most performant code, and this flexibility helped me around an error with another API.

I have a Entity Framework data model of a swim team with 1 Gender and 1 Family having many swimmers and 1 swimmer being a part of many seasons.  Entity framework has some pluses (what with that cool designer, a unified model for all data sources, etc…) but what it does not do well is respond to change.  In this case, GenderId and FamilyId started as scaler properties of the Swimmer Class but then I changed them into association properties on the server using foreign keys.  Updating the model via the designer worked well enough (though you do need to remove the properties of any field you drop by hand) but the problem came in the LINQ.

I wanted to query all swimmers who were in a certain season.  I wrote the query like so: 

            var query = (from ss in CurrentContext.tblSwimmerSeasons

                         .Include("tblSwimmers")

                         .Include("tblSwimmers.tblGenders")

                         .Include("tblSwimmers.tblFamily")

                         where ss.tblSeason.SeasonID == 1

                         select ss.tblSwimmer);

Notice that I am using eager loading on the lookup tables.  The problem was that tblGenders and tblFamily come back null every time for every record.  Since I have done this exact query before, my guess is that the designer somehow is screwing up the SQL that it is generating because of the changes I made.   

The first way out of the problem is to use lazy loading like this: 

            foreach (var item in query)

            {

                item.tblFamilyReference.Load();

                item.tblGenderReference.Load();

                //etc…

            }

However, the performance problems with the multiple server round-trips makes this an unrealistic solution with a data set of any size. My next though was to use Entity-SQL but I really don’t want to mix languages in my solution for the sake of consistency.  I then realized that the select statement was forcing the data load in the Select in the SQL being generated so all I had to do was be explicit in the tables I wanted:

            var query = (from ss in CurrentContext.tblSwimmerSeasons

                         where ss.tblSeason.SeasonID == currentSeason

                         select new { ss.tblSwimmer, ss.tblSwimmer.tblGender,

                         ss.tblSwimmer.tblFamily });

 

worked like a charm.

After reading up a bit, my understanding is that the new Entity Designer in 4.0 will be more capable with changes – let’s hope so.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: