CODEWITHSUNDEEP
linq
user517406
user517406

Reputation: 13773

return id field when executing SQL in LINQ

I'm trying to execute an SQL insert query using LINQ and returning the ID field. My code does not work, can anybody tell me if my syntax is wrong?

string query = "insert into EnvironmentalReportDepts ";
                query += "(fkDeptID, OrderNumber, fkEnvironmentalReportID) ";
                query += "values(" + tmpDeptReport.fkDeptID + ", " + tmpDeptReport.OrderNumber + ", " + tmpDeptReport.EnvironmentalReport.EnvironmentalReportID + ") ";
                query += "select scope_identity()";

                var newDeptID = _database.ExecuteQuery<int>(query).ToList()[0];

Upvotes: 0

Views: 735

Answers (2)

TynaP
TynaP

Reputation: 29

change query to:

string query = "DECLARE @scope_identity int"; 
query += "insert into EnvironmentalReportDepts ";               
query += "(fkDeptID, OrderNumber, fkEnvironmentalReportID) ";                 
query += "values(" + tmpDeptReport.fkDeptID + ", " + tmpDeptReport.OrderNumber + ", " + tmpDeptReport.EnvironmentalReport.EnvironmentalReportID + ") ";
query += "SET @scope_identity=@@IDENTITY";                 
query += "select @scope_identity";

Upvotes: 0

sgmoore
sgmoore

Reputation: 16067

Try changing

query += "select scope_identity()";

to

query += "; select scope_identity()";

Also, you may have to use 

 _database.ExecuteQuery<decimal>(query).ToList()[0];

Upvotes: 2

Related Questions