Entity Framework issues with views (and possibly tables as well)

Question

Hi,

I found some issues, the Visual Studio 2008 SP 1 designer for Entity Framework has with views. I understand that the designer has problems to get the primary key fields for views which do not have a proper primary key field included. But even if a primary key field is included, the designer does nor recognize this field correctly as the primary key. And to make things worse, changing the primary key for a view is not as easy as it should be.

For example: The view 'vProductAndDescription' of AdventureWorks includes the 'ProductID' field, which is the primary key of the (leading) Product table. But the Entity Framework designer creates a primary key including all fields of the view.

So far, so bad. If I correct the primary key in the designer (set 'Entity key' only for the field ProductID to true), the conceptual model is updated, but not the storage model. In the XML I find the following entry:

<EntityType Name="vProductAndDescription">
   <Key>
      <PropertyRef Name="ProductID" />
      <PropertyRef Name="Name" />
      <PropertyRef Name="ProductModel" />
      <PropertyRef Name="Culture" />
      <PropertyRef Name="Description" />
   </Key>
   <Property Name="ProductID" Type="int" Nullable="false" />
   <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
   <Property Name="ProductModel" Type="nvarchar" Nullable="false" MaxLength="50" />
   <Property Name="Culture" Type="nchar" Nullable="false" MaxLength="6" />
   <Property Name="Description" Type="nvarchar" Nullable="false" MaxLength="400" />
</EntityType>

The key is not changed. I have to change the key in the XML to get the entity model working (otherwise I would get the error that all key properties of the entity set must be assigned to all key properties of the table).

Another issue is, that a view (or table) which ich deleted in the designer is only deleted from the coceptual model, not from the storage model. To reinclude the view (or table), I have to restore the conceptual model or delete the entries from the storage model and add the view via updating in the designer.

This could not be by design, could it?

Answer 1

 

I have expieranced similar issues you are describing. The only thing i would like to add to this is, if you manually go in and change the ssdl that a view should have productid as the entity key, it will get overwritten anyways the next time u refresh the model from the database.

 

Thanks

Zeeshan Hirani

Answer 2

Yes, I experianced this too, sadly ..

Answer 3

Let me try to respond to the raised issues...

 

- We are exploring ways of allowing users to edit the SSDL, but the difficulty is that the update model from database feature needs to regenerate the whole SSDL. We have spent a tremendous amount of energy trying to solve this, but it doesn't look like we'll be able to do a lot for this in the next release. Preserving changes to key definitions is certainly on our list, but it's a bit tricky and at this time it does not fit. However, we do plan on preserving user-created <DefiningQuery> elements, which will allow you to work around this issue by creating a defining query over your view.

 

- There is no plan for causing changing key definitions in the conceptual model to change the key definitions in the storage model. It's an interesting idea, though, and will be given more thought.

 

- FWIW, we are also planning on allowing users to delete tables and stored procedures from the storage model, which should take care of the last issue. There is also some discussion about deleting items from the storage model that become "unmapped" - e.g. when you delete an entity in the conceptual model, we will delete any table mapped to it that is not mapped by anything else. This is a bit tricky though, and I am not sure if it will make it into the product.

 

HTH,

  Noam

Answer 4

Any idea how EF infers the EntityKeys for a view?

Answer 5

It appears to use any non-nullable fieldsn - which is really confusing, as this would appear to make the entity framework pretty useless with a view.

Answer 6

Kevin - you are correct, all non-nullable fields are read as keys. You can certainly change this in the SSDL, modulo the warning above about the SSDL getting regenerated when you update. We're working on fixing that.

Not sure why this would make the EF useless with views though.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 7

Well, Noam - can you write updates to an entity based on a view if all your non-nullable fields are marked as entity keys? If the only way to achieve this is  to go and update the auto-generated markup every time you update your model from the database, well then that makes it essentially useless with a view in my books.

Answer 8

I second Kevin's opinion about EF. I am using EF with Silverlight 3 and ADO.Net Data Services. You can find a work around for this problem here:
http://asheeshsoni.blogspot.com/2009/09/entity-framework-messes-up-primary-keys.html

---

as

Answer 9

Thanks for that workaround Asheesh. I don't see why they couldn't just prompt for confirmation of entity key inference for a view, because it really doesn't make a lot of sense to use non-nullable fields as keys. Unfortunately it doesn't look as if they plan to make any changes to the way they infer entity keys for the next version of EF (judging from the existing documentation on MSDN which still recommends hand editing), so it looks like we'll have to use your fix for a while to come.

Answer 10

I suggest some solutions to your problem of update EF :

- Using views of the model : presents EDM in 2-3 views (graphically, why not) for each layer of SSDL: physical (storage), conceptual. Developper will can add, change or delete "manually" some feature on schem;

- On demand update and only on designate parts (on user's selection in the diagram, why not ?). Also, open a window and lets user designate things to update or not. This will help to ovoid losing of feature manually add;

Thus, beginnner user can use automatic feature and experimented user can use advanced feature.

Answer 11

What is SSDL Exactly?? and how is it related to Entity Model and framework?/ I am beginner and want to understand

Answer 12

asheeshsoni,

Thank you for that work-around.  Interestingly, it shows a rugged way for how to prevent EF columns mapped to a view from showing up as primary keys.  However, my problem was somewhat the opposite - I needed to make view columns appear as primary keys in EF.  I was casting some columns in the view, and as a result, they were being made nullable (even though I knew that they could not be null).  I wanted to the view to return them as non nullable, so that I could use them as primary keys in the entity framework.  Here's the workaround for that:

http://stackoverflow.com/questions/2326813/how-to-make-a-view-column-not-null

Hope you get as much use from this link as I got from your :-)

Answer 13

This is just an another work-around,

 

Create the views such a way that, EF will identity only one column as identity column.

 

Ex.

 

CREATE View dbo.vEmployee As Select lEmployeeId, EmployeeName, Designation From Employee Union All Select 0 as lEmployeeId, Null as EmployeeName, Null as Designation Where 1 = 0

 

In this example, lEmployeeId will be considered as Identity column. No need to updated the SSDL & CSDL.

 

Thanks

Gopinath B.

---

Gops

Answer 14

This is just an another work-around,

 

Create the views such a way that, EF will identity only one column as identity column.

 

Ex.

 

CREATE View dbo.vEmployee As Select lEmployeeId, EmployeeName, Designation From Employee Union All Select 0 as lEmployeeId, Null as EmployeeName, Null as Designation Where 1 = 0

 

In this example, lEmployeeId will be considered as Identity column. No need to updated the SSDL & CSDL.

 

Thanks

Gopinath B.

---

Gops

Answer 15

If the "Update model from Database" has to guess what columns to include in the key, then copying whatever used to be defined as key before the wizard is run will in 99.97458647% cases be much better than overwriting that with all non-nullable columns.

And it's exactly what I did in my VS plugin.

And ... in an OnSave handler the plugin also copies the primary keys from the entities based on views from concept to store. While in case of tables, the store knows best, for views the store knows nothing.

So far it works perfect.

Jenda

Answer 16

Have you ever consider enhancing EF model update procedure to look for a unique cluster or unique index on the view and use that unique index definition to define the keys? Especially an index that has a name that starts with the psuedo standard "PK_"?

That would make our life soooooooooo much easier if all we had to do is define an index for the view that begins with "PK_".

Thanks.

---

Software Engineer