Problems updating/inserting with Linq-to-SQL + child relationships

Question

Hi, I'm learning LINQ, and am having trouble with inserting and updating in one go. I have a StatusReport table (School, Customer, Time), where School is the primary key, and a Service table (School, ServiceName, State, and Version), where School is a foreign key, and School + ServiceName is the primary key.

Here's my update code:

public MockReport(int numDrives, int numServers, int numCameras, string customer, string school)
{
    _school = school;
    string c = "...";
    using (DataClasses1DataContext _context = new DataClasses1DataContext(c))
    {
        _context.CommandTimeout = 60;
        Random random = new Random();

        bool inserting = false;
        _report = _context.StatusReports.SingleOrDefault(s => s.School == school);
        if (_report == null)
        {
            _report = new StatusReport();
            inserting = true;
        }


        updateService("System Monitor", "Running", "1.0.0.0");


        _report.Customer = customer;
        _report.School = school;
        _report.Time = DateTime.Now;

        if (inserting)
        {
            _context.StatusReports.InsertOnSubmit(_report);
        }

        _context.SubmitChanges();
    }
}

 private void updateService(string serviceName, string state, string version)
{
    Service service = _report.Services.SingleOrDefault(s => s.School == _school && s.ServiceName == serviceName); // returns null!
    bool inserting = false;
    if(service == null)
    {
        service = new Service();
        inserting = true;
    }
    service.ServiceName = serviceName;
    service.State = state;
    service.Version = version;
    if(inserting)
    {
        _report.Services.Add(service);
    }
}

The insert is fine, but the update fails - I get a SQL exception: Violation of PRIMARY KEY constraint 'PK_dbo.Service'. Cannot insert duplicate key in object 'dbo.Service'. The statement has been terminated.

Also, Service service = _report.Services.SingleOrDefault(s => s.School == _school && s.ServiceName == serviceName); returns null, even if the data is there.

Any ideas?

Answer 1

Your primary key contraint error is telling you that there's a duplicate value somewhere in the table you're trying to insert your new value. So your new value will be a duplicate of an already existing value.

Check that is not the case.

On the second error: if Service is an object, I don't know that your SingleOrDefault method will create that object for you...

You should create the Service object and then find the data you want to update in your database, stick it in the Service object, update the object and commit to your database.