Wouter Wouter - 1 month ago 6
ASP.NET (C#) Question

Saving OData entities from api with id to database in entity framework

I have multiple projects that return the same OData entities through a API endpoint. Now i want to call all of the projects and store them in my calling projects database with entity framework.

To add them to the db the ID gets overwritten but i want to save the id that the entity has in the projects database as well. so i can still access them if need be and to check if the data isn't already in my database. Because of this i need to add another MainProjectID and projectID column to the entity.

I tried making a new class that has a reference to the entity i want to save but this used new id's for the entities. I also tried inheriting the class but this gave me key conflict issues, and generics don't work either in entity framework(i'm not saying they should). So i'm kinda at a loss right now.

I basically want to save the id as a non-key. Is there any way i can do this without writing entirely new classes and parsing them manually ?

Any help would be greatly appreciated.

Answer

We have multiple alternatives here:

  • In a distributed system, best way to cope with these kinds of ID clashes is to make IDs globally unique. If you can modify how IDs are generated, that would be my choice to go. You can use a UUID (or Microsoft implementation GUID) that will produce a universal unique identifier. Or if that seems like an overkill you can devise a simple mechanism that combines ID with projectID. However you should ensure that the method you will use will not produce any collisions (no two different id-projectId pair will map to same value).

    This will ensure that same entity is used throughout your application and no overlaps occur if you try to put records from different sources into the same table. You only need to implement a mechanism to record which ID originated from which source. You can use a reference entity at aggregator for this purpose. You also need to disable auto increment nature of the ID column so that your global unique values are used in table.

  • You can use different entities for producing and aggregating applications. I don't know your application, but that seems like an OK approach to me since the aggregating application has a different idea about the entity. The aggregating application cares for which application produced the entity, that might make putting the source application identifier into the entry justifiable. Your entities will only differ in that and when you receive the OData object from API you'll need copy all other properties and put project identifier yourself.

  • You can use the previous solution, but you can use a derived class in order to not to repeat your object properties. This is a better design alternative. However with this method you'll have some problems with the primary key (as you've stated you had). Consider this example

    public class Base {
        public int ID { get; set; }
    
        [Required]
        [StringLength(50)]
        [Display(Name = "Name")]
        public string Name { get; set; }
    }
    
    public class Derived : Base {
        [Key]
        public int projectId {get; set; }
    }
    

    If you don't put [Key] to Derived then you'll have only ID as primary key. When you put [Key] to Derived then you'll have only projectId as primary key. You need to define a composite key and you can do this by removing the [Key] annotation from projectId and using the onModelCreating override of DbContext

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Derived>().HasKey(a => new { a.ID, a.projectId })
                                        .Property(c => c.ID).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
        modelBuilder.Entity<Derived>().Property(c => c.projectId).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
    }
    
  • You can mix these alternatives. You can drop the primary key on ID field, and then you can insert a new Entity that will model 1-M relationship between ID's and project ID's.