Efficient Data Loading with Linq using DataLoadOptions For the last five months I've been working on a hobby project called Mangosteen Nation which is a website for XanGo distributors. I really wanted to use this project to look at some of the new things in ASP.NET and C# such as LINQ and it's been a great experience.

So here is a tip for efficiently grabbing data using LINQ in C# 3.0.

The way LINQ works from what I can tell is that lets say you write a query to get a user object from the database but then you want to access the comments for that user.

MangoData db = new MangoData();
Mangosteen.aspnet_Membership user = db.aspnet_Memberships.First(p=>p.UserId == UserId);
List commentsFromUser = user.Comments.ToList();

This will actually issue two queries to the database although it maintains the same connection. The reason is because it will only return the user object but none of the associated content in other tables. This is good for efficency as we don't need to see the other stuff in the other tables associated to the user but when we need to access that information, it will automatically make another query to the database. If you're not careful, you could end up with a lot of queries to the database that you're not aware of.

What you really want is for just one query to be executed so what we want is that when it returns the user object, it's filled with the comments for the user as well. But there other associations and those shouldn't be returned. So how do we handle this? We use the DataLoadOptions.

If we modify our code with the new System.Data.Linq.DataLoadOptions settings, we'll get the following

MangoData db = new MangoData();

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith(p=>p.Comments);
db.LoadOptions = dlo;

Mangosteen.aspnet_Membership user = db.aspnet_Memberships.First(p=>p.UserId == UserId);
List commentsFromUser = user.Comments.ToList();

The data load options are declaring that whenever an aspnet_Membership object is loaded that the comments should also be loaded as well. Then you can attach that to the database object of your choosing to have those settings applied.

  ¶ 1:27 PM