CODEWITHSUNDEEP
.netlinq
danielovich
danielovich

Reputation: 9687

How to optimize this linq query?

I have this linq to sql query:

var items =
    from i in context.Items
    orderby i.itemId descending
    select new ItemWithCategories
    {
        item = i,
        categories = (
            from cats in context.categories 
            join ic in context.itemCategories 
                on cats.categoryId equals ic.categoryId
            where ic.itemId == i.itemId
            select cats).ToList()
    };

It's three tables. I need to join the categories with the items but there is a table in between (many-to-many). Is there a better to do this query ?

Upvotes: 2

Views: 143

Answers (2)

casperOne
casperOne

Reputation: 74530

The query that you have will create a list of objects which will have references to queries (the categories) which will then be executed when they are accessed (if they are at all).

This can cause a lot of "chatter" between the client and the database server, as well as the additional overhead of sending header data repeatedly across your TDS stream.

You might want to consider performing a join and flattening out the data here. It would require just one lookup, at the cost of replicating the data on the left hand side (items).

Of course, with any optimization, the key is to test, test, test.

Upvotes: 0

Steven
Steven

Reputation: 172646

This yields the same results, but is much easier to read:

var items =
    from item in context.Items
    orderby item.itemId descending
    select new ItemWithCategories
    {
        item = item,
        categories = (
            from itemCategory in item.itemCategories
            select itemCategory.category).ToList()
    };

See how I removed the join statement, but using the itemCategories property of item?

Upvotes: 1

Related Questions