SqlQuery return -1 in EF using MVC

Question

I want to run query in EF but when I run this query in SQL SERVER MANAGEMENT THEN return "00002" but when I run in EF, this query return -1. I don't know what is happing any senior can you tell where i am wrong. I also share the screenshot of the query result where you can see the return result. error: "-1". I am sharing the C# function where I fetch the query from the database and call the function using jquery. function working properly but query result is wrong . Why? I don't know. when i use SQLQUERY then it will return whole query as a string like " select RIGHT('00000' + cast(ISNULL(MAX(User_Id),0)+1 as varchar(5)) + '',6)from secUsers " C# public int Maxnumber() { int result = _db.Database.ExecuteSqlCommand( "select RIGHT('00000' + cast(ISNULL(MAX(User_Id),0)+1 as varchar(5)) + '',6)from secUsers" ); return result; } public JsonResult max_UserID() { var mNo= _IAccount.Maxnumber(); return Json(mNo, JsonRequestBehavior.AllowGet); } Jquery function MaxNo() { $.ajax({ type: 'GET' , url: '@Url.Action("max_UserID")' , dataType: 'json' , data: {}, success: function (mNo) { $( "#userid" ).val(mNo); }, error: function (ex) { var r = jQuery.parseJSON(response.responseText); alert( "Message: " + r.Message); alert( "StackTrace: " + r.StackTrace); alert( "ExceptionType: " + r.ExceptionType); } }); return false ; }

Laxmidhar Sahoo · Answer

hi 1 = If returning a single value .Then shoud use ExecuteScalar insted of ExecuteCommand 2=If use ExecuteCommand ,then should use dataset to capture the data. 3= store the pass argument string in a string variable and copy pest it in the sqlserver query editor and run Like string str = "select RIGHT('00000' + cast(ISNULL(MAX(User_Id),0)+1 as varchar(5)) + '',6)from secUsers"; check str value in ssms Thanks and Regards

Jenith Thakkar · Answer

Hi Write your query in this way int result = _db.Database.SqlQuery ("select RIGHT('00000' + cast(ISNULL(MAX(User_Id),0)+1 as varchar(5)) + '',6)from secUsers").FirstOrDefault (); You had use ExecuteSqlCommand Which is return only status of query. so you can use that for insert and update statement but here you want to do some select operation so use SqlQuery If it works then except my answer Thank you