Asking for suggestions on how to proceed with no FKs

Question

I am at the beginning of re-writing an old ASP.NET WebForms application, into an ASP.NET Core app using Razor and Blazor. The difficult step I am facing now is the database. The original developers, all who left before I was hired, had originally used several XML files as a data store. Then at some point they migrated all those XML files to a SQL Server database, with no primary or foreign keys. Ideally Entity Framework works best if the primary and foreign key relationships are defined. Well, they aren't. Years ago, I added primary keys to the database, but the foreign keys are harder. Sometimes what I think would be the foreign key in one table doesn't have a primary key by the same name in another table. I can make an educated guess, but that's the best I can do. And sometimes the data types aren't the same. (Sometimes it's a SMALLINT in one table and an INT in another. Or sometimes it's a VARCHAR in one table and a CHAR in another.)

I've been fretting on this for weeks and my management is getting impatient with me. I could do the best I can writing new EF using the old app for guidance. And there are gaps in the sequence of numeric fields which are primary keys. So. now I'm wondering if I should just forget trying to get the database properly set up and just go with what I've got, which is just primary keys? I'd like to ask for some feedback, please.

Answer 1

I'd probably design a new database schema for the new app, and write a conversion app for the data. skips in sequence is normal, and will occur even with identity.

if you can not change the schema, then I'd write a database abstraction layer and use whatever db access you understand best. the abstraction layer should be methods that return objects.

note: FK are used to define relationships between tables. Tables need not be related, but you should inspect all the join clauses carefully. Your current schema probably has lots of left joins.

Answer 2

Hi @Falanga, Rod, DOH

Years ago, I added primary keys to the database, but the foreign keys are harder. Sometimes what I think would be the foreign key in one table doesn't have a primary key by the same name in another table. I can make an educated guess, but that's the best I can do. And sometimes the data types aren't the same. (Sometimes it's a SMALLINT in one table and an INT in another. Or sometimes it's a VARCHAR in one table and a CHAR in another.)

As we all known, in SQL Server Database, it does not allow creating a foreign key constraint between columns of different data types. The data types of the primary key in the referenced table and the foreign key in the referencing table must be compatible. Specifically, they must be the same type or types that are considered compatible by SQL Server (e.g:int with int, nvarchar with nvarchar).

In your scenario, I think you can try the following options:

1. Modify the Data Type. Modify the column in one of the tables to match the data type in the other table, then add foreign key.
2. Use EF Core SQL Queries. Entity Framework Core allows you to drop down to SQL queries when working with a relational database. More detail information, see SQL Queries.
3. Use EF Core with Stored procedures or View. Refer to How to call stored procedure procedure using entity framework core?

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 3

Hi @Falanga, Rod, DOH

I do believe that I have a problem if I tried to introduce an identity property to one of the columns. For example, the primary key column on most of the tables have some missing values. There's values like 1, 3, 4, 5, 7, 8, 11, etc. When defining an identity column, whether it is a primary key or not, you can specify a seed and an interval. There's no way I can specify an interval that allows for +1 in some cases, then +2 or +3, etc. in other cases. And if that table is the primary key, then the foreign key relationship gets totally messed up if I can't match the values in the primary key to the foreign key values in the related table.

Based on the table (timetrack and r_job) definition, do you mean the jobid column in timetrack might contain value not in the jobid column in r_job table? If that is the case, when you set the foreign key, it will cause the foreign key constraint error.

So before setting the foreign key, you can query to check the records in timetrack that are causing the conflict, and then handle them:

SELECT * 
FROM timetrack 
WHERE jobid NOT IN (SELECT jobid FROM r_job);

Then, you can refer to the following steps to configure foreign key using EF core methods:

1.In the Asp.net core application appsettings.json file set the database connection string like this:

   {
    "ConnectionStrings": {
      "DefaultConnection": "your database connection string"
    },

2.Use Scaffolding (Reverse Engineering) to generate classes based on the existing database and tables (check the Scaffolding Prerequisites and require install EF Core Tools).

In my MVC application, right click the project and select the Open in Terminal option, in the terminal console executes the following command to generate the class:

dotnet ef dbcontext scaffold "Name=ConnectionStrings:DefaultConnection" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models --context-dir Data --context ApplicationDbContext --force --table r_job --table timetrack

the output as below: it will generate the RJob and Timetrack class and add the relate DbSet in the ApplicationDbContext:

3.Then in the RJob, Timetrack and ApplicationDbContext class, you can add the navigation property and configure relationship and add the foreign key:

4.In the Package Manger Console, run the add-migration addonetooneforeignkey command, in the migration file, change the content as below:

public partial class addonetooneforeignkey : Migration
{


    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<int>(
            name: "jobid",
            table: "timetrack",
            type: "int",
            nullable: true,
            oldClrType: typeof(int),
            oldType: "int");

        migrationBuilder.CreateIndex(
            name: "IX_timetrack_jobid",
            table: "timetrack",
            column: "jobid",
            unique: true,
            filter: "[jobid] IS NOT NULL");

        migrationBuilder.AddForeignKey(
            name: "FK_timetrack_r_job_jobid",
            table: "timetrack",
            column: "jobid",
            principalTable: "r_job",
            principalColumn: "jobid");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_timetrack_r_job_jobid",
            table: "timetrack");

        migrationBuilder.DropIndex(
            name: "IX_timetrack_jobid",
            table: "timetrack");

        migrationBuilder.AlterColumn<int>(
            name: "jobid",
            table: "timetrack",
            type: "int",
            nullable: false,
            defaultValue: 0,
            oldClrType: typeof(int),
            oldType: "int",
            oldNullable: true);
    }
}

5.After that, running the update-database command in the Package Manger Console to update the database.

6.After refresh the database from SSMS, you can see jobid column in the timetrack already is the foreign key.

In my above sample, I configure them with One-to-one relationships, you can also configure other relationships for them, refer to Introduction to relationships.

Besides, you can also try to use sql command to add the foreign key and then use the Scaffolding (Reverse Engineering) to generate classes based on the existing database.

Refer to #SQL FOREIGN KEY on ALTER TABLE

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 4

Here’s an approach that worked for me in a similar scenario:

Introduce New Primary Keys: By adding a new ID field as the primary key in each table, you can simplify the process significantly. Since this new key is unique and consistent, it serves as a reliable reference point across tables, eliminating gaps or inconsistencies present in the older fields.

Define Foreign Key Relationships Using the New Keys: With a new primary key in place, you can reference it directly as a foreign key in related tables, bypassing the complications of the existing fields. This approach avoids the mismatches in data types, naming conventions, and gaps found in older fields.

Programmatic Mapping for Legacy Relationships: If you still need to link historical fields or verify relationships, you could write scripts to identify patterns across tables, but these are no longer essential for basic functionality. The new primary and foreign keys take precedence, allowing Entity Framework to function smoothly.

This approach allowed me to work effectively with Entity Framework and meet management’s expectations without reworking legacy fields in depth. The new primary key and foreign key structure becomes the reliable foundation for the updated application.