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.
我正在创建一个应用程序，以便将数据从一个数据库传输到另一个新应用程序，我正在运行一个问题，访问旧数据库中的一些数据。旧的数据库是使用Ruby on Rails创建的，它们是通过Rails设计和创建的，SQL Server中的数据库没有主键，所有列都是空的。新的数据库是在SQL Server中设计和构建的，具有正确的键和可空列。由于这两个数据库都位于SQL Server中，所以我希望首先使用实体框架数据库，以简化数据转换。
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:
在EF Desginer中，我可以为所有表分配实体键，除了一个(响应)，这使我无法正确访问表中的数据。表定义如下:
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.
但是同一个question_id和question_version可能有多个answer_id和answer_version记录，或者answer_id和answer_version是空的，所以我不能使用这个。这个键的任何子集都不允许我正确地检索所有的数据。此外，我不能使用created_at或updated_at列，因为有多个行实例正在使用相同的时间戳编写。
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?
我只需要读取数据，不需要编写数据，因为它正在转换为新的数据库，而且我没有办法更改现有的数据库。有没有办法绕过这个关键问题?
2 个解决方案
#1
2
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.
为了绕过这个关键问题，我选择了一个主键，它至少不会失败，在这种情况下，它只会是assess_id和person_id列。这让我构建时没有任何错误，但我仍然不能正确地检索所有数据。
The workaround is to use a SqlQuery on the database.
解决方案是在数据库上使用SqlQuery。
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.
这将在整个数据库上执行查询，并将结果设置为包含正确数据的响应列表。确保您在数据库上执行查询，而不是在表中执行查询，否则将使用设计器中指定的错误键，您将无法获得正确的数据。
#2
1
Put your SQL in a stored procedure and call that from your application.
将SQL放在存储过程中，并从应用程序调用它。
If you cannot modify the source database, you can put the stored procedure in the destination.
如果不能修改源数据库，可以将存储过程放在目标中。
#1
2
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.
为了绕过这个关键问题，我选择了一个主键，它至少不会失败，在这种情况下，它只会是assess_id和person_id列。这让我构建时没有任何错误，但我仍然不能正确地检索所有数据。
The workaround is to use a SqlQuery on the database.
解决方案是在数据库上使用SqlQuery。
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.
这将在整个数据库上执行查询，并将结果设置为包含正确数据的响应列表。确保您在数据库上执行查询，而不是在表中执行查询，否则将使用设计器中指定的错误键，您将无法获得正确的数据。
#2
1
Put your SQL in a stored procedure and call that from your application.
将SQL放在存储过程中，并从应用程序调用它。
If you cannot modify the source database, you can put the stored procedure in the destination.
如果不能修改源数据库，可以将存储过程放在目标中。