Value of foreign key in not coming in the foreign table

Question

I have two database tables i.e "Sales" and "SaleItems". SaleId is a foreign key in SaleItems but i'm not getting the value. I'm pushing the "Product" database table's data to both "Sales" and "Saleitems". SaleId is generating in "Sales" table but i'm not getting it in "SaleItems" table. kindly help. Here's my View Code "table" class = "table" > "col" >Product Id "col" >Company "col" >Product Name "col" >User Name "col" >User Mobile "col" >Price per product "col" >Quantity "col" >Total Price "2" >Grand Total : "GrandTotal" > $( '#productSelect' ).change( function () { var id = $( this ).val(); if (id > 0) { $.get( "GetProduct" , { productId: id }, function (result) { console.log(result) $( "tbody" ).append( " " + result.ProductId + " " + result.CompanyName + " " + result.ProductName + " " + result.UserName + " " + result.UserMobile + " " + result.ProductPrice + " + id + "," + result.ProductPrice + ")'>- + id + "' value='0'>0 + id + "," + result.ProductPrice + ")'>+ + id + "'>0 x " ) CalculateGrandTotal(); }); } }) $( "body" ).on( "click" , "#btnSave" , function () { //Loop through the Table rows and build a JSON array. var sales = new Array(); var saleitems = new Array(); $( ".table tbody tr" ).each( function () { var row = $( this ); var Sale = {}; Sale.UserName = row.find( "td" ).eq(3).html(); Sale.UserMobile = row.find( "td" ).eq(4).html(); Sale.NetTotal = $( '#GrandTotal' ).html(); sales.push(Sale); var SaleItem = {}; SaleItem.ProductId = row.find( "td" ).eq(0).html(); SaleItem.ProductName = row.find( "td" ).eq(2).html(); SaleItem.ProductQuantity = row.find( "td" ).eq(7).find( 'button[type=text]' ).html(); saleitems.push(SaleItem); }); var model = { sales:sales , saleitems:saleitems }; //Send the JSON array to Controller using AJAX. $.ajax({ type: "POST" , url: "/Product/Insertsales" , data: JSON.stringify(model), contentType: "application/json; charset=utf-8" , dataType: "json" , success: function (r) { alert(r + " records inserted." ); } }); }); And Here's my Controller Code public JsonResult InsertSales(List sales , List saleitems) { using (sampledb6Entities sampledb6Entities = new sampledb6Entities()) { //Truncate Table to delete all old records. //sampledb6Entities.Database.ExecuteSqlCommand("TRUNCATE TABLE [Sales]"); //Check for NULL. if (sales == null ) { sales = new List (); } //Loop and insert records. foreach (Sale sale in sales) { sampledb6Entities.Sales.Add(sale); sampledb6Entities.SaveChanges(); } //Check for NULL. if (saleitems == null ) { saleitems = new List (); } var SaleIds = sales.Select(x => x.SaleId).ToList(); int Counter = 0; foreach (SaleItem saleitem in saleitems) { SaleItem si = new SaleItem(); si.SaleId = SaleIds[Counter]; si.ProductName = saleitem.ProductName; si.ProductQuantity = saleitem.ProductQuantity; si.ProductId = saleitem.ProductId; sampledb6Entities.SaleItems.Add(saleitem); Counter++; } int insertedRecords = sampledb6Entities.SaveChanges(); return Json(insertedRecords); } }

Ronika Jency · Answer

Hi, you may have one sale with many sale item right. so declare the salesitem inside the sale. like this SaleItem SaleItems. you can find the same, below class public class Sale { public int SaleId { get ; set ; } public string SalesName { get ; set ; } public SaleItem SalesItems { get ; set ; } } public class SaleItem { public int SalesItemId { get ; set ; } public int SalesItemName { get ; set ; } public int SaleId { get ; set ; } } below code will insert a record in sale and saleitem table with foriegn key foreach (Sale sale in sales) { sampledb6Entities.Sales.Add(sale); foreach (SaleItem saleitem in sale.SalesItems) { SaleItem si = new SaleItem(); si.SalesItemId = saleitem.SalesItemId; si.SalesItemName = saleitem.SalesItemName; sampledb6Entities.SaleItems.Add(saleitem); } sampledb6Entities.SaveChanges(); }

Sachin Singh · Answer

After Saving Sales into database, you can get SaleId of all currently added rows and then you can save SaleItems data with foreign key SaleId like below Don't use two list in Controller Action instead use ViewModel public class Sale { public int SaleId { get; set; } public string SalesName { get; set; } public SaleItem SalesItems { get; set; } } public class SaleItem { public int SalesItemId { get; set; } public int SalesItemName { get; set; } public int SaleId { get; set; } } Public Class SalesVM { public List Sales{get;set;} public List saleItems {get;Set;} } public void Save(SalesVM model) { List sales=model.sales; List SaleItems= new SaleItems(); _Context.Sales.AddRange(sales); _Context.SaveChanges(); //Sales successfully saved into databse // now you can retrieve currently added sale's Id like below line var SaleIds = Sales.Select(x => x.Id).Tolist(); int Counter=0; Foreach(var item in model.saleItems) { SaleItem si= new SaleItem(); si.SaleId= SaleIds[counter]; si.ProductName=model.SaleItems.ProductName; ____--- items.Add(si); Counter++; } _Context.SalesItems.AddRange(items); Context.SaveChanges(); }