CODEWITHSUNDEEP
c#sqlsql-serverentity-framework
Evan Frisch
Evan Frisch

Reputation: 1374

Can't access Entity Framework table with no primary key

I am creating an application to transfer data from one database into another for a new application and I am running into an issue access some of the data in the old database. The old database was created using Ruby on Rails and they way it was designed and created through Rails, the database in SQL Server has no primary keys and all the columns are nullable. The new database was designed and built in SQL Server with proper keys and nullable columns. Since both databases are in SQL Server I wanted to use Entity Framework Database First to make the data transition easier.

In the EF Desginer I was able to assign entity keys to all of the tables except for one (Response), which is keeping me from correctly accessing the data in the table. The table definition is as follows:

assess_id [int] NULL
person_id [int] NULL
question_id [int] NULL
question_version [int] NULL
answer_id [int] NULL
answer_version [int] NULL
text [nvarchar(4000)] NULL
created_at [datetime] NOT NULL
updated_at [datetime] NOT NULL

Because of the allowed records the primary key should consist of

assess_id
person_id
question_id
question_version
answer_id
answer_version

but there may be multiple answer_id and answer_version records to the same question_id and question_version or the answer_id and answer_version are null so I cannot use that. Any subset of this key would not allow me to properly retrieve all the data. Also I cannot use the created_at or updated_at columns as there are multiple instances of rows being written with the same time stamp.

I only need to read the data, not write, since it is being transformed into the new database and there is no way for me to change the existing database. Is there any way around the key issue?

Upvotes: 2

Views: 1540

Answers (2)

Peter Bill
Peter Bill

Reputation: 528

Put your SQL in a stored procedure and call that from your application.

If you cannot modify the source database, you can put the stored procedure in the destination.

Upvotes: 1

Evan Frisch
Evan Frisch

Reputation: 1374

Before I answer I would like to point out that this solution only works for read-only situations and if you need to write to the table it will not help. In that case I point you to this answer where you will find some help.

I managed to find a work around for this scenario and while easy enough to use, I would not consider it optimal.

In order to get around the key issue, I picked a primary key that would at least not fail a null check, in this case just the assess_id and person_id columns. This let me build without any errors but I still could not retrieve all the data correctly.

The workaround is to use a SqlQuery on the database.

var responses = context.Database.SqlQuery<Response>("SELECT * FROM dbo.responses");

This executed the query on the whole database and casted the result set into a list of responses with the correct data. Make sure you execute the query on the database and not the table, otherwise the incorrect key specified in the designer will be used and you won't get the correct data back.

Upvotes: 2

Related Questions