Thursday, May 30, 2019

Overcome 'The natural key for the table TableName was not found' while creating data entity

When we need to create a data entity for a table without a natural key, say VendTrans, Data entity Wizard shows the following error.

In order to overcome this limitation, we can simply feed the wizard with a copy of a query table with a natural key added. Lately we can just replace one field and its related relation and index in the data entity and staging table.

So, find VendTrans table and create a copy into your model. Delete all methods, add new String field f, make a new unique index based on this field and make this index primary and clustered. You do not need either build or synchronize your solution yet.

Now run the Wizard again with VendTransCopy as a source, and you will get all needed objects created.

Go to data entity view and change its query data source to VendTrans table.

Then rename field f to something meaningful as VendTrans_RecId and point it to VendTrans.RecId.

Add a new Int64 field with the same name in the related staging table

Then use it instead of field f in the index and relation.

You can delete field f as well as VendTransCopy table.

That is it! Now build and synchronize the solution. The new data entity is ready for use.

No comments: