Configure the Key when using Views as the basis for Entities

When using a View as the basis for an Entity in the Entity Framework (rather than a Table), make sure that the Key fields/properties are configured properly in the Model (EDMX).

We recently experienced an anomaly where a Select All from a View-based Entity was not returning All the Entities we expected. There weren’t any filters, sorts, or joins, it was a straight select using Linq to Entities.
Apparently, when we added the View to the Model, EF picked some columns (per its own magical algorithm) and made those the Key fields/Properties. When it then loads the Entities from the recordset, any record with the same Key replaces an Entity that already has that Key. So several of the records were, because of the EF generated Key, being replaced this way.
We corrected the Key on the Entity (in both the Conceptual model and the Storage model), and the Select All returned all the right Entities, as expected.
In our case, the View only returns 5 columns, so it was simple enough to make all the fields part of the Key. For a larger query, if no compound key is apparent, I expect we would need to add a RowId to the View so that the EF can use that as a surrogate Key. We do not actually use the View Key in the app (we only use the values in those rows to populate a list box that we then parse into several fields on selection), so we can use a surrogate Key without being concerned about the actual values that are actually being used.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.