Store into two tables with primary key simultaneously

Question

I need to store a calibration into two tables simultaneously, with one table describing the instance of the calibration (operator, date, station) and the other storing the raw data in multiple rows (9 or 10 pairs of wavelength and responsivity for a single calibration). The schema for this is below:

Spectrometer calibration schema

An example calibration:

Spectrometer calibration data

The SpectrometerCalibration.ID = SpectrometerCalibrationID for all calibration points. What I've tried (acutally using LINQ to SQL in vb.net so treat as pseudo-code)

INSERT INTO SpectrometerCalibration (StationID, OperatorID, Date) VALUES (7, 2, '2013/10/21 11:00:00.000 AM')

SELECT TOP 1 ID FROM SpectrometerCalibration ORDER BY ID DESC

-- Insert once for each wavelength / responsivity pair
INSERT INTO SpectrometerCalibrationValues (SpectrometerCalibrationID, Wavelength, Responsivity) VALUES (<from previous query>, <wl>, <val>)

This solution has an issue with timing. If two stations are calibrating simultaneously, the incorrect ID could be returned. I believe this is poor design. Trying to avoid this method.

I have seen many more complicated questions on SO, but none seem to solve my problem. I assume there is a standard way to do this correctly. No, I can't change this schema. I am using LINQ to SQL in vb.net. Is a better way to do it?

duplicate? http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — igrimpe, Oct 21 '13 at 16:05
That guy had the same issue, but his solution is in SQL and I'm not sure how to do that with LINQ in vb / if I can — djv, Oct 21 '13 at 16:08
An easy way out is always to use a GUID that you create in advance. If you are not bound to integers as key, you can change that to GUID - not automatic - and supply that GUID for both tables. — igrimpe, Oct 21 '13 at 16:14
GUIDs should be avoided whenever possible. Because GUIDs form a random sequence it leads to excessive fragmentation of table indexes. Additionally they take up a large amount of space which will cause SQL to have to load more pages into memory in order to read a given table. If you are in a situation where you absolutely have to use a GUID, there are a few methods for calculating sequential GUIDs however this still does not address the space required to store the value. — David, Oct 21 '13 at 16:21

Hamlet Hakobyan · Accepted Answer · 2013-10-21T16:16:00.367

In SQL Server it looks like this:

DECLARE @NewID int

INSERT INTO SpectrometerCalibration (StationID, OperatorID, Date)
VALUES (7, 2, '2013/10/21 11:00:00.000 AM')

SELECT @NewID = SCOPE_IDENTITY()

-- Insert once for each wavelength / responsivity pair
INSERT INTO SpectrometerCalibrationValues (SpectrometerCalibrationID, Wavelength, Responsivity)
VALUES (@NewID , <wl>, <val>)

To achieve same result in LINQ2SQL you can use this:

using (var dbc = new yourDataContext())
{
    var calibration = new SpectrometerCalibration
        {
            StationID = 7,
            OperatorID = 2,
            Date = dateTime.Now
        };
    dbc.calibrations.InsertOnSubmit(calibration );
    dbc.SubmitChanges();

    var clibrationValue = new SpectrometerCalibrationValue
          {
              SpectrometerCalibrationID = calibration.Id,
              Wavelength = 1,
              Responsivity = 1
          };
    dbc.clibrationValues.InsertOnSubmit(clibrationValue);
    dbc.SubmitChanges();
}

I believe this might work, but I'm not in a position to write t-sql. I'm looking for a solution in vb.net using LINQ. I will update my tags - thanks! — djv, Oct 21 '13 at 16:11

score 0 · Answer 2 · answered Oct 21 '13 at 16:16

Linq-to-Sql (which you mentioned you are using) is one of the best ways of doing this.

All you need is to create the objects:

SpectrometerCalibration calibration = new SpectrometerCalibration();
yourDataContext.SpectrometerCalibrations.InsertOnSubmit(calibration);
yourDataContext.SubmitChanges();
// After the line above Linq-to-Sql retrieves the id of the calibration object.

// Then, you could make your values:
SpectrometerCalibrationValue value = new SpectrometerCalibrationValue();
value.SpectrometerCalibrationID = calibration.Id;
// Don't forget to update the calibration data in the value objects.
yourDataContext.SpectrometerCalibrationValues.InsertOnSubmit(value);
yourDataContext.SpectrometerCalibrationValues.InsertOnSubmit(value2);
// Etc ...

yourDataContext.SubmitChanges();

However, I would strongly recommend you to change your schema to have the foreign key on another table.

score 0 · Answer 3 · answered Oct 21 '13 at 16:17

In situations like this one my preference would be to use a stored procedure so there is more control in the database layer over what actually happens, and it allows for the chance to perform more complex logic on the information before it is returned to the application layer.

In this scenario, assuming that Date is not a part of the natural key on the table, the T-SQL logic would be shaped to look something like this.

INSERT INTO SpectrometerCalibration (StationID, OperatorID, Date)
SELECT 
FROM (SELECT @StationID [StationID], @OperatorID [OperatorID], @Date [DateStamp]) T
LEFT JOIN SpectrometerCalibration C ON
    C.StationID = T.StationID
    AND C.OperatorID = T.OperatorID
WHERE C.ID IS NULL

SELECT @SpectrometerCalibrationID = ID
FROM SpectrometerCalibration 
WHERE StationID = @StationID
    AND OperatorID = @OperatorID
    --Not known if Date is part of combination which uniquely identifies a row
    --AND Date = @Date

INSERT INTO SpectrometerCalibrationValues (SpectrometerCalibrationID, Wavelength, Responsivity)
VALUES (@SpectrometerCalibrationID, <S

Store into two tables with primary key simultaneously

3 Answers3