Calling stored procedure with “FromSqlRaw” command returning an error

0
votes

I have seen this error in similar questions but with this being my first stab at .Net Core I want to make sure that my c# code is not the culprit. The error is coming when the stored procedure is being called via FromSqlRaw command. I verified I am sending the correct values to the FromSqlRaw command but error is complaining about a key?

Please see code below:

Controller

public class HomeController : Controller
{
    private IStudent _info;

    private readonly StudentInformation _studentInformation;

    public HomeController(IStudent info)
    {
        _info = info;
    }

    //public async Task<IActionResult> Index([FromQuery] Student student)
    public IActionResult Index([FromQuery] Student student)
    {
        int id = student.Id;

        if (id == 0) 
        {
            return NotFound(); 
        }

        var model = _info.GetById(id);

        return View(model);
    }
}

Student Class

public class Student
{
    public int Id_Num { get; set; }

    public string StudentName { get; set; }
    public string PreferredName { get; set; }
    public string Gender { get; set; }

    public string DOB { get; set; }
    public string Resident { get; set; }

    public string F1Visa { get; set; }
    public string Division { get; set; }
    public string Tuition { get; set; }
    public string cohort { get; set; }
    public string Ferpa { get; set; }
} 

public class StudentInformationService : IStudent
{
    private StudentInfo _info;

    public StudentInformationService(StudentInfo info)
    {
        _info = info;
    }

    public IEnumerable<Student> GetAll()
    {
        throw new NotImplementedException();
    }

    public Student GetById(int id)
    {
        return _info.StudentRow
                   .FromSqlRaw("StudentRegistrationInfo @IdNum, @OldIdNum", 
                               new SqlParameter("IdNum", id),
                               new SqlParameter("OldIdNum", id))
                   .AsEnumerable().FirstOrDefault();
    }
}

Here is the error:

InvalidOperationException: The entity type 'Student' requires a primary key to be defined. If you intended to use a keyless entity type call 'HasNoKey()'.

Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNonNullPrimaryKeys(IModel model, IDiagnosticsLogger logger)

Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger logger)

Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger logger)

Microsoft.EntityFrameworkCore.SqlServer.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger logger)

Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher+ImmediateConventionScope.OnModelFinalized(IConventionModelBuilder modelBuilder)

Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()

Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder)

Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()

Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor.VisitCallSite(ServiceCallSite callSite, TArgument argument)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor.VisitCallSite(ServiceCallSite callSite, TArgument argument)

Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)

Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)

Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider)

Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()

Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()

Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()

Microsoft.EntityFrameworkCore.DbContext.get_Model()

Microsoft.EntityFrameworkCore.Internal.InternalDbSet.get_EntityType()

Microsoft.EntityFrameworkCore.Internal.InternalDbSet.get_EntityQueryable()

Microsoft.EntityFrameworkCore.Internal.InternalDbSet.System.Linq.IQueryable.get_Provider()

Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSqlRaw(DbSet source, string sql, Object[] parameters)

StudentServices.StudentInformationService.GetById(int id) in StudentInformationService.cs +

  1. return _info.StudentRow.FromSqlRaw("StudentRegistrationInfo @IdNum, @OldIdNum",

StudentChecklist.Controllers.HomeController.Index(Student student) in HomeController.cs +

  1. var model = _info.GetById(id);

lambda_method(Closure , object , Object[] )

Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(object target, Object[] parameters)

Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+d__12.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+d__10.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)

Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)

Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+d__14.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker+d__22.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)

Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)

Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker+d__17.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker+d__15.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

Microsoft.AspNetCore.Builder.RouterMiddleware+d__4.MoveNext()

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware+d__7.MoveNext()

1
c#sqlentity-frameworkasp.net-core-mvc

1 Answers

1
votes

The error is really quite clear - your Student model class doesn't define a primary key for the entity (which it should - always).

By convention, Id or (classname)Id (here: StudentId) would automatically be considered the primary key - since you have neither of these, you need to explicitly annotate the column to be used as primary key - most likely the Id_Num column - with a [Key] annotation - like this:

public class Student
{
    [Key]
    public int Id_Num { get; set; }

    // remainder of your properties
}