How to create shorter queries with EF?

Question

This block of code gathers the Job Order information from the database, couple tables joining together, and returns back an object. Well everything works as a charm but too slow?

This is the code that does the job;

public IQueryable<object> JsonGetJobOrdersBy(int status, int companyID)
{

    IQueryable<object> result = this.db.JobOrder.Where(j => j.Status == status && j.RelatedCompanyID == companyID)
        .Select(x => new
        {
            JobOrderID = x.JobOrderID,
            CustomerName = x.Customers.Name,
            CustomerSurName = x.Customers.Surname,
            SerialNo = x.SerialNo,
            DeviceName = x.DeviceModel.DeviceName,
            BrandName = x.DeviceModel.DeviceBrand.BrandName,
            State = x.State,
            ServiceType = x.ServiceType,
            InsertDate = x.InsertDate,
            StaffFirstName = x.AttendedStaff.FirstOrDefault().SystemUsers.StaffFirstName,
            StaffSurName = x.AttendedStaff.FirstOrDefault().SystemUsers.StaffSurName,
            Importance = x.Importance
        }).OrderByDescending(x => x.JobOrderID);

    return result;
}

And this block produces that kinda long sql

SELECT 
[Project6].[JobOrderID] AS [JobOrderID], 
[Project6].[Name] AS [Name], 
[Project6].[Surname] AS [Surname], 
[Project6].[SerialNo] AS [SerialNo], 
[Project6].[DeviceName] AS [DeviceName], 
[Project6].[BrandName] AS [BrandName], 
[Project6].[State] AS [State], 
[Project6].[ServiceType] AS [ServiceType], 
[Project6].[InsertDate] AS [InsertDate], 
[Project6].[StaffFirstName] AS [StaffFirstName], 
[Project6].[StaffSurName] AS [StaffSurName], 
[Project6].[Importance] AS [Importance]
FROM ( SELECT 
    [Project5].[JobOrderID] AS [JobOrderID], 
    [Project5].[SerialNo] AS [SerialNo], 
    [Project5].[Importance] AS [Importance], 
    [Project5].[ServiceType] AS [ServiceType], 
    [Project5].[State] AS [State], 
    [Project5].[InsertDate] AS [InsertDate], 
    [Project5].[Name] AS [Name], 
    [Project5].[Surname] AS [Surname], 
    [Project5].[DeviceName] AS [DeviceName], 
    [Project5].[BrandName] AS [BrandName], 
    [Project5].[StaffFirstName] AS [StaffFirstName], 
    [Extent8].[StaffSurName] AS [StaffSurName]
    FROM   (SELECT 
        [Project3].[JobOrderID] AS [JobOrderID], 
        [Project3].[SerialNo] AS [SerialNo], 
        [Project3].[Importance] AS [Importance], 
        [Project3].[ServiceType] AS [ServiceType], 
        [Project3].[State] AS [State], 
        [Project3].[InsertDate] AS [InsertDate], 
        [Project3].[Name] AS [Name], 
        [Project3].[Surname] AS [Surname], 
        [Project3].[DeviceName] AS [DeviceName], 
        [Project3].[BrandName] AS [BrandName], 
        [Project3].[StaffFirstName] AS [StaffFirstName], 
        (SELECT TOP (1) 
            [Extent7].[StaffID] AS [StaffID]
            FROM [dbo].[AttendedStaff] AS [Extent7]
            WHERE [Project3].[JobOrderID] = [Extent7].[JobOrderID]) AS [C1]
        FROM ( SELECT 
            [Project2].[JobOrderID] AS [JobOrderID], 
            [Project2].[SerialNo] AS [SerialNo], 
            [Project2].[Importance] AS [Importance], 
            [Project2].[ServiceType] AS [ServiceType], 
            [Project2].[State] AS [State], 
            [Project2].[InsertDate] AS [InsertDate], 
            [Project2].[Name] AS [Name], 
            [Project2].[Surname] AS [Surname], 
            [Project2].[DeviceName] AS [DeviceName], 
            [Project2].[BrandName] AS [BrandName], 
            [Extent6].[StaffFirstName] AS [StaffFirstName]
            FROM   (SELECT 
                [Extent1].[JobOrderID] AS [JobOrderID], 
                [Extent1].[SerialNo] AS [SerialNo], 
                [Extent1].[Importance] AS [Importance], 
                [Extent1].[ServiceType] AS [ServiceType], 
                [Extent1].[State] AS [State], 
                [Extent1].[InsertDate] AS [InsertDate], 
                [Extent2].[Name] AS [Name], 
                [Extent2].[Surname] AS [Surname], 
                [Extent3].[DeviceName] AS [DeviceName], 
                [Extent4].[BrandName] AS [BrandName], 
                (SELECT TOP (1) 
                    [Extent5].[StaffID] AS [StaffID]
                    FROM [dbo].[AttendedStaff] AS [Extent5]
                    WHERE [Extent1].[JobOrderID] = [Extent5].[JobOrderID]) AS [C1]
                FROM    [dbo].[JobOrder] AS [Extent1]
                INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]
                INNER JOIN [dbo].[DeviceModel] AS [Extent3] ON [Extent1].[DeviceModelID] = [Extent3].[DeviceModelID]
                LEFT OUTER JOIN [dbo].[DeviceBrand] AS [Extent4] ON [Extent3].[BrandID] = [Extent4].[DeviceBrandID]
                WHERE ([Extent1].[Status] = @p__linq__0) AND ([Extent1].[RelatedCompanyID] = @p__linq__1) ) AS [Project2]
            LEFT OUTER JOIN [dbo].[SystemUsers] AS [Extent6] ON [Project2].[C1] = [Extent6].[UserID]
        )  AS [Project3] ) AS [Project5]
    LEFT OUTER JOIN [dbo].[SystemUsers] AS [Extent8] ON [Project5].[C1] = [Extent8].[UserID]
)  AS [Project6]
ORDER BY [Project6].[JobOrderID] DESC

Well I couldn't tell if that long query is ok or not. I need help to create a little bit shorter and well performance queries.

do not worry for EF this is not a long query! there are a lot of tricks in EF to optimzie the queries: https://www.simple-talk.com/dotnet/.net-tools/entity-framework-performance-and-what-you-can-do-about-it/ — Bassam Alugili, Jun 16 '16 at 13:06

score 0 · Answer 1 · answered Jun 16 '16 at 16:14

First and foremost make sure than any relationship you're using in your select statement is included (joined) in your query. Based on your select statement, I would guess you'd need to add something like:

.Include("Customers").Include("DeviceModel").Include("AttendedStaff.SystemUsers")

Next, anything you're querying on should be an indexed field, namely Status and RelatedCompanyID.

How to create shorter queries with EF?

1 Answers1