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?

Woodcutter

Is MySQL not capable of optimizing both queries to the same plan? Maybe the difference does not matter at runtime. – Fairbanks

#12691275 – Sperrylite

I don't know if it helps, but EF won't use key information on views to determine join types either. It only selects using an inner join on tables, never on views. – Premaxilla

@CharlieBrown - Great thanks for that. I guess this question is sort of a duplicate of the other one. – Woodcutter

It seem it reads the view query and treat it as sub-query, I think it will give you in the end same performance . – Flagstone

This is the price one pays when using ORMs – Educt

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

Capitulate