CODEWITHSUNDEEP
c#linq
Craig
Craig

Reputation: 18734

Performance difference between Linq Select and ToList

Would the following linq expressions both result in the same performance?

In my conjured up scenario, my table, MyData. has 25 columns, and 50,000 rows. I am trying to get all MyData records based on a WHERE.

var myData = _myDb.Entities<MyData>()
        .ToList()
        .Select(p => new { p.Name, p.Id })
        .Where(y => y.DepartmentId = 1)
        .OrderBy(x => x.Name);

vs

var myData = _myDb.Entities<MyData>()
        .Where(y => y.DepartmentId = 1)
        .Select(p => new { p.Name, p.Id })
        .OrderBy(x => x.Name)
        .ToList();

I think the first query would basically execute a SELECT * FROM MyData due to the order of the ToList() in the statement. Costly. All data brought back to client, to then apply ordering and filtering.

Where as the 2nd option would be better, executing a SELECT Name, Id FROM MyData WHERE DepartmentId = 1 ORDER BY Name - so smaller set of data, ordered by SQL, in this case.

Or would Linq work this out, and do the latter, regardless?

Upvotes: 1

Views: 1322

Answers (1)

Dawood Awan
Dawood Awan

Reputation: 7348

No, LINQ would not work this out regardless.

I have created a sample of your queries using my database in Linq Pad.

The first one is this

TelematicDevices
.ToList()
.Where(s => s.sReference.Contains("abc"))
.Select(p => new { p.sReference, p.iDeviceId})
.OrderBy(x => x.sReference)
.Dump();

Which generates the following SQL

SELECT 
    [Extent1].[iDeviceId] AS [iDeviceId], 
    [Extent1].[iDeviceTypeId] AS [iDeviceTypeId], 
    [Extent1].[iGSMProviderId] AS [iGSMProviderId], 
    [Extent1].[sAssetClass] AS [sAssetClass], 
    [Extent1].[iCompanyId] AS [iCompanyId], 
    [Extent1].[iAssetId] AS [iAssetId], 
    [Extent1].[sReference] AS [sReference], 
    [Extent1].[sDescription] AS [sDescription], 
    [Extent1].[sSoftwareId] AS [sSoftwareId], 
    [Extent1].[sIMEI] AS [sIMEI], 

   // ... ALL OTHER COLUMNS IN MY TABLE

    FROM [dbo].[TelematicDevices] AS [Extent1]
GO

And the second query 

TelematicDevices
.Where(s => s.sReference.Contains("abc"))
.Select(p => new { p.sReference, p.iDeviceId })
.OrderBy(x => x.sReference)
.ToList()
.Dump();

generates the following SQL

SELECT 
    [Extent1].[iDeviceId] AS [iDeviceId], 
    [Extent1].[sReference] AS [sReference]
    FROM [dbo].[TelematicDevices] AS [Extent1]
    WHERE [Extent1].[sReference] LIKE N'%abc%'
    ORDER BY [Extent1].[sReference] ASC

As you can see there is a difference.

1st query will select all data and columns - then filter and order by in the application back-end.

2nd query will select only the 2 columns, filter and then order by on the database server.

Upvotes: 7

Related Questions