Larry Bargers Larry Bargers - 29 days ago 11
C# Question

Linq Update excel sheet from Any

I'd like to update an Excel cell with the Total Hours value from a where clause. I know the way I am currently doing this is horribly inefficient. How can I change my Linq statement to be more performant?

if (employeeHours.Any(x => x.EmployeeName == newHours.EmployeeName && x.ChargeNumber == newHours.ChargeNumber))
{
sheet.Cells[row, 5] = employeeHours.Where(x => x.EmployeeName == newHours.EmployeeName && x.ChargeNumber == newHours.ChargeNumber).FirstOrDefault().TotalHours;
}

Answer

Your if statement is a bit redundant. Also, is employee name or charge number unique? If so, a .Single() statement would be more appropriate. If they aren't unique, I'd just do the following:

var hours = employeeHours.Where(x => x.EmployeeName == newHours.EmployeeName && x.ChargeNumber == newHours.ChargeNumber)?.FirstOrDefault()?.TotalHours;

if (hours != null)
    sheet.Cells[row, 5] = hours;

Note the use of the null conditional operator ?. before and after the FirstOrDefault() method. This will essentially prevent a null exception error from being thrown if there's no entry that matches your condition, or if the entry's TotalHours property is null. You could also use First() and wrap it in a try catch block, since First() throws an error if there's no entry found (FirstOrDefault() will set the value equal to null if there's no entry found). Here's the try/catch approach:

try
{
    sheet.Cells[row, 5] = employeeHours.Where(x => x.EmployeeName == newHours.EmployeeName && x.ChargeNumber == newHours.ChargeNumber).First().TotalHours;
}
catch (Exception)
{
    // handle errors here
    throw;
}

Update: Since both the EmployeeName and ChargeNumber make a unique Id when used with one another, I'd update your code to the following:

var hours = employeeHours.SingleOrDefault(x => x.EmployeeName == newHours.EmployeeName && x.ChargeNumber == newHours.ChargeNumber)?.TotalHours;

if (hours != null)
    sheet.Cells[row, 5] = hours;
Comments