Improve SQL query performance for Metrics

[fcatae]

The following query performs joins on table FiscalYear twice, and performance is slightly degraded.

SELECT [m].[MetricID], [m].[MetricCategory], [m].[MetricName], [m].[Description], [m].[FiscalYearID], [m.FiscalYear].[FullNumericFiscalYear], [m.FiscalYear].[TextualFiscalYear]
FROM [Metrics] AS [m]
INNER JOIN [FiscalYears] AS [m.FiscalYear] ON [m].[FiscalYearID] = [m.FiscalYear].[FiscalYearID]
INNER JOIN [FiscalYears] AS [f] ON [m].[FiscalYearID] = [f].[FiscalYearID]
ORDER BY [f].[FullNumericFiscalYear], [m].[MetricCategory]

[fcatae]

Rewriting LINQ query:

var response = (from m in _context.Metrics
                join f in _context.FiscalYears on m.FiscalYear.FiscalYearID equals f.FiscalYearID
                where f.FullNumericFiscalYear == year
                orderby f.FullNumericFiscalYear, m.MetricCategory
                select new MetricViewModel
                {
                    ...
                }).ToList();

to

var response = (from m in _context.Metrics
                where m.FiscalYear.FullNumericFiscalYear == year
                orderby m.FiscalYear.FullNumericFiscalYear, m.MetricCategory
                select new MetricViewModel
                {
                    ...
                }).ToList();

[fcatae]

Query after the fix:

SELECT [m].[MetricID], [m].[MetricCategory], [m].[MetricName], [m].[Description], [m].[FiscalYearID], [m.FiscalYear].[FullNumericFiscalYear], [m.FiscalYear].[TextualFiscalYear]
FROM [Metrics] AS [m]
INNER JOIN [FiscalYears] AS [m.FiscalYear] ON [m].[FiscalYearID] = [m.FiscalYear].[FiscalYearID]
ORDER BY [m].[FiscalYearID], [m].[MetricCategory]

[allantargino]

That's great!!! This query used to bother me a lot 👍 By the time this code was written, Entity Framework was not loading Fiscal Year inside metric object: m.FiscalYear.FullNumericFiscalYear I don't know if we make some error or somehow it was making lazy loading..