EF 6 Find Generates Derived From Statement-CodePudding

I'm confused on why EF 6's Find method generates a derived query. I ran the following two statements in LinqPad:

using (var ctx = new ApplicationDbContext())
            {
                var z = ctx.TicketTracker.Find(11277);
                var x = ctx.TicketTracker.Where(w => w.TicketTrackerId == 11277).FirstOrDefault();
                
            }

Here's the output:

SELECT 
[Limit1].[TicketTrackerId] AS [TicketTrackerId], 
[Limit1].[BusinessId] AS [BusinessId], 
[Limit1].[DeviceId] AS [DeviceId], 
[Limit1].[ETicketStorageId] AS [ETicketStorageId], 
[Limit1].[TicketJSON] AS [TicketJSON], 
[Limit1].[DriverUpdateId] AS [DriverUpdateId], 
[Limit1].[OrderNumber] AS [OrderNumber], 
[Limit1].[OrderKey] AS [OrderKey], 
[Limit1].[TicketNumber] AS [TicketNumber], 
[Limit1].[CustomerId] AS [CustomerId], 
[Limit1].[VehicleId] AS [VehicleId], 
[Limit1].[Plant] AS [Plant], 
[Limit1].[Notes] AS [Notes], 
[Limit1].[PostedToKeystone] AS [PostedToKeystone], 
[Limit1].[EmailSuccess] AS [EmailSuccess], 
[Limit1].[EmailRecipients] AS [EmailRecipients], 
[Limit1].[FormType] AS [FormType], 
[Limit1].[Usage] AS [Usage], 
[Limit1].[JobId] AS [JobId], 
[Limit1].[JobLot] AS [JobLot], 
[Limit1].[CustomerPo] AS [CustomerPo], 
[Limit1].[CreateDate] AS [CreateDate], 
[Limit1].[PostedToKeystoneDate] AS [PostedToKeystoneDate], 
[Limit1].[Devices_DeviceId] AS [Devices_DeviceId]
FROM ( SELECT TOP (2) 
    [Extent1].[TicketTrackerId] AS [TicketTrackerId], 
    [Extent1].[BusinessId] AS [BusinessId], 
    [Extent1].[DeviceId] AS [DeviceId], 
    [Extent1].[ETicketStorageId] AS [ETicketStorageId], 
    [Extent1].[TicketJSON] AS [TicketJSON], 
    [Extent1].[DriverUpdateId] AS [DriverUpdateId], 
    [Extent1].[OrderNumber] AS [OrderNumber], 
    [Extent1].[OrderKey] AS [OrderKey], 
    [Extent1].[TicketNumber] AS [TicketNumber], 
    [Extent1].[CustomerId] AS [CustomerId], 
    [Extent1].[VehicleId] AS [VehicleId], 
    [Extent1].[Plant] AS [Plant], 
    [Extent1].[Notes] AS [Notes], 
    [Extent1].[PostedToKeystone] AS [PostedToKeystone], 
    [Extent1].[EmailSuccess] AS [EmailSuccess], 
    [Extent1].[EmailRecipients] AS [EmailRecipients], 
    [Extent1].[FormType] AS [FormType], 
    [Extent1].[Usage] AS [Usage], 
    [Extent1].[JobId] AS [JobId], 
    [Extent1].[JobLot] AS [JobLot], 
    [Extent1].[CustomerPo] AS [CustomerPo], 
    [Extent1].[CreateDate] AS [CreateDate], 
    [Extent1].[PostedToKeystoneDate] AS [PostedToKeystoneDate], 
    [Extent1].[Devices_DeviceId] AS [Devices_DeviceId]
    FROM [ETicket].[TicketTracker] AS [Extent1]
    WHERE [Extent1].[TicketTrackerId] = @p0
)  AS [Limit1]
GO

SELECT TOP (1) 
    [Extent1].[TicketTrackerId] AS [TicketTrackerId], 
    [Extent1].[BusinessId] AS [BusinessId], 
    [Extent1].[DeviceId] AS [DeviceId], 
    [Extent1].[ETicketStorageId] AS [ETicketStorageId], 
    [Extent1].[TicketJSON] AS [TicketJSON], 
    [Extent1].[DriverUpdateId] AS [DriverUpdateId], 
    [Extent1].[OrderNumber] AS [OrderNumber], 
    [Extent1].[OrderKey] AS [OrderKey], 
    [Extent1].[TicketNumber] AS [TicketNumber], 
    [Extent1].[CustomerId] AS [CustomerId], 
    [Extent1].[VehicleId] AS [VehicleId], 
    [Extent1].[Plant] AS [Plant], 
    [Extent1].[Notes] AS [Notes], 
    [Extent1].[PostedToKeystone] AS [PostedToKeystone], 
    [Extent1].[EmailSuccess] AS [EmailSuccess], 
    [Extent1].[EmailRecipients] AS [EmailRecipients], 
    [Extent1].[FormType] AS [FormType], 
    [Extent1].[Usage] AS [Usage], 
    [Extent1].[JobId] AS [JobId], 
    [Extent1].[JobLot] AS [JobLot], 
    [Extent1].[CustomerPo] AS [CustomerPo], 
    [Extent1].[CreateDate] AS [CreateDate], 
    [Extent1].[PostedToKeystoneDate] AS [PostedToKeystoneDate], 
    [Extent1].[Devices_DeviceId] AS [Devices_DeviceId]
    FROM [ETicket].[TicketTracker] AS [Extent1]
    WHERE 11277 = [Extent1].[TicketTrackerId]

I would love for Find to simply generate a select statement but before I start switching code, is there a reason EF adds this complex query with a simple method? I did read that Find uses internal caching and Where doesn't, but other than that what am I missing?

CodePudding user response：

When you see random queries running slow for seemingly no reason, or resource consumption is really high this is often a sign of an indexing issue.

-- Putting this answer here as this was in fact the issue with the OPs query.

CodePudding user response：

Those queries are normal and shouldn't cause any performance problems. For some more complex queries you may find that it's worth writing a custom Raw SQL Query, but not in this case.

this query is always either taking up too many resources or is the slowest.

Turn on the Query Store to find out what resources it's taking and why it's slow.