locked
Entity Framework Core no mapping to a relational type can be found for the CLR type 'object[]' RRS feed

  • Question

  • User1374623307 posted

    Good day,

        I am having an issue using the .FromSqlRaw as it it returning an Unhandled Exception: InvalidOperationException: No mapping to a relation type can be found for the CLR type ;object[]'.  While this might sound simple, I am passing the stored procedure string and the parameters to the .FromSqlRaw.

    So the following is what is throwing the error:

    public async Task<List<MyClass>> LoadDataAsync<U>(string storedProcedure, U parameter)
    {
         var optionsBuilder = new DbContextOptionsBuilder<DbContext>().UseSqlServer(Configuration.GetConnectionString("Default"));
         using DbContext dbContext = new DbContext(optionsBuilder.Options))
        {
             List<MyClass> myClass = await dbContext.MyClass.FromSqlRaw(storedProcedure, parameters).ToListAsync();
    
             return myClass;
        }
    }

    Now I am passing the following into that:

    public async Task<IList<MyClass>> GetIdAsync(string name, string normalName, DateTime createdDate)
    {
         var output = await _sql.LoadDataAsync<object>("[dbo].[GetDBId] @p0, @p1, @p2", new [] { name, normalName, createdDate });
         return output;
    }

    I know the stored procedure works, because if I Execute that stored procedure with the same values from SQL Server Management Studio it displays the correct output.  So I know the issue with with the input the stored procedure and the .FromSqlRaw not liking how I pass the information to it.  I had tried _sql.LoadDataAsync<dynamic> before but it too errored out.  In this case I am passing three objects but some of my stored procedures have upwards of 18 parameters, so I need that LoadDataAsync to be able to accept any number of parameters.

    If anyone has a thought on what might help I would appreciate it.

    Sunday, October 11, 2020 6:02 PM

All replies

  • User475983607 posted

    I'm guessing a problem with the parameters.   You should declare the parameters.

    List<SqlParameter> parameters = new List<SqlParameter>
    {
       new SqlParameter("@parmName1", val1),
       new SqlParameter("@parmName2", val2)
    }
    List<MyClass> myClass = await dbContext.MyClass.FromSqlRaw(storedProcedure, parameters.ToArray()).ToListAsync();

    Sunday, October 11, 2020 6:26 PM
  • User1374623307 posted

    I had thought of that, but what I find the problem is that first part.  @paramName.  If I do it before passing down to the LoadDataAsync, I get the error that U does not contain a definition for ToArray and no accessible extension method ToArray accepting a first argument of type  'U' could be found.  Ok, so I tried inside my LoadDataAsync.  I do a for loop and put parameter. in for the length but do not have count as 'U' does not contain a definition for 'Count'.  I changed my LoadDataAsync to be (string storedProcedure, object [] parameters) and now I could for loop and do a @p0, @p1, etc.

    That seems to work so far, but it may lead to other issues.  I guess I will have to see.  Thanks for the input.

    Sunday, October 11, 2020 9:35 PM
  • User475983607 posted

    In my opinion, it makes little sense to use a generic and pass an object[] when the FromSqlRaw accepts an array of SqlParameter. 

    I would fail this in a code review and recommend passing the an array of SqlParameters or use method overloads if needed.  Also, your design forces developers to build the inputs and the output outside of the method.   IMHO, this make the code confusing.  I'd rather have a method named GetUserById with known input and output parameters. 

    Monday, October 12, 2020 11:54 AM