How to get inserted record ID when using LINQ to execute SQL insert statement

Data and Databases
1

I need help with getting the ID of a newly inserted record when running an SQL insert command through LINQ. I’m trying to insert data and then retrieve the auto-generated ID value, but my current approach isn’t working properly. Here’s what I’m attempting:

string sqlCommand = "insert into CompanyDivisions ";
sqlCommand += "(departmentID, Priority, companyReportID) ";
sqlCommand += "values(" + divisionData.departmentID + ", " + divisionData.Priority + ", " + divisionData.CompanyReport.ReportID + ") ";
sqlCommand += "select scope_identity()";

var insertedID = _dbContext.ExecuteQuery<int>(sqlCommand).ToList()[0];

Can someone point out what might be wrong with this syntax? I expected this to return the ID of the newly created record, but it’s not behaving as expected. Is there a better way to handle this scenario?

2

interesting approach! what happens when you run that code? are you getting an exception or just null values? have you thought about using ExecuteScalar instead of ExecuteQuery? since you’re expecting one value back, it might work better.

3

Your problem is with how you’re running the SQL. You’re concatenating SCOPE_IDENTITY() to your INSERT, so the query returns a result set instead of running as separate operations. Use ExecuteScalar<int>() instead of ExecuteQuery<int>() - SCOPE_IDENTITY() just returns one value. Also, you’re opening yourself up to SQL injection by concatenating strings like that. Use parameterized queries instead. Better yet, just use Entity Framework’s normal insert methods with SaveChanges() - it’ll automatically fill in the ID property after the insert.