Insert Records In Two Tables In Foreign Key Relationship Using Entity Framework Core

Introduction

 

This article will help you to insert a record in two tables using Entity Framework Core which has a foreign key relationship.

 

Step 1 - We will create table in SQL Server

 

CallDetail Table is Parent table,

CREATE TABLE [dbo].[CallDetail](
       [CallID] [int] IDENTITY(1,1) NOT NULL,
       [CreatedDate] [datetime] NOT NULL,
       [CreatedBy] [uniqueidentifier] NOT NULL,
       [FirstName] [nvarchar](50) NOT NULL,
       [LastName] [nvarchar](50) NOT NULL,
       [MobileNumber] [nvarchar](12) NOT NULL,
       [FirmName] [nvarchar](128) NULL,
       [Address] [nvarchar](512) NOT NULL,
       [LastChangedDate] [datetime] NOT NULL,
       [OutComeID] [int] NOT NULL,
       [Remark] [nvarchar](max) NULL,

 CONSTRAINT [PK_CallDetail] PRIMARY KEY CLUSTERED
(
       [CallID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CallTransactionDetail is Child table,

CREATE TABLE [dbo].[CallTransactionDetail](
    [CallTransactionID] [int] IDENTITY(1,1) NOT NULL,
    [CallID] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [CreatedBy] [uniqueidentifier] NOT NULL,
    [OutComeID] [int] NOT NULL,
    [Remarks] [nvarchar](max) NULL,
 CONSTRAINT [PK_CallTransactionDetail] PRIMARY KEY CLUSTERED
(
    [CallTransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[CallTransactionDetail]  WITH CHECK ADD  CONSTRAINT [FK_CallTransactionDetail_CallDetail] FOREIGN KEY([CallID])
REFERENCES [dbo].[CallDetail] ([CallID])
GO

ALTER TABLE [dbo].[CallTransactionDetail] CHECK CONSTRAINT [FK_CallTransactionDetail_CallDetail]
GO

We have assigned a foreign key, CallID, to CallTransactionDetail table which is the primary key in CallDetail table.

 

Step 2

 

Create Model Classes using scaffold command in the .net core application in Package Manager Console.

 

Note

You need to install the nuget package Microsoft.EntityFrameworkCore. For that please execute the below command in Package Manager Console:

 

Install-Package Microsoft.EntityFrameworkCore -Version 3.1.3

 

 

 

Scaffold-DbContext "Server=.\SQLEXPRESS;Database=dbCallMgt;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

 

The database is dbCallMgt. It will create the model classes in Models directory in .net core applications.

 

 

Step 3

 

You can see the two classes in Models directory

 

CallDetail.cs

public partial class CallDetail
    {
        public CallDetail()
        {
            CallTransactionDetail = new HashSet<CallTransactionDetail>();
        }
        public int CallId { get; set; }
        public DateTime CreatedDate { get; set; }
        public Guid CreatedBy { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string MobileNumber { get; set; }
        public string FirmName { get; set; }
        public string Address { get; set; }
        public DateTime LastChangedDate { get; set; }
        public int OutComeId { get; set; }
        public string Remark { get; set; }

        public virtual ICollection<CallTransactionDetail> CallTransactionDetail { get; set; }
    }

CallTransactionDetail.cs

public partial class CallTransactionDetail
    {
        public int CallTransactionId { get; set; }
        public int CallId { get; set; }
        public DateTime CreatedDate { get; set; }
        public Guid CreatedBy { get; set; }
        public int OutComeId { get; set; }
        public string Remarks { get; set; }

        public virtual CallDetail Call { get; set; }
    }

Step 4

 

Now we directly go to the Insert method which will insert the record in both tables:

[HttpPost]
        public async Task<ActionResult> PostCallDetail(CallDetail callDetail)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    callDetail.LastChangedDate = DateTime.Now;
                    callDetail.CallTransactionDetail.Add(new CallTransactionDetail()
                    {
                        CreatedBy = callDetail.CreatedBy,
                        OutComeId = callDetail.OutComeId,
                        Remarks = callDetail.Remark,
                    });

                    _context.CallDetail.Add(callDetail);
                    await _context.SaveChangesAsync();
                    return Ok("Inserted successfully!");
                }
                else
                {
                   return BadRequest("Failed to insert!");
                }
            }
            catch (Exception ex)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, ex);
            }
        }

So, it is very simple to add in both tables by using _context.CallDetail.Add(callDetail); and await _context.SaveChangesAsync(); command of entity framework core.