Linq to Entities 4.0 - Optimized Query and multiple calls to database in a single query

Question

Hi,

Can anyone tell me if the following query calls the database multiple times or just once?

var bizItems = new
{
    items = (
        from bl in db.rc_BusinessLocations
        orderby bl.rc_BusinessProfiles.BusinessName
        select new BusinessLocationItem
        {
            BusinessLocation = bl,
            BusinessProfile = bl.rc_BusinessProfiles,
            Products = bl.rc_InventoryProducts_Business
        })
        .Skip(pageSkip).Take(pageSize),

    Count = db.rc_BusinessLocations.Count()
};

I really need to get the Count() out of the query and I couldn't find another way to do it so if you have some better optimized code, feel free to share it!

Thanks in advance!

Gwar

Answer 1

This will definitely result in two calls.

Answer 2

You should check out Scott Guthrie's post on using the LINQ to SQL Debug Visualizer. This will allow you to see exactly the SQL that will be generated from your LINQ statement.

Answer 3

It totally depends on what you are doing with the bizItems variable, because after you've ran just this code, only a COUNT(*) query will have ran. This is because the item contains an IQueryable which is a description of a query (an intent to run), not the result of the operation. The query will only run when you start iterating this query, by using foreach or an operator such as .Count(). Besides this, the BusinessProfile and Products properties will probably also contain IQueryables.

So, let's take a look at what you might do with this code:

foreach (var item in bizItems.items)
{
    Console.WriteLine(item.BusinessLocation.City);

    foreach (var profile in item.BusinessProfile)
    {
        Console.WriteLine(profile.Name);
    }

    foreach (var product in item.Products)
    {
        Console.WriteLine(product.Price);
    }

    Console.WriteLine(item.Count);
    Console.WriteLine();
}

So, if you ask me again, looking at this code, how many queries will be sent to the database, my answer is: 2 + 2 * the number of items in bizItems.items. So the number of queries will be between 2 and (2 + 2 * pageSize).