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.
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.
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.
Member
19 Points
90 Posts
Entity Framework Core no mapping to a relational type can be found for the CLR type 'object[]'
Oct 11, 2020 06:02 PM|MikeRM2|LINK
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.
All-Star
50691 Points
22462 Posts
Re: Entity Framework Core no mapping to a relational type can be found for the CLR type 'object[]...
Oct 11, 2020 06:26 PM|mgebhard|LINK
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) }Member
19 Points
90 Posts
Re: Entity Framework Core no mapping to a relational type can be found for the CLR type 'object[]...
Oct 11, 2020 09:35 PM|MikeRM2|LINK
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.
All-Star
50691 Points
22462 Posts
Re: Entity Framework Core no mapping to a relational type can be found for the CLR type 'object[]...
Oct 12, 2020 11:54 AM|mgebhard|LINK
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.