How to read data faster in Entity Framework from SQL Server Compact C#

Question

I used SQL Server Compact edition v4.

When I get new from entities, it takes 1 second, but when I count from a table with only one record, it takes 4 seconds.

I used Entity Framework and SQL Server Compact Edition.

What is the reason for this slow speed on computers with a low configuration?

var db = new Entities();    // 1 second
db.User.count();            // 4 seconds

Answer 1

Opening a connection can be slow, so a possible solution is to open the connection once at app startup and keep that connection open for the lifetime of the app.

Answer 2

The first query you execute against the first instance of a DbContext triggers the static setup for the DbContext. This is where all of the mapping is resolved so that the DbContext is ready to work with the DbSets and associated entities. The more entity definitions the context has to deal with, the longer this can take.

For mobile applications I would figure EF would represent a potentially prohibitive cost for data access, however from general EF experience to avoid this hit when the user fires off their first real query you can implement a "warm up" at the application launch:

using (var context = new Entities())
{
   bool result = context.Users.Any();
}

This ensures that the static context definition is set up and all further instances of the context will be ready to execute immediately. Note that this cost is only on the first instance of the DbContext, you don't have this cost with additional DbContext instances. Those instances should be short-lived and bounded by a using block to ensure they are disposed. For a mobile app you may want to consider one longer-lived DbContext but I would be wary of fetching tracked entities (instead, use AsNoTracking() when loading entities you aren't editing). Tracked entities could eat up limited resources and potentially cause performance issues down the road.