EF Core 进行 SQL 数据库 MERGE 调用并获取新插入记录以及标识列的输出

发布于 2025-01-17 22:51:02 字数 1885 浏览 0 评论 0原文

我们正在使用EF Core进行数据库调用，并且有一个合并调用以有条件地插入记录，并且一旦插入记录，我们想要在输出（以及标识列）中获取新记录。我正在这样做，请避免其他选择查询帖子。以下是我们的代码，关于如何获得新记录的输出以及标识列（自动生成的值）的任何指南：

 string query = $"MERGE INTO [ReceiptMaster] AS old " +
                    $"USING (VALUES ({receipt.ProfileID},'{receipt.TranscribeID}','{receipt.ReceiptStatusID}'," +
                    $"'{receipt.ReceiptTypeID}','{receipt.TransactionDate}','{receipt.ProcessTypeID}'," +
                    $"'{receipt.TripTranscriptionVendorID}','{receipt.AcquireTypeID}',{receipt.CategoryTypeID}," +
                    $"'{receipt.IsResubmitted}','{receipt.IsResubmittedByPanel}', {receipt.ScrapeJobID}, " +
                    $"'{receipt.TripTranscriptionPayloadID}')) " +
                    $"AS new (ProfileID, TranscribeID, ReceiptStatusID, ReceiptTypeID, TransactionDate, ProcessTypeID, " +
                    $"TripTranscriptionVendorID, AcquireTypeID, CategoryTypeID, IsResubmitted, IsResubmittedByPanel, " +
                    $"ScrapeJobID, TripTranscriptionPayloadID) " +
                    $"ON new.TranscribeID = old.TranscribeID " +
                    $"WHEN NOT MATCHED BY TARGET THEN INSERT (ProfileID, TranscribeID, ReceiptStatusID, ReceiptTypeID, " +
                    $"TransactionDate, ProcessTypeID, TripTranscriptionVendorID, AcquireTypeID, CategoryTypeID, IsResubmitted, " +
                    $"IsResubmittedByPanel, ScrapeJobID, TripTranscriptionPayloadID) VALUES (ProfileID, TranscribeID, ReceiptStatusID, " +
                    $"ReceiptTypeID, TransactionDate, ProcessTypeID, TripTranscriptionVendorID, AcquireTypeID, CategoryTypeID, " +
                    $"IsResubmitted, IsResubmittedByPanel, ScrapeJobID, TripTranscriptionPayloadID);";

            var context = _dbContext.Database.ExecuteSqlRaw(query);

            if (context == 0) return false;
            else return true;
        }

原文

We are using EF Core to make database call and we have a MERGE call to conditionally insert a record and what we want is once the record is inserted we want to get that new RECORD in the OUTPUT (along with the identify column). I am doing this avoid additional Select query post the insert. Following is our code, any guidance on how we can get the OUTPUT of the new record along with the identify column (auto generated value):

 string query = quot;MERGE INTO [ReceiptMaster] AS old " +
                    quot;USING (VALUES ({receipt.ProfileID},'{receipt.TranscribeID}','{receipt.ReceiptStatusID}'," +
                    quot;'{receipt.ReceiptTypeID}','{receipt.TransactionDate}','{receipt.ProcessTypeID}'," +
                    quot;'{receipt.TripTranscriptionVendorID}','{receipt.AcquireTypeID}',{receipt.CategoryTypeID}," +
                    quot;'{receipt.IsResubmitted}','{receipt.IsResubmittedByPanel}', {receipt.ScrapeJobID}, " +
                    quot;'{receipt.TripTranscriptionPayloadID}')) " +
                    quot;AS new (ProfileID, TranscribeID, ReceiptStatusID, ReceiptTypeID, TransactionDate, ProcessTypeID, " +
                    quot;TripTranscriptionVendorID, AcquireTypeID, CategoryTypeID, IsResubmitted, IsResubmittedByPanel, " +
                    quot;ScrapeJobID, TripTranscriptionPayloadID) " +
                    quot;ON new.TranscribeID = old.TranscribeID " +
                    quot;WHEN NOT MATCHED BY TARGET THEN INSERT (ProfileID, TranscribeID, ReceiptStatusID, ReceiptTypeID, " +
                    quot;TransactionDate, ProcessTypeID, TripTranscriptionVendorID, AcquireTypeID, CategoryTypeID, IsResubmitted, " +
                    quot;IsResubmittedByPanel, ScrapeJobID, TripTranscriptionPayloadID) VALUES (ProfileID, TranscribeID, ReceiptStatusID, " +
                    quot;ReceiptTypeID, TransactionDate, ProcessTypeID, TripTranscriptionVendorID, AcquireTypeID, CategoryTypeID, " +
                    quot;IsResubmitted, IsResubmittedByPanel, ScrapeJobID, TripTranscriptionPayloadID);";

            var context = _dbContext.Database.ExecuteSqlRaw(query);

            if (context == 0) return false;
            else return true;
        }