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

4 posts, 1 answers
  1. Gary
    Gary avatar
    28 posts
    Member since:
    Jun 2015

    Posted 03 Sep 2015 Link to this post

    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; }
     
    }

  2. Answer
    Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 08 Sep 2015 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
  4. Gary
    Gary avatar
    28 posts
    Member since:
    Jun 2015

    Posted 16 Sep 2015 Link to this post

    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. 

  5. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 21 Sep 2015 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top