CODEWITHSUNDEEP
c#linqentity-framework
7h4ro3
7h4ro3

Reputation: 123

Inefficient entity framework queries

I have a following foreach statement:

foreach (var articleId in cleanArticlesIds)
{
    var countArt = context.TrackingInformations.Where(x => x.ArticleId == articleId).Count();
    articleDictionary.Add(articleId, countArt);
}

Database looks like this

TrackingInformation(Id, ArticleId --some stuff
Article(Id, --some stuff

what I want to do is to get all the article ids count from TrackingInformations Table. For example:

ArticleId:1 Count:1
ArticleId:2 Count:8
ArticleId:3 Count:5
ArticleId:4 Count:0

so I can have a dictionary<articleId, count>

Context is the Entity Framework DbContext. The problem is that this solution works very slow (there are > 10k articles in db and they should rapidly grow)

Upvotes: 1

Views: 484

Answers (2)

Ilya Ivanov
Ilya Ivanov

Reputation: 23646

Try next query to gather grouped data and them add missing information. You can try to skip Select clause, I don't know if EF can handle ToDictionary in good manner.

If you encounter Select n + 1 problem (huge amount of database requests), you can add ToList() step between Select and ToDictionary, so that all required information will be brought into memory.

This depends all your mapping configuration, environment, so in order to get good performance, you need to play a little bit with different queries. Main approach is to aggregate as much data as possible at database level with few queries. 

var articleDictionary = 
    context.TrackingInformations.Where(trackInfo => cleanArticlesIds.Contains(trackInfo.ArticleId))
                                .GroupBy(trackInfo => trackInfo.ArticleId)
                                .Select(grp => new{grp.Key, Count = grp.Count()})
                                .ToDictionary(info => "ArticleId:" + info.Key, 
                                              info => info.Count);

foreach (var missingArticleId in cleanArticlesIds)
{
    if(!articleDictionary.ContainsKey(missingArticleId))
        articleDictionary.add(missingArticleId, 0);
}

Upvotes: 4

Robert McKee
Robert McKee

Reputation: 21492

If TrackingInformation is a navigatable property of Article, then you can do this:

var result=context.Article.Select(a=>new {a.id,Count=a.TrackingInformation.Count()});

Putting it into a dictionary is simple as well:

var result=context.Article
  .Select(a=>new {a.id,Count=a.TrackingInformation.Count()})
  .ToDictionary(a=>a.id,a=>a.Count);

If TrackingInforation isn't a navigatable property, then you can do:

var result=context.Article.GroupJoin(
          context.TrackingInformation, 
          foo => foo.id,
          bar => bar.id,
          (x,y) => new { id = x.id, Count = y.Count() })
       .ToDictionary(a=>a.id,a=>a.Count);

Upvotes: 2

Related Questions