We are using entity framework 5/6 to generate a database first Model, to expose some views
I noticed in the generated model, that many columns are defined as Keys (EntityKey = true) which made entity framewrok include order by statements in almost every query to that view.
I don't know the rule of thumb here, but through trial and error and monitoring the generated SQL queries in SQL profiler, I concluded that Entity Framwork adds order by to all the queries using the columns marked as Entity Keys.
ORDER BY [ID] ASC, [Name] ASC, [Type] ASC
If I modified the edmx file through dessigner and set EntityKey = false to Name and Type columns the query is:
ORDER BY [ID] ASC
The question is, how does Entity Framework decide that [ID],[Name], and [Type] are Entity Keys in the first place?
The Problem is, so many order by may affect performance, isn't that right?, and the [Name] column is of type XML, so SQL server simply throws exception
The XML data type cannot be compared or sorted, except when using the IS NULL operator.