Value of foreign key in not coming in the foreign table

Hamza Shah

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">

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ronika Jency5y ago
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();
}
0
Sachin Singh5y ago
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();

}
0