ASP.NET MVC: How to Insert Data Into Multiple Tables?

Question

I have two tables (NPG_Chemical and NPG_Chemical_Synonym):

public partial class NPG_Chemical
{
    [Key]
    [Column(TypeName = "numeric")]
    public decimal NPG_Chemical_ID { get; set; }

    [StringLength(256)]
    public string Chemical { get; set; }
}

public partial class NPG_Chemical_Synonym
{
    [Key]
    [Column(TypeName = "numeric")]
    public decimal NPG_Chemical_Synonym_ID { get; set; }

    [ForeignKey("NPG_Chemical_ID")]
    [Column(TypeName = "numeric")]
    public decimal NPG_Chemical_ID { get; set; }

    [StringLength(512)]
    public string Synonym { get; set; }
}

In the NPG_ChemicalController I have something like:

    [HttpPost]
    public ActionResult Create(NPG_ChemicalViewModel model)
    {
        using (var context = new NPG_Model())
        {
            var chemical = new NPG_Chemical();
            chemical.Chemical = model.NPG_Chemical.Chemical;
            context.NPG_Chemical.Add(chemical);

            var synonym = new NPG_Chemical_Synonym();
            synonym.Synonym = model.NPG_Chemical_Synonym.Synonym;
            synonym.NPG_Chemical_ID = chemical.NPG_Chemical_ID;
            context.NPG_Chemical_Synonym.Add(synonym);
            context.SaveChanges();
        }
        return View();
    }

and NPG_ChemicalViewModel:

namespace NPG_Administrative_Utility.Models
{
public class NPG_ChemicalViewModel
{
    public NPG_ChemicalViewModel()
    {
        NPG_Chemical = new NPG_Chemical();
        NPG_Chemical_Synonym = new NPG_Chemical_Synonym();
    }

    public NPG_Chemical NPG_Chemical { get; set; }
    public NPG_Chemical_Synonym NPG_Chemical_Synonym { get; set; }
}
}

When I try to create a view based on NPG_ChemicalViewModel, it shows:

Can any one help me on this?

Answer 1

You'll need a view model. At the simplest, you can just do something like:

public class NPG_ChemicalViewModel
{
    public NPG_ChemicalViewModel()
    {
        NPG_Chemical = new NPG_Chemical();
        NPG_Chemical_Synonym = new NPG_Chemical_Synonym();
    }

    public NPG_Chemical NPG_Chemical { get; set; }
    public NPG_Chemical_Synonym NPG_Chemical_Synonym { get; set; }
}

Then, change your action to accept this:

public ActionResult Create(NPG_ChemicalViewModel model)

In your view, you would generate the individual properties like:

@Html.EditorFor(m => m.NPG_Chemical.Chemical)

However, it's far better to only include the properties on your view model that you want to be edited:

public class ChemicalViewModel
{
    public string Chemical { get; set; }
    public string Synonym { get; set; }
}

Then, in your action, you just map this posted data where it should go:

var chemical = new NPG_Chemical();
chemical.Chemical = model.Chemical;
context.NPG_Chemical.Add(chemical);

var synonym = new NPG_Chemical_Synonym();
synonym.Synonym = model.Synonym;
synonym.NPG_Chemical_ID = chemical.NPG_Chemical_ID;
context.NPG_Chemical_Synonym.Add(synonym);

That said, there's some significant issues with your code here. First, it looks like you're dealing with a one-to-one or one-to-many relationship here between Chemical and Synonym, but right now, you have no foreign keys being utilized. You should add a navigation property to your synonym class:

[ForeignKey("NPG_Chemical_ID")]
public NPG_Chemical Chemical { get; set; }

That tells Entity Framework that you have a relationship and among other things allows it to automatically fill in IDs as necessary. For example, with that, you could now simply do:

synonym.Chemical = chemical;

Instead of directly referencing the ID. That way, if the id is autogenerated or otherwise unknown before saving, the relationship will still be preserved. Whereas, without it, you'd have to save chemical first, set the autogenerated id on synonym and then save the synonym in a separate transaction.

Second, if you're going to use keys typed as "numeric". Then, you're going to be responsible for generating a unique numeric string for each record. That's a huge pain, as it's going to require checking a proposed id against other existing record ids before actually saving. Otherwise, you run the risk of a primary key collision. It's far better to use a standard autoincrementing PK or barring that, at least a GUID, where you're assured a reasonably low risk of collisions occurring.

Third, you should absolute not use using with your context. Here it's not a big deal, since, you're only saving and not reading data from the database, but in a typical view, lazy-loading will kick you in the posterior quick doing that. Your context should be request-scoped, either as an instance variable on your controller (since the controller is newed up and disposed with each request) or using dependency injection. You never want to create an instance of your context anywhere else, including an action method.