Linq insert / update

Standard Linq functionality

Insert

Run newRun = new Run();
newRun.StartTime = DateTime.Now;
dc.Runs.InsertOnSubmit(newRun);
dc.SubmitChanges();

If there are results, they have to be returned as OUT parameters in the sproc. (eg. Id, Tms)

Update

mRun = (from r in dc.Runs where r.RunID == mRun.RunID select r).Single();
mRun.Errors = errors;
dc.SubmitChanges();

Personal standard for inserting / updating

Using stored procedures

Normally an insert / update / delete is done by a stored procedure. This procedure handles the insert/update/delete and returns the inserted / updated record. In case of update / delete a timestamp should ensure no other user changed the record in the mean time.

Data layer

namespace DL
{
  public static class InvoiceDL
  {
    public static Invoice Insert(Invoice newInvoice)
    {
      DataContext dc = new DataContext();
      long? customerId = newInvoice.Customer == null ? null : (long?)newInvoice.Customer.CustomerID;
      Invoice inserted = (from i in dc.csInvoice_I1(customerId, newInvoice.Amount, newInvoice.Date)
                          select i).Single();
      return inserted;
    }

    public static Invoice Update(Invoice changed)
    {
      DataContext dc = new DataContext();
      long? customerId = changed.Customer == null ? null : (long?)changed.Customer.CustomerID;
      Invoice updated = (from i in dc.csInvoice_U1(changed.InvoiceID, customerId, changed.Amount, changed.Date, changed.Tms)
                         select i).Single();
      return updated;
    }
  }
}

Remarks:
customerID
If you set the customer of the invoice with newInvoice.Customer = someCustomer, newInvoice.CustomerID is NOT updated. Setting newInvoice.CustomerID manually will cast an exception!
Just using newInvoice.Customer.CustomerID can cast an exception is there is no customer attached.
This code should not cast an exception, since it is possible that an invoice doesn't have a customer (yet).

Don't use using:
using (DataContext dc = new DataContext())
{
long? customerId = changed.Customer == null ? null : (long?)changed.Customer.CustomerID;
Invoice updated = (from i in dc.csInvoice_U1(changed.InvoiceID, customerId, changed.Amount, changed.Date, changed.Tms)
select i).Single();
return updated;
}
Because in this case the subobjects won't be loaded.

Update on 2016-09-15:

ContractContact contact01 = new ContractContact
{
  Contact = new Bgs.Contract.Be.Contact { Name = "Unit test dealer name" },
  ContactID = 1,
};

will cast ForeignKeyReferenceAlreadyHasValueException.
ContractContact contact01 = new ContractContact
{
  ContactID = 1,
  Contact = new Bgs.Contract.Be.Contact { Name = "Unit test dealer name" },
};

the ContactID will be overwritten to 0.
Good solution:
ContractContact contact01 = new ContractContact
{
  Contact = new Bgs.Contract.Be.Contact {  ContactID = 1, Name = "Unit test dealer name" },
};

Linq will update contact01.ContactID to 1.

Business layer

How did I get to this?

Passing Linq objects as parameters

Linq interaction: internal object

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License