Syntax error when inserting new record

Mar 25, 2013 at 5:06 PM
I'm getting a "syntax error" returned when attempting to save a new record. I've tracked it down and it appears that the "DefiningQuery" stored in the EDMX file is getting injected into the INSERT statement passed to the OLE DB driver. If I manually edit the EDMX file and remove the defining query for the table I'm adding the record to, it works (that is, completely remove the <DefiningQuery> tags and query contained within).
<EntitySet Name="Work" EntityType="VfpLenderModel.Store.Work" store:Type="Tables" store:Name="Work">
            <DefiningQuery>SELECT 
      Work.Loan, 
      Work.Address, 
      Work.City
</DefiningQuery>
          </EntitySet>
NOTE: The above sample doesn't include the full query - I chopped the rest of it off before posting.
Coordinator
Mar 25, 2013 at 5:45 PM
This is to be expected. The DefiningQuery elements are added to EDMX by the Entity Framework when a primary key or candidate key cannot be determined for the Table. In this case the Entity Framework will add a message in the Error List stating that the entityset will be read only.

To make the read only entityset editable you have to remove the DefiningQuery element, as you've done, and you should set the primary key (by default the primary key will be as a composite key of all non-nullable fields).