Entity Framework - slow when using a simple query

Question

I have a simple class in entity framework as follows:

public class Merchandising
{
    public int Id { get; set; }

    public int Index { get; set; }    

    public int CategoryId { get; set; }

    public int? CardId { get; set; }  
}

In the database this has approximately 1000 rows, and a direct query takes less than a second, but when I execute this statement, it takes up to 55 seconds to run - which I find very bizarre. Can anyone throw any light on this?

var mm = a.Merchandisings.ToList();    
var m = mm.Where(f => f.CategoryId == catId).ToList();

catId is an integer value, and mm takes a fraction of a second to execute. There are approximately 1000 rows returned by mm, and 40 rows returned by m. M takes around 55 seconds to execute.

I am assuming that although the CategoryId and CardId both link to other classes (and are big data objects), data isn't loaded because there is no lazy loading.

I really can't understand why it is taking so long for m to execute, and I guess this is to do with some lack of knowledge with equity framework. Can anyone assist?

Take a look with the profiler, investigate what actual queries are sent to the DB — Alejandro
– Alejandro, Commented Aug 22, 2015 at 14:41

ocuenca · Accepted Answer · 2015-08-22 14:35:30Z

5

The problem is when you call the ToList method in your first line you will bring all the elements to memory, so try filtering first to avoid load unnecessary elements that don't meet the condition, and after that call the ToList method:

var m =  a.Merchandisings.Where(f => f.CategoryId == catId).ToList();

edited Aug 22, 2015 at 14:35

answered Aug 22, 2015 at 14:30

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3429672 Over a year ago

Thank you - although this didn't directly fix the problem - it led me to the a variable which was stored in memory and was much larger, Once I switched this out, it worked fine.

Collectives™ on Stack Overflow

Entity Framework - slow when using a simple query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related