CODEWITHSUNDEEP
c#oracle-databaselinqentity-framework-6
Larsbj
Larsbj

Reputation: 38

Foreign keys which does not exist

I am stuck with a legacy database with multiple views and tables, with mostly correct foreign key values. However, some of these "foreign keys" are not nullable, so the DBA has, in his infinite wisdom, decided to use "0" instead of null.

In this example, OWNERID in CAR would be set to 0 instead of null if the car has no owner. When EF attempts to retrieve all cars, only cars with a valid OwnerId would be returned, and the ones with 0 left out. 

[Table("CAR")]
public class Car {
      [Key, Column("CARID")]
      public int CarId { get;set;}
      [Column("OWNERID")]
      public int OwnerId { get;set;}

      [ForeignKey("OwnerId")]
      public virtual Owner {get;set;}
}

[Table("OWNER"]
public class Owner {
      [Key, Column("OWNERID")]
      public int Id {get;set;}
}

Query:

  var allCars = context.Cars.Include(c => c.Owner).ToList();

This gives only the cars with a valid OwnerId (>0) in return. Currently, I "solve" this by doing two queries, like this:

  var carsWithOwner = context.Cars.Include(c => c.Owner).Where(c => c.OwnerId > 0).ToList();
  var carsWithoutOwner = context.Cars.Where(c => c.OwnerId == 0).ToList();

Any pointers or recommendations of what to do? I would like to get all cars in return, and with Owner set to null where OwnerId is 0.

I cannot change the database.

Upvotes: 1

Views: 1510

Answers (1)

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

If you want to allow ownerID field to be nullable, you have to change your EF model anyway.

[Column("OWNERID")]
public int? OwnerId { get;set;}

At this point your problem should be solved as Include uses LEFT JOIN if foreign key is nullable. All the cars with ownerId = 0 should have Owner navigation property set to NULL.

If this still does not work, you could create a view on top of existing table and query that. I know that you have stated that you cannot change database, but I guess it means that you cannot change existing table schema.

1) Create view vw_CAR

CREATE VIEW vw_CAR
AS 
SELECT 
    CARID, 
    CASE WHEN OWNERID = 0 THEN NULL ELSE OWNERID END AS OWNERID
FROM 
    CAR

2) Change table to view 

[Table("vw_CAR")]

Upvotes: 2

Related Questions