I need to to build a GUI to query a database, and I opted to use Entity Framework and the database-first approach.

The database has a flaw in it's layout, in my opinion, and I wonder what my options are to correct this in the ef model (and how).

The database looks like this:

CREATE TABLE a (
    idA int
)
CREATE TABLE b (
    idB int
)
CREATE TABLE c (
    idC int,
    fkA int,
    fkB int
)

The design issue I see is that items in B do not exits alone, they are always related to A. The following tables would make more sense:

CREATE TABLE a (
    idA int
)
CREATE TABLE b (
    idB int,
    fkA int,
)
CREATE TABLE c (
    idC int,
    fkB int
)

With words, c is set up to be a child of independent a and b, while in reality, b is always a child of a, and c is a child of b (and of a in consequence).

How would I modify the generated model to change this, if this is possible at all? Using Visual Studio, and the EDMX Model editor, obviously, but what changes do need to make to the model so that is still loads the wrong database layout, but offers the corrected one to the GUI?

The GUI will only read data, there is no need write anything anytime.

Thanks!

1 Answers

2
Innominatum On Best Solutions

If you want to apply changes to the database you'll need more than read only access. When the changes are made and saved in the database, you can update the model.

If you are using an Ado.net edmx file, updating is easy. Select the .edmx file and select update model, select the related tables from the database and follow the remaining of the GUI.

If you need to something from the package manager console or from dotnet console. It is wise to use an tutorial from here.

Update from OP

It's possible but it could result to many unwanted side-effects. When you are getting the objects from the database you should box them into new objects that represent the newly wanted structure. Another way to do this is to change the model, you'll need to add properties to the given classes. Be warned that your code will suffer from lot's of side effects.