simbada simbada - 1 month ago 12
C# Question

fetching Id for matching row - any performance difference between two approaches?

I have method that returns

Guid?
. I come across two ways to fetch
matching Id from table
.


1- Which one is the preferred way or its just personnel preference?

2- Any performance difference?


private Guid? findCustomerId(string customerNo)
{
// 1st way
return _context.Customers.FirstOrDefault(x => x.Code == customerNo).Id;

// 2nd way
return _context.Customers.Where(x => x.Code == customerNo).Select(y => y.Id).FirstOrDefault();

}

Answer

Both approaches generate the same sql but to one difference - which columns to return of that one selected row:

// 1st way - Returns entire record to memory and then you decide to take just ID
return _context.Customers.FirstOrDefault(x => x.Code == customerNo).Id;

// 2nd way - Returns just the column of ID to memory
return _context.Customers.Where(x => x.Code == customerNo).Select(y => y.Id).FirstOrDefault();

If you strip the columns from the generated sql you can see that they are doing the same.

//Approach 1:
SELECT [Limit1].[Id] AS [Id]
FROM ( SELECT TOP (1) *
       FROM [dbo].[Customers] AS [Extent1]
       WHERE ([Extent1].[Code] = @p__linq__0) 
       OR (([Extent1].[Code] IS NULL) AND (@p__linq__0 IS NULL))
    )  AS [Limit1]



//Approach 2:
SELECT TOP (1) *    
FROM [dbo].[Customers] AS [Extent1]
WHERE ([Extent1].[Code] = @p__linq__0) 
OR (([Extent1].[Code] IS NULL) AND (@p__linq__0 IS NULL))
  1. Both queries retrieve TOP (1) records
  2. WHERE clauses are the same
  3. The nested SELECT in the first approach has no performance influence

If you don't have any cricital performance problems (or if your colums contain huge fields of data) caused by retrieving the entire record then go for readability - and the first way is more readable in my opinion