I have a single page which collects related data into a series of input fields.

When the user clicks submit I want to insert the data into two different database tables in one go. ie I want to get the primary key from the first insert, and then use it for the second insert operation.

I am hoping to do this all in one go, but I am not sure of the best way to do this with the Models/Entities in MVC.

I'd try something like

private YourFirstTableDBEntities _dbFirst = new YourFirstTableDBEntities();
private YourSecondTableDBEntities _dbSecond = new YourSecondTableDBEntities();

[AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Edit( ...

        var secondTableStuff = (from m in _db.SecondTableSet where m.Id == firstTableForeignKey.Id select m).First();
       _db.ApplyPropertyChanges(firstTable.EntityKey.EntitySetName, firstTable);
       _db.SaveChanges();
       _db.ApplyPropertyChanges(secondTable.EntityKey.EntitySetName, secondTable);
       _db.SaveChanges();

People more expert than me can help you with the correct syntax.  Syntax is always torturing me.

I would not be surprised if there are more elegant solutions.

TIMTOWTDI =. there is more than one way to do it.

Regards,
Gerry (Lowry)

I have interesting question. In that case we use two Models - in each model we have:

private databaseDataContext db = new databaseDataContext();

it means two connections to database !! Is it ok in ASP.NET MVC ??

Thanks for your reply. I should have mentioned that I am not using Linq2Sql.

I am looking for a solution of how to insert more than one entity using the Model Binding approach?

I do not think it is a problem.  In a complex application, one could be connected to multiple databases.

I would hope that with two connections to the same database, there might be some efficiency that one gets automatically.

I'm not a database expert, regardless, this seems normal to me.  In our application, we are abstracting to a logical model of our data.  The physical model should be more or less irrelevant.  We should be able to restructure the physical model without changing the logical model AFAIK.

Regards,
Gerry

Your context instance should provide change tracking for you.  I gather from the original poster that we're talking about ONE context, but TWO models in that context. Maybe I'm mistaken.

IN the case that I'm correct, then you only need to instantiate one context and save changes once after applying the changes to both entities; it'll navigate the relationships and such to make sure keys line up.

Also in that case, you're only tying up one connection.

IF you meant to be hitting two *different* datasources, then Gerry's correct on all counts.

 Sorry, missed the question about model binding.  That's fairly straightforward.  The easiest case is to create a viewmodel that wraps both, like this:

public class MyViewModel
{
    public FirstEntity Model1 {get; set;}
    public SecondEntity Model2 {get;set;}
}

Your view would then inherit from ViewPage<MyViewModel> for example, adn you would write your form fields using syntax like this:

<%= Html.TextBox("Model1.Name") %>
<%= Html.TextBox("Model2.Name") %>

The method that receives the post should expect a parameter of the type of the viewmodel as well.

Hope that helps,

Paul

gerrylowry:

I do not think it is a problem.  In a complex application, one could be connected to multiple databases.

I would hope that with two connections to the same database, there might be some efficiency that one gets automatically.

I'm not a database expert, regardless, this seems normal to me.  In our application, we are abstracting to a logical model of our data.  The physical model should be more or less irrelevant.  We should be able to restructure the physical model without changing the logical model AFAIK.

 

Regards,
Gerry

I wanted to comment on this.  I'm not a database expert, either, but I've been on all sides of them through my career (install, dba, develop, app developer, etc).  I wanted to drill down a bit on the pro's and cons of the scenarios here.

Multiple Db's.  Using multiple connections in and of itself isn't a problem; doing so synchronously and in series is probably a reliability and performance issue, especially if one considers that you may be taking a network hit to talk to them.  Consider that in the above code, with two DBs, you are forced to wait for the first to respond before calling the second.  If the entities are not related, there's no good reason for that and the whole thing can (and should) be done using asynchronous calls (though that ups the complexity of the solution).  If they are related, you can still use asynch methods, you just have to use callbacks to pass the keys needed between them.  In both cases, you have to consider how to notify your user if things went wrong, etc.  A potentially more elegant solution would be to use a message queue of some sort (or bus that guarantees reliable messaging) so that you can respond to the user that "all is well" as soon as the objects hit the queue, since the  queue's job is to make sure that the transaction happens successfully.

Multiple connections to the same DB.  Less of an issue.  Yes, ADO.Net (and by extension L2S and L2E) does pool connections, so that you can take less of a hit for opening new ones, but you do still *use* two connections out of that pool, and that's still a finite resource.  In a high traffic site, you could still  run into issues.  While you can change the # of pooled connections, that changes the physical resource consumption profile of your app, and why hasten the rate at which your app uses resources needlessly? 

That said, one of the things that most of the modern ORMs do pretty well (L2S, L2E, NHibernate, LLBLGENPro) is minimizing footprint on the connection pool.  They grab a connection at teh very last second, and release it as soon as possible.  So the danger is probably minimal for using multiple "contexts" (using the generic term) at the same time.  So, multiple connections at the same time == bad.  Multiple contexts, probably less so.

Finally, I would agree that in a perfect world the logical model would be completely ignorant of the persistence means, and the physical model could be changed willy-nilly.  I have found that from a practical standpoint, that's not always the case.  Some ORM tools are better than others at allowing that (e.g. NHhibernate is better at that than L2S or L2E), but there are always tradeoffs.  If you don't want to expose your properties as virtual, and you're using NHibernate, then you either have to tell it to bypass your properties and go straight for the fields (which you may not want to do) or else you have to accept not lazyloading them.  Which may or may not be a good choice for you, performannce-wise.  L2S, you have to accep ttha tyou'll only be using SQL Server.  The list goes on.