One query generated in> 1100 different queries in Entity Framework
I have a weird problem. I have a table called Item that refers to a table called brand via the BrandID key in the Item table.
I have an EF Code First model that has a Navigation property as listed in the Items table:
public virtual Brand Brand { get; set; }
I have a list of items that I am sending to a FilterItems method that receives a predicate from another method, executes a Where clause 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;
}
The database contains about 32,000 titles and 1,000 brands, each of which is associated with just one brand.
The search is very slow and when I debug the SQL I find that it does 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 done a total of 1123 times, which is ridiculous.
Surely the generated sql must be a single sql statement with an inner join?
Can anyone please explain why this is happening and if there is anything I can do to stop this ridiculous behavior.
I use
- Visual Studio 2012
- FROM#
- Sql Server Compact 4.0
- Entity Framework 6
- .Net 4.5
source to share
IEnumerable<T>
linq for objects - you say to do these operations separately for each item. It cannot compose queries unless you are using LINQ-to-some-backend like LINQ-to-Entities. Fortunately, this is usually as easy 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;
}
source to share