DbSet.Find method ridiculously slow compared to .SingleOrDefault on ID

dbcontext dbset ef-code-first entity-framework sql-server-ce-4


I have the following code (SQL Server Compact 4.0 is the database):

Dim competitor=context.Competitors.Find(id)

When I profile this, the Find method retrieves the competitor from a database of just 60 entries in 300+ milliseconds.

If I modify the code to:

Dim competitor=context.Competitors.SingleOrDefault(function(c) c.ID=id)

The rival is then located in under 3 milliseconds.

The competitor category is

Public Class Competitor
    Implements IEquatable(Of Competitor)

    Public Sub New()
        CompetitionSubscriptions = New List(Of CompetitionSubscription)
        OpponentMeetings = New List(Of Meeting)
        GUID = GUID.NewGuid
    End Sub

    Public Sub New(name As String)
        Me.Name = name
    End Sub

    Public Property ID As Long
    Public Property GUID As Guid

    Public Property Name As String

    Public Overridable Property CompetitionSubscriptions As ICollection(Of CompetitionSubscription)
    Public Overridable Property OpponentMeetings As ICollection(Of Meeting)
End Class

The many-to-many relations were defined forCompetitionSubscriptions and OpponentMeetings by use of the fluent API.

The identity of the objectCompetitor class is a Long, which Code First converts to a datatable's Identity column with a primary key (SQL Server Compact 4.0)

What is happening here?

7/27/2012 10:42:35 AM

Accepted Answer

Find calls DetectChanges internally, SingleOrDefault (or any query, really) doesn't.DetectChanges is a costly procedure, which is the reasonFind is more time consuming, yet it might become quicker if the item is already loaded into the contextFind would just return the loaded item rather than doing a query).

If you want to useFind You may stop automatic change detection for a large number of entities, such as those in a loop, by writing it in C# instead of VB like in the following example:

    context.Configuration.AutoDetectChangesEnabled = false;
    foreach (var id in someIdCollection)
        var competitor = context.Competitors.Find(id);
        // ...
    context.Configuration.AutoDetectChangesEnabled = true;

Now, Find never callsDetectChanges Every call should be answered as quickly as possible.SingleOrDefault (and more quickly if the object is already associated with the context.)

Automatic change detection is a challenging and enigmatic topic. This four-part series provides an excellent, in-depth discussion:

(The article's introduction has links to parts 2, 3, and 4, as well as the link to part 1)


7/27/2012 12:54:52 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow