Sql Server fails to use index on simple bijection
Am I simply expecting too much?


Yes. At least in current versions of the product.

SQL Server will not pick apart the CASE statement and reverse engineer it to discover that if the result of the computed column is 'one' then [Extent1].[Number] must be 0.

You need to make sure that you write your predicates to be sargable. Which almost always involves it being in the form. basetable_column_name comparison_operator expression.

Even minor deviations break sargability.

WHERE P.Number + 0 = 0;


would not use an index seek either even though it is even more straightforward to simplify than the CASE expression.

If you want to search on a string name and get a seek on number you would need a mapping table with the names and numbers and join onto it in the query, then the plan might have a seek on the mapping table followed by a correlated seek on [dbo].[MyEntities] with the number returned from the first seek.