EF Views With Left Joins Not Displaying database data returned

Question

EF5, VS12, SQL10

I have a view with a Left Join that returns 4 rows.
The view is used for a report so there is severely de-normalized data in it.  All the fields that are not part of the left join have identical values.  It's easier to make reports this way.

When the view is placed in EF it marks all the fields except the left join fields as PK.  The Left join fields are marked as null

A sql server trace shows the correct select being sent to sql server and the correct data coming back.
Sent:  select columname1, col2, etc from MyView where id=55
Returned: 4 rows with correct values in all fields.

However when I look at the results from the EF, all the rows are identical.  Where the 4 left join fields should have different values.

It looks like the EF is just repeating the 1st row 4 times; Maybe because all the PK values are the same?

Anyway to get EF to display the row values returned from SQL server?

Answer 1

Hi,

The problem here, as you guessed, is because the primary keys are all the same. Once an entity is created in a context then EF will not add another entity with the same primary key. So you are getting the same one over and over.

There are a couple of possible workarounds. The most obvious is to add something to your view that makes each row unique. The heuristic for determining if something is a key or not when importing a view is to just make everything that is non-nullable a key. So if you add another column that has a unique number in it and update from schema then it should make this work for you.

An alternative to that would be to create a stored procedure to do the query and execute the stored proc. By default the stored proc will create complex types, not entities. So the primary key problem doesn't exist in that scenario.

Another alternative might be to use a non-tracking query. If it is a  non-tracking query then it should avoid the process that tracks the entity, so EF will not know that it already has the entity and so it will return each one. see here: http://msdn.microsoft.com/en-us/data/jj556203

Hopefully that makes sense and one of the possible solutions will work for you. Let me know how you go.

---

We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

Answer 2

Went with non-tracking.  Using views sure is a easy way to introduce hard to detect bugs.  Not sure that should be a feature.  Maybe EF should throw an exception when it feels their are multiple PK rows returned.