Avoid duplicate entries in Entity Framework, using MSSQL and .Net Core

Question

I have 2 related tables, which are Assemblies and Parts.

My Assembly class:

public class Assembly{
  public int Id { get; set; }
  public string No{ get; set; }
  public string Name { get; set; }

  public int? ParentId { get; set; }
  public Assembly Parent { get; set; }

  public List<Assembly> Children { get; set}
  public List<Part> Parts { get; set; }
}

My Part class:

public class Part{
   public int Id { get; set; }
   public string No { get; set; }
   public string Name { get; set; }
   public string Image { get; set; }

   public int AssemblyId { get; set;}
   public Assembly Assembly { get; set; }
}

What am I doing is, first I'm creating Assemblies in a controller. Then when I create an Assembly, a file upload page is opening, then I upload an Excell files and map the excell data to my Part Object as a reference to my Assembly object.(Every parts AssemblyId will be the AssemblyId comes from route)

But there is a huge problem with this, because most of the Assemblies have the same Part or Parts in its Parts list.

For example, lets say there is a part, Part No : 555 , Part Name : Axle.

And lets assume that Im going to create 5 Assembly in my project. And every Assembly will going to have data imported from different excell files.

And each excell contains the same Part which its Part No : 555 and Part Name: Axle but obviously with different AssemblyId(FK).

But still in my Database there are so many Parts which has the same information with its duplicates but the little difference is that their AssemblyId column is different and referencing to another Assembly in the AssemblyId.

What I'd like to achive is to avoid from this situation...

I don't want to repeat my data. Actually I think a solution but Im not sure that its a good solution.. I might do a "if" statement and check, while uploading data from excell, if a Part with those information aleady exists in the database I might not upload that specific row to the table, and use the part which is already exist. But I think this will slows down my project.

I hope I've asked it clearly..

Answer 1

This is a relational design issue, your Parts table should not have reference to the Assembly table. Assembly Table should have the reference only to the parts ids.

Please consider :

• To have one table for the assembly definition, this table shouldn't have parts involved, only assemblies.
• To have one table for the Assembly-Part Relation.

public class Assembly_Part{ public int AssemblyId { get; set;} public int PartId { get; set; } }

• To have Parts Table with no relation to the assemblies (remove AssemblyId from your Parts Table)

if you don't want some parts to be part of a specific assembly then you should create a extra relation for parts id-assemblies.

When you send data to the DB you should send an assembly and parts id related, not the part definition.

Then Your part definition process should take care of the part creation, not the assembly creation process.

When adding parts to the assembly you may have a distinction between new parts and old parts if both process performed together.

This is my optimal scenario:

• You want to create an assembly, your process will define a new AssemblyId and allow you to pick parts to be included in the Assembly. In DB you will only add records to Assembly Table.

• In case you need a new part, that part should be created in a different process, this process will handle the part creation and validation for existing parts. In DB you will only add records to Parts Table.

• When loading parts for an assembly you will create a List that will contain all part related to the current searches, no duplication.

Please refer to : Database Design Best Practices.

Answer 2

You can override the equals method for whichever class you are looking at (Part in this case) and compare the fields you care about to see if one Part is the same as another in the context of your app. See the following for a simple example:

  public class Part 
  {
    public string Name { get; set; }

    public DateTime? InactiveDate { get; set; }

    public int AssemblyId {get; set;}

    public override bool Equals(object obj)
    {
      Part yVal = (Part) obj;

      if (yVal == null)
      {
        return false;
      }

      // compare each of the values we care about. i.e: NOT comparing AssemblyId
      return this.Name == yVal.Name
        && this.InactiveDate == yVal.InactiveDate;
    }

You would then use this when youre going to save a new part to the database, see if any existing part .Equals any existing Part:

var existingPart = db.Parts.SingleOrDefault(x => myPartToSave.Equals(x))

if(existingPart != null)
{
    myAssemblyToSave.Parts.Remove(myPartToSave);
    myAssemblyToSave.Parts.Add(existingPart);
}

//then save your part as you normally would

If it does, then save your Assembly using the existing Part and if it doesn't, save the Assembly and Part as you normally would .

Note: before implementing this, it's probably a good idea to write a sql script (or console app, or whatever youre most comfortable with) to get rid of any duplicates that currently exist

Another Note: It's also best practice to override the GetHashCode method when you override Equals. I won't go into detail here why since it's beyond the scope of this question- you should be able to easily find information on this. There's a pretty good thread right here on SO about it