Adding a new entity and its' new navigation properties via a WebAPI call

[Gary]

I am attempting to add a new "DeliverablePackage" entity to my database. Each "DeliverablePackage" entity will contain references to one or more new (not currently existing) "DeliverablePackageItem" entities, so these "DeliverablePackageItem" entities need to be created at the same time as the "DeliverablePackage" entity itself. The request is being made to a WebAPI web service, so the model definition is being sent as JSON. (This example is a package with a single package item )

{
  "Label": "PackageName",
  "SKU": "PRT-040601-01",
  "Description": "It's a test package",
  "PackageItems": [
      {
        "DeliverableID": 652,
        "DeliverableQty": 1
      }
  ]
}

 

Using WebAPI this JSON is "hydrated" into my Domain models. This results in a "DeliverablePackage" model with a collection of "PackageItems" in a navigation property loaded with the details of the "DeliverablePackageItem" defined in the JSON. 

[HttpPost]
public void CreateDeliverablePackage([FromBody] DeliverablePackage model)
{
    using (CatalogAdminDbCtx ctx = new CatalogAdminDbCtx())
    {
        model = repo.ctx.AttachCopy<DeliverablePackage>(model); // changes state from "NotManaged" to "New"
        ctx.Add(model);
        ctx.SaveChanges();
    }
}

 

If I were to inspect the properties of the model reference at this time I would see something like ... ​

model.Label         = "PackageName"
model.SKU           = "PRT-040601-01"
model.Description   = "It's a test package"
model.ID            = 0                         // default value

model.PackageItems[0].DeliverableID     = 652      // Foreign key to existing Deliverable Entity
model.PackageItems[0].Deliverable       = null;    // Navigation Property to existing Deliverable Entity
model.PackageItems[0].DeliverableQty    = 1        // the number of Deliverables included in this item.
model.PackageItems[0].PackageID         = 0        // Foreign key to the DeliverablePackage that this DeliverablePackageItem is in.
model.PackageItems[0].Package           = null          // Navigation property to the DeliverablePackage that this DeliverablePackageItem is in (obviously would be self-referencing if we were to populate it)

 

In this example I am expecting a new "DeliverablePackage" to be added to the database as well as a new  "DeliverablePackageItem" associated to the "DeliverablePackage" which will reference an existing "Deliverable" (ItemID). I am instead getting a "The INSERT statement conflicted with the FOREIGN KEY constraint" ERROR because the context is attempting to insert the new "DeliverablePackageItem" but not associate it with the parent "DeliverablePackage". 

So, it appears that the problem is that the insertion of the "DeliverablePackage" is not associating it's newly generated ID with it's "DeliverablePackageItem" as I would have expected (still learning).

Is there some way for the ORM to apply this association or do I have to parse through and find it manually? 



Here are the model definitions to help illustrate how things are structured and associated.

public class Deliverable : ModelBase, ISingleID
{
    public Int64 ID { get; set; }
    public String Label { get; set; }
    public String Description { get; set; }
}

public class DeliverablePackage : ModelBase, ISingleID
{
    public Int64 ID { get; set; }
    public String Label { get; set; }
    public String SKU { get; set; }
    public String Description { get; set; }

    public IList<DeliverablePackageItem> DeliverableItems { get; set; }
}

public class DeliverablePackageItem : ModelBase
{
    public Int16 DeliverableQty { get; set; }
    //
    // foreign key and navigation property
    public Int64 PackageID { get; set; }
    public DeliverablePackage Package { get; set; }
    //
    // foreign key and navigation property
    public Int64 DeliverableID { get; set; }
    public Deliverable Deliverable { get; set; }

}

[Kristian Nikolov]

Hello Gary,

Thank you for contacting us.

Telerik Data Access supports inserting entire graphs of objects. We tried to reproduce the error you are experiencing by recreating the model based on the provided details, deserializing the provided JSON and executing the sample code for adding the object. The operation however executed successfully on our side.

As you have already guessed, this error generally occurs when the child object is not associated with its parent. We recommend making sure the SQL generated when inserting the uses the correct value for the foreign key of the DeliverablePackageItem object. You can do this with the following steps:

1. Set the LogEvents setting of your model to Verbose.
2. Log the SQL statements generated for the insert operation. You can use code similar to the following:
   

   context.Log = new StringWriter();
   context.Add(deliverablePackage);
   try
   {
       context.SaveChanges();
   }
   catch (Exception)
   { }
   string sql = context.Log.ToString();

Furthermore it appears that the argument of your controller`s endpoint is the actual entity. Therefore it would be enough to only use the Add() and SaveChanges() methods of the context or use the AttachCopy() and SaveChanges() methods. There is no need to use both AttachCopy() and Add().

I hope this helps. Feel free to get back to us via the forum in case the issue continues to persist.

Regards,
Kristian Nikolov
Telerik

 

[Gary]

Thank you Kristian. While you set me on the right path I wanted to elaborate on your answer for future reference. I think that the problem I had was that I was not explicitly defining the navigation on ​both ends of the relationship. I used the information found here to walk me through the steps.

I already had THIS ... association of the items to the package.

 

 MappingConfiguration<DeliverablePackageItem> map = new MappingConfiguration<DeliverablePackageItem>();
 //
 // DeliverablePackageItem -- Package
 map.HasAssociation(i => i.Package)
    .WithOpposite(p => p.DeliverableItems)
    .HasConstraint((i, p) => i.PackageID == p.ID)
    .IsManaged(true)
    .WithDataAccessKind(Telerik.OpenAccess.DataAccessKind.ReadWrite);

 

What I needed to add was THIS on the other end of the navigational relationship ... associating the package to the items

 

 MappingConfiguration<DeliverablePackage> map = new MappingConfiguration<DeliverablePackage>();
 map.HasProperty(c => c.ID).IsIdentity(KeyGenerator.Autoinc);
 //
 // Package -- DeliverablePackageItem
 map.HasAssociation(pkg => pkg.DeliverableItems)
    .WithOpposite(item => item.Package)
    .IsManaged(true)
    .WithDataAccessKind(Telerik.OpenAccess.DataAccessKind.ReadWrite);

 

I wanted to ​make sure to capture this here so I could reference it in the future (I WILL forget) and hopefully someone else will find it helpful as well. 

[Kristian Nikolov]

Hello Gary,

We are glad you have resolved the issue and thank you for providing information regarding how you did it.

Should you have any more questions, feel free to get back to us.

Regards,
Kristian Nikolov
Telerik