Rubix_Revenge Rubix_Revenge - 2 months ago 5
C# Question

Static select lists based on database queries

In the process of refactoring an ASP.NET MVC 5 web project, I see an opportunity to move some select lists to another class where multiple controllers can access them. This would allow me to remove duplicate code.

In this instance, the select lists require a trip to the database. To hand-code the lists, which might change over time, would not be feasible (hence, the database query).

Although I have no compiler errors and the page appears to work as intended, I am not sure if I am creating other problems by taking this approach. Is the code approach shown below a "bad" way to achieve this outcome? Is there a better way to do this?

In summary, this is what I am doing:

  • The class and methods within are static

  • A private static readonly database context is defined at the top

  • The two functions shown query the database and produce the desired results.

  • Because this is a static class, there is no dispose method.

The Class:

public static class ElpLookupLists
private static readonly EllAssessmentContext Db = new EllAssessmentContext();

// code...

internal static IEnumerable<SelectListItem> StandardSelectList(string selectedDomain)
return Db.ElpStandardLists.Where(m => m.Domain == selectedDomain)
.Select(m => m.Standard).Distinct()
.Select(z => new SelectListItem { Text = z.ToString(), Value = z.ToString() }).OrderBy(z => z.Value)

internal static IEnumerable<SelectListItem> PerformanceIndicatorSelectList(string selectedDomain, int? selectedStandard,
string selectedSubConcept)
var query =
Db.ElpStandardLists.Where(m => m.Domain == selectedDomain).Where(m => m.Standard == selectedStandard);
if (!string.IsNullOrEmpty(selectedSubConcept)) query = query.Where(m => m.SubConcept == selectedSubConcept);
var list =
query.Select(m => m.PerformanceIndicator)
.Distinct().OrderBy(m => m)
.Select(z => new SelectListItem { Text = z.ToString(), Value = z.ToString() })
.OrderBy(z => z.Text).ToList();
return list;


In my opinion a better alternative would be to create a separate controller with methods to get this data, and you could OutputCache this method. You can then call this method in other controllers, and it won't make the database trip every time. The return value will be cached. You can control the cache settings of course.

The advantage of this technique over yours is that in your case, the database trip will always happen when the application starts because the method is static, irrespective of whether or not you are going to use it. Whereas by using a cached method, you make the database trip the first time you call the method.