5

I have the following situation between these three tables:

alt text

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. :(

Tejo
  • 1,268
  • 11
  • 18
  • Is there a unique constraint in the database on the File table for the FileNumber and FileType fields? – Dr. Wily's Apprentice Aug 27 '10 at 18:31
  • This isn't my area, so hopefully someone else can provide better information. I found this answer (http://stackoverflow.com/questions/1963829/how-can-i-add-constraints-to-an-ado-net-entity/1979093#1979093), and googled the referenced thread (it's in google's cache). Short answer seems to be a tentative yes, you could trick EF by modifying the SSDL, but I don't think this works for you because you mentioned there are already other relationships to File that use FileId, so if you change the "primary key" of File in the SSDL, then you'll break those relationships. – Dr. Wily's Apprentice Aug 27 '10 at 18:51
  • There is a Unique Index for them. – Tejo Aug 27 '10 at 18:53
  • Yeah, we considered changing the "primary key" in the EF model, but the issue with breaking the other relationships keeps us from using that solution :( – Tejo Aug 27 '10 at 18:55
  • 1
    You say you can't modify the database structure...how about adding a new table? You could add a new table (let's call it FileReference) that has FileNumber and FileType as the primary key and have your TransactionLine table link to that. You could also put a foreign key constraint on FileReference pointing to those two fields on the File table (good thing you already have a unique index), making a 1-to-1 relationship between File and FileReference. I know...it's an ugly suggestion. – Dr. Wily's Apprentice Aug 27 '10 at 19:16
  • This could work, but the tables we can create MUST have only one field in the primary key(actually they are created with two fields.. Code(varchar(8)) and Name(varchar(50)) and this is arbitrary(this doesn't come from our company, but we have to put up with these rules). :( We tried to do this with an entity that maps only __FileNumber__ and __FileType__ from the File table, but the EF complains that wee didn't map the __FileId__ field – Tejo Aug 27 '10 at 20:14
  • This isn't correct data modeling, but is it possible for you to make File.FileNumber plus File.FileType a foreign key referencing TransactionLine? EF would more properly consider TransactionLine to be an association rather than a separate entity, since it's just a join table. However, that doesn't solve your problem, since the association will still complain about not referencing the key. – Andrew Aug 27 '10 at 22:40

1 Answers1

2

We archieved our goal :D.

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

Here is what we did:

alt text

  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.

Tejo
  • 1,268
  • 11
  • 18