Why does entity framework create a subquery when selecting from a view?

Question

I have a table called PersonTable with the columns: PersonId, RestarauntId, Age

I have a view called PersonView that does:

select PersonId, 
       RestarauntId, 
       RestarauntName(RestarauntId) as `RestarauntName`, 
       Age 
FROM PersonTable

When I do something as simple as:

var persons = context.PersonView.Where(x=>x.PersonId == 1)
                                .Select(x=> 
                                   new {x.PersonId, 
                                        x.RestarauntId, 
                                        x.RestarauntName, 
                                        x.Age });

The above returns 1 record and I would expect the MySql query to be:

SELECT PersonId, RestarauntId, RestarauntName, Age 
FROM PersonView
WHERE PersonId = 1

BUT instead, it generates the following:

SELECT 1 AS `C1`, T.PersonId, T.RestarauntId, T.RestarauntName, T.Age
FROM
(SELECT PersonId, RestarauntId, RestarauntName, Age 
FROM PersonView) AS T
WHERE T.PersonId = 1

So it does not matter what I pass to the where clause, it always will get all the records first in a sub-select. This only happens when I query against the view which I need to, but I was curious as to why it creates the above query instead of the one I expect it to make? Is this an Entity Framework issue or a MySql Issue?

Answer 1

MySql View does not allow dynamic filters in the query used.
There are few hacks used to achieve this. But by design, mysql views are not dynamic in nature. Views always execute the actual query supplied and only on that result, further filtering can be done, as you mentioned in your example. For more details, visit Here