EFCore SqlException for SelectListItem projection using same property for Value and Text

Question

I created a SelectListHelper to keep all of my dropdown data population in a central location. It projects rows in my database into collections of IEnumerable<SelectListItem> that can be consumed by my views. Here is a portion of the class:

public class SelectListHelper
{
    private AppDbContext _db;

    public SelectListHelper(AppDbContext db)
    {
        _db = db;
    }

    public IEnumerable<SelectListItem> GetCountries(bool showAll = false)
    {
        return _db.Set<Country>().Where(x => showAll || x.Enabled == true).OrderBy(x => x.Name).Select(x => new SelectListItem { Value = x.Abbreviation, Text = x.Name }).ToList();
    }

    public IEnumerable<SelectListItem> GetGoogleCategories(bool showAll = false)
    {
        return _db.Set<GoogleCategory>().Where(x => showAll || x.Enabled == true).OrderBy(x => x.Name).Select(x => new SelectListItem { Value = x.Name, Text = x.Name }).ToList();
    }
}

GetCountries, and all other functions omitted for brevity, work just fine. They use distinct columns projected onto the Value and Text properties of the SelectListItem.

GetGoogleCategories projects the Name column onto both the Value and Text properties of the SelectListItem. This produces the following SqlException:

An exception of type 'System.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code Invalid column name 'Value'.

When I looked at the SQL being generated by the GetCountries function, it looked like I expected:

SELECT [x].[Abbreviation] AS [Value], [x].[Name] AS [Text]
FROM [Countries] AS [x]
WHERE [x].[Enabled] = 1
ORDER BY [Text]

However, the SQL generated by the GetGoogleCategories function did not look like I expected:

SELECT [x].[Name] AS [Text]
FROM [GoogleCategories] AS [x]
WHERE [x].Enabled] = 1
ORDER BY [Value]

I'm using EfCore 2.1.0 in Visual Studio 2017 (15.7.3). Any ideas what might be going on here? I can work around this by returning an array of Names and manually building a list of SelectListItems, but this has me worried about what other things in my Data Access Layer might be working improperly.

---

public class GoogleCategory
{
    [Key]
    public int Id { get; set; }

    [Required, StringLength(250)]
    public string Name { get; set; }

    public bool Enabled { get; set; } = true;
}

public class Country
{
    [Key]
    public int Id { get; set; }

    [Required, StringLength(250)]
    public string Name { get; set; }

    [Required, StringLength(2)]
    public string Abbreviation { get; set; }

    public bool Enabled { get; set; } = true;
}

Answer 1

It's a 2.1 regression bug tracked by #12180: Invalid column name: orderby uses a column alias that does not exist.

The workaround for your particular case is to use the SelectListItem constructor with parameters (a feature introduced in 2.1):

return _db.Set<GoogleCategory>()
    .Where(x => showAll || x.Enabled == true)
    .OrderBy(x => x.Name)
    .Select(x => new SelectListItem(x.Name, x.Name) })
    .ToList();

but of course the concerns remain.