EF6 Syntax error when getting stored procedure results with parameter

2

I don't understand why I'm getting a syntax error when this line of code executes:

return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<Kitti_getProductGroupDetail_Result>("Kitti_getProductGroupDetail @ProductGroupId", @params);

Here is the whole method:

 public virtual ObjectResult<Kitti_getProductGroupDetail_Result> Kitti_getProductGroupDetail(Guid ProductGroupId)
 {
     var @params = new SqlParameter[]
     {
            new SqlParameter("ProductGroupId", ProductGroupId)
     };

     return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<Kitti_getProductGroupDetail_Result>("Kitti_getProductGroupDetail @ProductGroupId", @params);
}

Here is the calling method:

public System.Collections.Generic.IEnumerable<ProductDetail> getProductDetailsFromSP(Guid ProductGroupId)
{
    System.Collections.Generic.List<ProductDetail> productDetailList = new System.Collections.Generic.List<ProductDetail>();

    using (var context = new EFDbContext())
    {
        var result = context.Kitti_getProductGroupDetail(ProductGroupId);

        foreach (Kitti_getProductGroupDetail_Result res in result)
        {
             ProductDetail productDetail = new ProductDetail();
             productDetail.ProductId = res.productid;
             productDetail.SizeId = res.sizeid;
             productDetail.SizeDescription = res.sizedescription;
             productDetail.SizeRank = res.sizerank;
             productDetail.ColorId = res.colorid;
             productDetail.ColorDescription = res.colordescription;
             productDetail.ColorRank = res.colorrank;
             productDetail.QuantityRemainingInStock = res.quantityremaininginstock;
             productDetail.ProductGroupId = res.productgroupid;
             productDetail.ProductGroupName = "ProductGroupName is currently not in the stored procedure or in EF";
             productDetail.Description = res.description;

             productDetailList.Add(productDetail);
         }
     }

     return productDetailList;
 }

The result object:

public partial class Kitti_getProductGroupDetail_Result
{
    public System.Guid productid { get; set; }
    public System.Guid productgroupid { get; set; }
    public string description { get; set; }
    public decimal price { get; set; }
    public Nullable<System.Guid> colorid { get; set; }
    public string colordescription { get; set; }
    public int colorrank { get; set; }
    public Nullable<System.Guid> sizeid { get; set; }
    public string sizedescription { get; set; }
    public int sizerank { get; set; }
    public int quantityremaininginstock { get; set; }
    public string productname { get; set; }
    public string imagesrc { get; set; }
}

Finally here is my stored procedure:

ALTER PROCEDURE [dbo].[Kitti_getProductGroupDetail] 
@ProductGroupId uniqueidentifier



-- Insert statements for procedure here
SELECT product.ProductId as 'productid', product.ProductGroupId as 'productgroupid', product.[Description] as 'description'
, product.Price as 'price', product.ColorId as 'colorid', color.ColorDescription as 'colordescription', color.ColorRank as 'colorrank', 
product.SizeId as 'sizeid', size.SizeDescription as 'sizedescription', size.SizeRank as 'sizerank', product.QuantityRemainingInStock as 'quantityremaininginstock',
product.Name as 'productname', product.ProductImageSrc as 'imagesrc'
FROM dbo.Product product
INNER JOIN dbo.Color color
    on color.ColorId = product.ColorId
INNER JOIN dbo.Size size
    on size.SizeId = product.SizeId
INNER JOIN dbo.ProductGroup productgroup
    on productgroup.ProductGroupId = product.ProductGroupId
WHERE product.ProductGroupId = @ProductGroupId
AND product.QuantityRemainingInStock > 0

ProductGroupId is definitely getting populated. What doesn't EF like about my code? I have a similar method that does not require a parameter to execute the stored procedure - it works fine

 // this works just fine
    public virtual ObjectResult<Kitti_getProductDetailsAndCategory_Result> Kitti_getProductDetailsAndCategory()
    {
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<Kitti_getProductDetailsAndCategory_Result>("Kitti_getProductDetailsAndCategory");

        //ExecuteFunction changed to ExecuteStoreQuery
    }

stack trace:

Incorrect syntax near 'Kitti_getProductGroupDetail'. 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 'Kitti_getProductGroupDetail'.

Source Error:

Line 40: }; Line 41: Line 42: return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery("Kitti_getProductGroupDetail @ProductGroupId", @params); Line 43: } Line 44:

Source File: C:\Concrete\EFDbContext.cs Line: 42

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'Kitti_getProductGroupDetail'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +2444082 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5775560 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) +409 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) +2127 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) +911 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, Object[] parameters) +83 KittiCutieFashion.Domain.Concrete.EFDbContext.Kitti_getProductGroupDetail(Guid ProductGroupId) in C:\KittiCutieFashion\KittiCutieFashion.Domain\Concrete\EFDbContext.cs:42 KittiCutieFashion.Domain.Concrete.EFDbContext.getProductDetailsFromSP(Guid ProductGroupId) in C:\KittiCutieFashion\KittiCutieFashion.Domain\Concrete\EFDbContext.cs:82 KittiCutieFashion.Domain.Concrete.EFProductDetailRepository.get_ProductDetail() in C:\KittiCutieFashion\KittiCutieFashion.Domain\Concrete\EFProductDetailRepository.cs:33 KittiCutieFashion.WebUI.Controllers.ProductDetailController.ProductDetailView(String returnUrl) in C:\KittiCutieFashion\KittiCutieFashion.WebUI\Controllers\ProductDetailController.cs:43 lambda_method(Closure , ControllerBase , Object[] ) +103 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.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.AsyncInvocationWithFilters.b__11_0() +50 System.Web.Mvc.Async.<>c__DisplayClass11_1.b__2() +228 System.Web.Mvc.Async.<>c__DisplayClass7_0.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.b__3() +35 System.Web.Mvc.Async.<>c__DisplayClass3_1.b__5(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.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.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.b__20_1(IAsyncResult asyncResult, ProcessRequestState innerState) +28 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) +29 System.Web.Mvc.Async.WrappedAsyncResultBase1.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() +9748493 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.2558.0

The version of sql server I am using happens to be 2005 not sure if that has anything to do with it.

tsql
sql-server-2005
asp.net-mvc-5
entity-framework-6
asked on Stack Overflow Aug 6, 2018 by drzounds • edited Aug 7, 2018 by drzounds

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0