1

Firstly, sorry if the title is misleading, feel free to alter if need be.

I am using LINQ to SQL in a C# asp.net MVC 3 web application, and one of the features is to query a collection of "Users". There is filtering, sorting and paging applied before making use of the data. This part is all good, all of this is organised so that only one SQL query is executed at this stage (hats of to LINQ for this).

However, what I have is that each "User" has a number of associated tables (namely Addresses, Emails, Numbers) where a user can have one or many of each. Now for the purpose of my function I need to collect the first entry in each of the associated tables (namely the "primary" record). The way I have this coded is that for each record found in the "filtering" part, there is a separate query to pull each primary detail record.

I am sure at this point you all know where I am going with this: Can I reduce the number of queries without compromising the structure of my code?

So, the next part, what does my code look like...

(keep in mind this is summed up for this question and may or may not compile/run)

IQueryable<Data.User> users = GetUsers();

users = ApplyFilterSortingPaging(users);//nothing actually executed yet, still building the IQueryable

List<Models.User> results = users.Select(x => x.ToModel());

Now you will want to see the ToModel extension method...

public static Models.User ToModel(this Data.User user)
{
    Models.User result = new Models.User()
    {
       Name = user.Name,
       PrimaryAddress = user.Addresses.First(),
       PrimaryEmail = user.Emails.First(),
       PrimaryNumber = user.Numbers.First()
    };
}

As you might expect, for each contact record I now have an additional 3 queries being run. Overall the performance is not an issue (at least at the moment) - its all pretty fast. Is there something I can do to reduce this down to just one query per record? or even just to one main "get all filtered records + extra data" query?

Maybe this is the cost of more readable/reusable code?

AakashM
  • 62,551
  • 17
  • 151
  • 186
musefan
  • 47,875
  • 21
  • 135
  • 185
  • 2
    This problem is known as the 'n+1' problem, to help your search. See also http://www.west-wind.com/weblog/posts/2009/Oct/12/LINQ-to-SQL-Lazy-Loading-and-Prefetching and http://stackoverflow.com/questions/1298972/manually-select-related-table-data-select-n-1-problem-linq-to-sql (pretty similar) – AakashM Apr 20 '12 at 10:19
  • @AakashM: Thanks for the pointer. after some looking around (and testing) it seems there is no way to `LoadWith` for multiple relationships, so my best efforts will only reduce one of the "per-entry" queries :S – musefan Apr 20 '12 at 11:13

1 Answers1

1

You could create a view of the data you need in the database, and then query that using LINQ.

paul
  • 21,653
  • 1
  • 53
  • 54