Wednesday, 7 January 2015

Entity Framework - Unable to update EntitySet - it has a DefiningQuery and no element exists to support the current operation.

I had this error when working on an older .Net site yesterday with EF4. It seems that the db table wasn't setup with a primary key which apparently makes EF see it as a view. Looking at the XML in the .edmx file revealed this for my table:

<entityset Name="SpecialFeatures" EntityType="EB.Store.SpecialFeatures" store:Type="Tables" store:Schema="dbo" store:Name="SpecialFeatures">
            <definingquery>SELECT 
      [SpecialFeatures].[Id] AS [Id], 
      [SpecialFeatures].[ProductDetailIcon] AS [ProductDetailIcon], 
      [SpecialFeatures].[SearchResultsIcon] AS [SearchResultsIcon], 
      [SpecialFeatures].[Name] AS [Name]
      FROM [dbo].[SpecialFeatures] AS [SpecialFeatures]
</definingquery>
          </entityset>
To overcome this problem I performed the following surgery on the db and xml:
  1. Add a primary key to the db table in SSMS
  2. Open edmx file in text editor
  3. Locate the entity in the edmx:StorageModels element
  4. Remove the DefiningQuery entirely
  5. Rename the store:Schema="dbo" to Schema="dbo"
  6. Remove the store:Name property

No comments:

Post a Comment

Comments are moderated, so you'll have to wait a little bit before they appear!