Single query generated into > 1100 different Queries in Entity Framework

Question

I have an odd problem - I have a table called Item which links to a table called brand via a BrandID key in the Item table.

I have a EF Code First model that has the Navigation property as follows in the Items table:

public virtual Brand Brand { get; set; }

I have a list of Items that I send to a method called FilterItems which gets a predicate from another method the executes a Where statement to return a list of filtered Icons:

   public IEnumerable<Item> FilterItems(IEnumerable<Item> items)
            {
                string searchString = txtSearchString.Text.ToUpper();

                Func<Item, bool> predicate;

                switch (Field)
                {
                    case SearchField.Brand:
                    default:
                        predicate = BuildBrandPredicate(searchString);
                        break;
                    case SearchField.Model:
                        predicate = BuildModelPredicate(searchString);
                        break;
                    case SearchField.Note:
                        predicate = BuildNotePredicate(searchString);
                        break;
                    case SearchField.Reference:
                        predicate = BuildReferencePredicate(searchString);
                        break;
                    case SearchField.Text:
                        predicate = BuildTextPredicate(searchString);
                        break;
                }

                var result = items.Where(predicate);
                return result;
            }

        private Func<Item, bool> BuildBrandPredicate(string searchString)
        {
            Func<Item, bool> predicate;
            //build the predicate for brand
            switch (cboSearchActions.Text)
            {
                case "Exact":
                    predicate = (item => item.Brand.Description.ToUpper() == searchString);
                    break;
                //Other similar functions go here but I am concentrating on Exact
            }
            return predicate;
        }

There are about 32000 Items and 1000 brands in the database with each item being linked to only one brand.

The search is VERY slow and when I debug the SQL I find that it runs this sequence for every record in the brand table:

Opened connection at 29/09/2014 15:14:46 +01:00

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Description] AS [Description]
    FROM [Brand] AS [Extent1]
    WHERE [Extent1].[ID] = @EntityKeyValue1


-- EntityKeyValue1: '1' (Type = Int32, IsNullable = false)

-- Executing at 29/09/2014 15:14:46 +01:00

-- Completed in 6 ms with result: SqlCeDataReader

This is run a total of 1123 times which is ridiculous.

Surely the sql generated should be a single sql statement with an inner join?

Can anyone explain why this is occuring and whether there is anything I can do to stop this ridiculous behaviour

I am using

1. Visual Studio 2012
2. C#
3. Sql Server Compact 4.0
4. Entity Framework 6
5. .Net 4.5

Answer 1

IEnumerable<T> is linq to objects - you are telling it to perform these operations separately per-item. It can't possibly compose the queries unless you use LINQ-to-some-backend, such as LINQ-to-Entities. Fortunately, this is usually as simple as replacing IEnumerable<T> with IQueryable<T>, and Func<Foo,Bar> with Expression<Func<Foo,Bar>>:

    public IQueryable<Item> FilterItems(IQueryable<Item> items)
    {
        string searchString = txtSearchString.Text.ToUpper();

        Expression<Func<Item, bool>> predicate;

        switch (Field)
        {
            case SearchField.Brand:
            default:
                predicate = BuildBrandPredicate(searchString);
                break;
            case SearchField.Model:
                predicate = BuildModelPredicate(searchString);
                break;
            case SearchField.Note:
                predicate = BuildNotePredicate(searchString);
                break;
            case SearchField.Reference:
                predicate = BuildReferencePredicate(searchString);
                break;
            case SearchField.Text:
                predicate = BuildTextPredicate(searchString);
                break;
        }

        var result = items.Where(predicate);
        return result;
    }
    private Expression<Func<Item, bool>> BuildBrandPredicate(string searchString)
    {
        Expression<Func<Item, bool>> predicate;
        //build the predicate for brand
        switch (cboSearchActions.Text)
        {
            case "Exact":
                predicate = (item => item.Brand.Description.ToUpper() == searchString);
                break;
            //Other similar functions go here but I am concentrating on Exact
        }
        return predicate;
    }