Databases
StackOverflow2013
Postsdbo
POEF 4.3 Multiple Enumeration Issue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEF 4.3 Multiple Enumeration Issue
    primarykey
    data
    text
    <p>We are using EF 4.3 for our data layer and have a generic repository pattern in place. Backend is SQL 2008 R2 and the project is .NET 4.0/MVC 3 but I don't think that this factors into the question.</p> <p>Basically, we have a one to many relationship in our database of two objects. One is for 'Traps' and the second is for 'Trap Activities'. Meaning, once one of these 'Traps' is deployed, anything that happens to that trap is kept in the Trap Activity table. Should be a fairly straightforward way of doing this. </p> <p>The relationship is defined with a FK in the 'Trap Activity' table to the PK of the 'Traps' table. Both tables have PKs defined. </p> <p>In our service layer, I need to query out a list of 'Traps' with the date that these traps were deployed. This is accomplished by the following code snippet:</p> <pre><code>var traps = this.trapRepository.Find(x =&gt; x.DeploymentYear == 2012).Select(x =&gt; new TrapHomeViewModel { County = x.County.Name, DeploymentDate = x.TrapActivities.First(y =&gt; y.ActivityType == 1).ActivityDate, State = x.County.CountyState.Abbreviation, Latitude = x.Latitude, Longitude = x.Longitude, TrapId = x.TrapID, TrapNumber = x.SerialNumber, Centroid = x.TrapCentroid }).ToList(); </code></pre> <p>The issue is around the DeploymentDate property. As written, this takes 25s to return a list of around 3000 items. Updating the Trap table to have the deployment date to be stored there and populating with this line:</p> <pre><code>DeploymentDate = x.DeploymentDate.Value.Date </code></pre> <p>Results in a less than 1s response time. Now I think I know what is going on here (multiple enumerations of the data set) but what I thought would happen would be a query similar to the following:</p> <pre><code>SELECT Counties.Name, TrapActivities.ActivityDate, States.Abbreviation, Traps.Latitude, Traps.Longitude, Traps.TrapID, Traps.SerialNumber, Traps.TrapCentroid FROM TrapActivities INNER JOIN Traps ON TrapActivities.TrapID = Traps.TrapID INNER JOIN Counties ON Traps.CountyID = Counties.CountyID INNER JOIN States ON Counties.State = States.FIPS_Code WHERE (TrapActivities.ActivityType = 1) </code></pre> <p>...but that does not seem to be the case. With all the background information above, where have I strayed in populating this view model? I don't think I have ran into this issue before but this is also a much larger dataset than some of our other projects. Any guidance on this would be much helpful. If I need to provide any other information, please let me know.</p> <p><strong>EDIT</strong></p> <p>As requested, the GenericRepository Find method and constructors:</p> <pre><code> public class GenericRepository&lt;T&gt; : IGenericRepository&lt;T&gt; where T : class { private readonly IObjectSet&lt;T&gt; objectSet; private ObjectContext context; public GenericRepository() : this(new APHISEntities()) { } public GenericRepository(ObjectContext context) { this.context = context; this.objectSet = this.context.CreateObjectSet&lt;T&gt;(); } public IEnumerable&lt;T&gt; Find(Func&lt;T, bool&gt; predicate) { return this.objectSet.Where(predicate); } </code></pre> <p><strong>EDIT 2</strong></p> <p>This is an example of the SQL being generated by the above code:</p> <pre><code>exec sp_executesql N'SELECT [Extent1].[TrapActivityID] AS [TrapActivityID], [Extent1].[TrapID] AS [TrapID], [Extent1].[ActivityType] AS [ActivityType], [Extent1].[Notes] AS [Notes], [Extent1].[AgentID] AS [AgentID], [Extent1].[ActivityDate] AS [ActivityDate], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[EditedOn] AS [EditedOn], [Extent1].[Deleted] AS [Deleted], [Extent1].[VisualInspectionID] AS [VisualInspectionID] FROM [dbo].[TrapActivities] AS [Extent1] WHERE [Extent1].[TrapID] = @EntityKeyValue1',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='FEBC7ED4-E726-4F5E-B2BA-FFD53AB7DF34' </code></pre> <p>It looks to me that it is taking a list of Trap Ids and then running a query for each one, resulting in thousands of SQL statements being generated. It also appears to be running individual queries for the County information as well.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTommy
    1. USTommy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEF 4.3 Multiple Enumeration Issue
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POEF 4.3 Multiple Enumeration Issue
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEF 4.3 Multiple Enumeration Issue
      1. This table or related slice is empty.
      1. VTUpMod
    1. COBest bet in this situation and for future situations is to run a profiler such as Anjlab sql profiler on your database. It'll show you the exact SQL that is executing and how long each call takes. You'll then be able to see if it's doing anything super inefficiently. You probably just need to add some indexes to your database to speed it up. Once you find the query that's taking 25 seconds, SSMS has an option to view the execution path, and you should see where the problems lie.
      singulars
      1. POEF 4.3 Multiple Enumeration Issue
      1. USNibblyPig
    2. COYou can see exactly what query is being generated by grabbing the network traffic (Wire Shark will do this) and looking at it, then see if you have the indexes to optimize it. You might be surprised by the query it generates - I was with some of mine.
      singulars
      1. POEF 4.3 Multiple Enumeration Issue
      1. USitsmatt
    3. COWhat does your repository's `Find` method look like?
      singulars
      1. POEF 4.3 Multiple Enumeration Issue
      1. USBrian Cauthon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload