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

Question

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.

By "overwritten" do you mean that on DB they are handled as auto_increment columns and original values are lost? If so, take a look at that : http://stackoverflow.com/questions/7206273/disabling-identity-auto-incrementing-on-integer-primary-key-using-code-first — infiniteRefactor, Oct 19 '16 at 23:14
Yes, but I still can't use the original values of the id as key since they might collide with each other from project to project. Since they do not all come from the same dB. And since the entity's will basically all be put into the same table the id's will collide. — Lieke, Oct 19 '16 at 23:23
Then since you tried inheritance and referencing, I guess you can not modify the entity class? — infiniteRefactor, Oct 19 '16 at 23:44
I could if necessary, but I'd rather not add any values only for the aggregation. Since that seems like bad design to me( to add fields only for the aggregation). But if necessary it is possible. — Lieke, Oct 19 '16 at 23:47
I am thinking of maybe using the id and the projectname together as a primary key. And then turning off the autoincrement like you said. Which I'll try tomorrow. — Lieke, Oct 19 '16 at 23:52

score 3 · Accepted Answer · answered Oct 20 '16 at 01:11

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.

This solved my problem. Very good answer with multiple good options to choose from as well as a solution for the design problem of repeating code. Thank you very much. — Lieke, Oct 20 '16 at 06:47

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

1 Answers1