Create a pseudo foreign key on a view using entity framework

Question

I have created a view "Supplier" that shows columns from a table "T_ADDRESS". The view is declared as (I know, the '*' is a no-go in views)

create View Supplier as
  select * from T_ADRESSEN where IsSupplier = 1

In EF, I want to use the view as it is more readable than the ugly "T_ADRESSEN". So far so easy.

Now comes the tricky part (for me). The table T_ADDRESS has a self referencing foreign key "MainAddressId" which points to T_ADDRESS.

Creating a DB-first (or CodeFirst from DB) will create the FK relationship for the table T_ADDRESS (and the navigational properties), but not for the view 'Supplier'. Of course not: EF does not know anything about the FK relationship (although the view exposes the same columns).

Now I tried to use the 'ForeignKey' and 'InverseProperty' attributes in my code first model on the Supplier-class but this gives me an ModelValidationException. Also clear: There is no such FK-relationship.

How can I tell EF to treat a field just like a foreign key although the constraint does not exist?

What I am trying to do is to have 'Suppliers' in my EF model (as a subset of T_ADDRESS). If there is another way to do it, I would be happy to receive a hint.

Answer 1

You can't define ForeignKey and InverseProperty on a view. In your case, you need to use that ugly T_ADRESSEN table and use [AutoMapper][1] to map it the to the DTO class. In your case, T_ADRESSEN is the context table and Supplier is your DTO class.

with AutoMapper you can do something like this:

       var ugly = context.T_ADRESSEN.Where(e=>e.IsSupplier ==1);
       var suppliers = mapper.Map<IEnumerable<Supplier>>(ugly);

where mapper is IMapper interface defined in AutoMapper.

Sometime one should figure out that the DTO mapping technique as a replacement to the traditional database view.