ASP.NET MVC viewmodel SQL query syntax

0

I'm trying to convert basic models to model vİew and SQL queries. So what is wrong my queries or other files?

// GET: Cars
public ActionResult Index()
{
    List<CarsTrxViewModel> model = db.Database.SqlQuery<CarsTrxViewModel>(SqlCars.CarsTrxByID()).ToList();

    return View(model);
    // return View(db.Cars.ToList());
}

View model:

public class CarsTrxViewModel
{
    public int CarID { get; set; }
    public string CarBrand { get; set; }
    public string CarModel { get; set; }

    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:dd/MM/yyyy HH:mm:ss}")]
    public DateTime CreateDate { get; set; }
}

Query:

public static class SqlCars
{
    public static string CarsTrxByID()
    {
        return "select " + " * , " +
             "cars.CarID as CarID " +
            ",cars.CarBrand as CarBrand " +
            ",cars.CarModel as CarModel " +
            ",cars.CreateDate as CreateDate " +
            "from (SELECT * from [NagisOfflineDB].[dbo].[cars] )";
    }

    public static string GetCardholderByID()
    {
        return "SELECT TOP 1 * from [NagisOfflineDB].[dbo].[cars]";
    }
}

Model:

public class Cars
{
    [Key]
    public int CarID { get; set; }

    [StringLength(50)]
    [Required]
    public string CarBrand { get; set; }

    [StringLength(250)]
    [Required]
    public string CarModel { get; set; }

    public DateTime CreateDate { get; set; }
}

I'm not join or other operations I'm just select one column in one table and set alias rows so what is wrong my query?

Compiler say your query wrong its error System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

Full version of error

Server Error in '/' Application.
Incorrect syntax near ')'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Source Error:

Line 20:
Line 21:
Line 22: List model = db.Database.SqlQuery(SqlCars.CarsTrxByID()).ToList();

And the stack trace

[SqlException (0x80131904): Incorrect syntax near ')'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +2552942
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) +5952492
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4169 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58 System.Data.SqlClient.SqlDataReader.get_MetaData() +89 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +430 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2598 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +1483 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +64 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +240 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext1 c) +14 System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) +72 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +402 System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +166 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12 System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal(String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) +266 System.Data.Entity.Core.Objects.<>c__DisplayClass691.<ExecuteStoreQueryReliably>b__68() +44 System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +288 System.Data.Entity.Core.Objects.<>c__DisplayClass691.<ExecuteStoreQueryReliably>b__67() +167 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func1 operation) +190 System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably(String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) +462 System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery(String commandText, ExecutionOptions executionOptions, Object[] parameters) +60 System.Data.Entity.Internal.<>c__DisplayClass141.<ExecuteSqlQuery>b__13() +130 System.Data.Entity.Internal.LazyEnumerator1.MoveNext() +45 System.Collections.Generic.List1..ctor(IEnumerable1 collection) +387 System.Linq.Enumerable.ToList(IEnumerable1 source) +58 NgsOfflineDev.Controllers.CarsController.Index() in C:\Dev\NagisDevOffline\NgsOfflineDev\NgsOfflineDev\Controllers\CarsController.cs:22 lambda_method(Closure , ControllerBase , Object[] ) +61 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +157 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +27 System.Web.Mvc.Async.<>c.<BeginInvokeSynchronousActionMethod>b__9_0(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +22 System.Web.Mvc.Async.WrappedAsyncResult2.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32 System.Web.Mvc.Async.<>c__DisplayClass11_0.<InvokeActionMethodFilterAsynchronouslyRecursive>b__0() +58 System.Web.Mvc.Async.<>c__DisplayClass11_2.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2() +228 System.Web.Mvc.Async.<>c__DisplayClass7_0.<BeginInvokeActionMethodWithFilters>b__1(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResult1.CallEndDelegate(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34 System.Web.Mvc.Async.<>c__DisplayClass3_6.<BeginInvokeAction>b__4() +35 System.Web.Mvc.Async.<>c__DisplayClass3_1.<BeginInvokeAction>b__1(IAsyncResult asyncResult) +100 System.Web.Mvc.Async.WrappedAsyncResult1.CallEndDelegate(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27 System.Web.Mvc.<>c.<BeginExecuteCore>b__152_1(IAsyncResult asyncResult, ExecuteCoreState innerState) +11 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49 System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +45 System.Web.Mvc.<>c.<BeginExecute>b__151_2(IAsyncResult asyncResult, Controller controller) +13 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +22 System.Web.Mvc.Async.WrappedAsyncResultBase1.End() +49 System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26 System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10 System.Web.Mvc.<>c.<BeginProcessRequest>b__20_1(IAsyncResult asyncResult, ProcessRequestState innerState) +28 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9874329 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159

Version Information:

  • Microsoft .NET Framework Version: 4.0.30319
  • ASP.NET Version: 4.7.3062.0
c#
sql-server
asp.net-mvc
action
viewmodel
asked on Stack Overflow Jan 16, 2020 by Mehmet Başaran • edited Jan 17, 2020 by marc_s

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0