none
Saving Parent-Child Collections to Sql Server RRS feed

  • Question

  • Using WebApi 2, I'm trying to create a service that inserts into SQL Server. The problem occurs when I have a request that contains multiple child nodes.  The first node gets saved while others are not.  I've tried every approach using the Entity Framework (data-first, code-first, etc) but none seem to be able to successfully create a model, controller, etc. 

    The input data will have data that goes into a parent table and then will have child data that goes into another table.  I can get code working to save to both of these tables in the scenario where there is one child.  However, if there is more than one, only one saves.  Are there any examples or tutorials for this scenario?   For example:

    Sample Input Data:

    {
      "LOG_ID": 1112,
      "DP_ID": 2,
      "IP": "string",
      "HOST": "string",
      "LogDetail": {
        "LOG_DTL_ID": 111,
        "LOG_ID": 1112,
        "METRIC_TYPE_ID": 1,
        "EVENT_TYPE_ID": 2,
        "KEY_VAL_PAIR": "string",
        "CRTE_DTTM": "2016-07-13T17:23:06.994Z"
      },
      "LogDetail": {
        "LOG_DTL_ID": 121,
        "LOG_ID": 1112,
        "METRIC_TYPE_ID": 2,
        "EVENT_TYPE_ID": 2,
        "KEY_VAL_PAIR": "string",
        "CRTE_DTTM": "2016-07-13T17:23:06.994Z"
      }
    }

    Sample models look similar to:

    public class LOGGING
        {
            public int LOG_ID { get; set; }
            public int CDP_ID { get; set; }
            public string IP { get; set; }
            public string HOST { get; set; }
            public System.DateTime CRTE_DTTM { get; set; }    
            public LOG_DETAIL {get;set;}
        }
    	
    	 public partial class LOG_DETAIL
        {
            public int LOG_DTL_ID { get; set; }
            public int LOG_ID { get; set; }
            public int METRIC_TYPE_ID { get; set; }
            public int EVENT_TYPE_ID { get; set; }
            public string KEY_VAL_PAIR { get; set; }
            public System.DateTime CRTE_DTTM { get; set; }
        }


    Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D

    Thursday, July 14, 2016 3:24 PM

Answers

  • Hi polymorphic,

    Based on your description and related code, it seems that you could use one-to-many relationship, and your model classes look similar to:

    public class LOGGING
        {
            public int LOG_ID { get; set; }
            public int CDP_ID { get; set; }
            public string IP { get; set; }
            public string HOST { get; set; }
            public System.DateTime CRTE_DTTM { get; set; }
    
            public virtual ICollection<LOG_DETAIL> LOG_DETAILs { get; set; }
    
        }
    
        public partial class LOG_DETAIL
        {
            public int LOG_DTL_ID { get; set; }
            public int LOG_ID { get; set; }
            public int METRIC_TYPE_ID { get; set; }
            public int EVENT_TYPE_ID { get; set; }
            public string KEY_VAL_PAIR { get; set; }
            public System.DateTime CRTE_DTTM { get; set; }
    
            //Navigation Property
            public virtual LOGGING LOGGING { get; set; }
        }

    For more information about Getting started with ASP.Net Web API 2 using CodeFirst with one-to-many relationship, please refer to: 

    http://www.codeproject.com/Articles/821439/Getting-started-with-ASP-Net-Web-API-using-CodeF

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 15, 2016 4:37 AM
    Moderator

All replies

  • The Web APi should be calling a DAL and the DAL does the CRUD operations with the DB using EF on the behalf of the Web API.

    You should be able to test everything in the DAL with the DAL dealing with EF by using a Console Application, as an example, before you even get to using the Web API. All The Web API has to do is call methods on the DAL.

    Thursday, July 14, 2016 8:19 PM
  • Hi polymorphic,

    Based on your description and related code, it seems that you could use one-to-many relationship, and your model classes look similar to:

    public class LOGGING
        {
            public int LOG_ID { get; set; }
            public int CDP_ID { get; set; }
            public string IP { get; set; }
            public string HOST { get; set; }
            public System.DateTime CRTE_DTTM { get; set; }
    
            public virtual ICollection<LOG_DETAIL> LOG_DETAILs { get; set; }
    
        }
    
        public partial class LOG_DETAIL
        {
            public int LOG_DTL_ID { get; set; }
            public int LOG_ID { get; set; }
            public int METRIC_TYPE_ID { get; set; }
            public int EVENT_TYPE_ID { get; set; }
            public string KEY_VAL_PAIR { get; set; }
            public System.DateTime CRTE_DTTM { get; set; }
    
            //Navigation Property
            public virtual LOGGING LOGGING { get; set; }
        }

    For more information about Getting started with ASP.Net Web API 2 using CodeFirst with one-to-many relationship, please refer to: 

    http://www.codeproject.com/Articles/821439/Getting-started-with-ASP-Net-Web-API-using-CodeF

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 15, 2016 4:37 AM
    Moderator
  • Hi Cole,

    Thank you for the tutorial.  It is a simple example and easy to understand.  I had reviewed this example before missed some of the points.  After some reading and going back and reviewing this, I was able to understand much more.

    Thanks,

    cj


    Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D

    Thursday, July 28, 2016 4:50 PM