Is it possible to create an association between two normal fields in Entity Framework, thereby ignoring the primary key?

Question

I have the following situation between these three tables:

Before I start explaining what I'm trying to accomplish here, let me clarify three things:

1. I drew the model on EntityFramework, but this is actually the database view. Just assume that the fields type match between tables;

2. I know that this database model sucks. But changing it is out of question. I know that it would make my life easier.

3. I'm using Visual Studio 2008 and .Net 3.5 on this project. But I'm accepting solutions based on Visual Studio 2010 and .Net 4.0 as well.

So, as I tried to demonstrate on the picture above, in this project that I'm working on I got these 3 tables. One of then, TransactionLine, associates a file to a transaction line, that is part of a full transaction.

Now, what I'm trying to associate here is TransactionLine and File. However, TransactionLine doens't have FileId field from the File table. The fields FileNumber and FileType from this table stand for an composite key on File. And indeed, File's FileNumber and FileType never repeat. They could be the primary key, but then again, I can't touch the database structure.

I try to associate TransactionLine and File by creanting an Association of One To Many From File to TransactionLine. After that, I select the Association and change to the Mapping Details screen, then I select Map's to TransactionLine.

The data that he askes me to fill on the model screen are:

• File.FileId;
• TransactionLine.TransactionId;
• TransactionLine.FileNumber;
• TransactionLine.FileType,

All of the TransactionLine scalar properties are set by theirs corresponding database fields, which are of the same name. But when I'm going to specify the linkage with File table, the only scalar field that I can specify there is FileId, which I don't have on the TransactionLine table.

I also tried to set File's FileNumber and FileType scalar properties entity key flag to true, and FileId entity flag to false, but with no success, since the program warns me that database FileId is a primary key but is not set like one on the entity.

Is it possible to link TransactionLine FileNumber and FileType with File? Is there any workaround for this situation?

Special Note: FileId is indeed the primary key for File. Others tables link with it by this field. The TransactionLine here is an exception to the common sense rule. :(

Answer 1

We archieved our goal :D.

Thanks to Dr. Wily's Apprentice for giving us a tip about the solution.

Here is what we did:

1. We created a view, named VIEWFILEEXTRA, with just the fields we’d like to use in the association;

2. After that, we created an entity based on this view, named FileExtra;

3. Map FileExtra to VIEWFILEEXTRA, if not done yet;

4. Change FileNumber and FileType from FileExtra Entity Key flag to true;

5. Delete FileNumber and FileType scalar properties from File;

6. Create an 1 on 1 association between file and FileExtra;

7. Set the scalar properties in the association mapping to point to File’s database fields FileNumber and FileType;

8. Create an 1 to Many association between FileExtra and TransactionLine;

9. Point the association mapping to TransactionLine, and set the fields.

And that’s it. Thank you very much for your attention and tips :D.