How do I add an object to the database using Linq to SQL?

Question

I'm trying to learn LINQ to SQL and I'm able to query the database and get back an IQueryable and manipulate the objects I retrieve from that. But I've no idea how to add a new object back into the database or into the original IQueryable.

private DataContext db;
private IQueryable<ActionType> action;

public void BuildQuery(string connection) {
    db = new DataContext(connection);
    action = db.GetTable<ActionType>().Select(a=>a);

    ActionType at = new ActionType();
    at.Name = "New Action Type";

    // What now? action.add(at) || db.GetTable<ActionType>.add(at); ??
}

It's a suprisingly hard thing to search for if you don't know the right terms. And I can't find any examples that do exactly what I want them to do.

So, how do I go about adding new objects to a query/database?

Answer 1

To insert your newly created instance of "ActionType", you need add your object to the data context (and "add" was renamed to "InsertOnSubmit" during Linq-to-SQL beta) and then call SubmitChanges on the data context:

public void BuildQuery(string connection) {
    db = new DataContext(connection);
    action = db.GetTable<ActionType>().Select(a=>a);

    ActionType at = new ActionType();
    at.Name = "New Action Type";

    // What now? action.add(at) || db.GetTable<ActionType>.add(at); ??
    db.ActionTypes.InsertOnSubmit(at);
    db.SubmitChanges();
}

See this blog post here why you should be using InsertOnSubmit over Attach.

Marc

Answer 2

Now all you need to do is submit your changes back to the database:

db.Attach(at);
db.SubmitChanges();

Answer 3

private DataContext db;
private IQueryable<ActionType> action;

public void BuildQuery(string connection) {
   db = new DataContext(connection);
   action = db.GetTable<ActionType>().Select(a=>a);

   ActionType at = new ActionType();
   at.Name = "New Action Type";

   //There must be a table like ActionType and it seems ActionTypes when calling it ith   // db
   db.ActionTypes.InsertOnSubmit(at);
   db.SubmitChanges();
}

You can see a nice example here : Click Here

Answer 4

I'd wrap the DataContext within a using statement - it ensures it is disposed of, when the operation is complete.

Like this:

public void BuildQuery(string connection) 
{
    using (var db = new DataContext(connection))
    {
        action = db.GetTable<ActionType>().Select(a=>a);

        ActionType at = new ActionType();
        at.Name = "New Action Type";

        // What now? action.add(at) || db.GetTable<ActionType>.add(at); ??
        db.ActionTypes.InsertOnSubmit(at);
        db.SubmitChanges();
    }
}