Dapper - The member of type <>f__AnonymousType.....cannot be used as a parameter value

Question

I am trying to execute a Stored Procedure which returns a return value.

But I am getting the error message:

2021-06-23 14:02:37.844 +02:00 [ERR] The member of type <>f__AnonymousType0`3[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] cannot be used as a parameter value

List<Person2> persons = new List<Person2>()
            {
                new Person2() { Id = 22, FirstName = "Calvin", LastName = "Kattar" },
                new Person2() { Id = 23, FirstName = "Edson", LastName = "Barboza" }
            };
            
            using (IDbConnection connection = new OracleConnection(GetConnectionString()))
            {
                try
                {
                    var personProjections = persons.Select(x => new  { id = x.Id, first_name = x.FirstName, last_name = x.LastName, out_message = x.OutMessage});

                    var results = connection.Query<Person2>("kwc_test_person_procedure2",
                    new { personProjections },
                    commandType: CommandType.StoredProcedure
                    );                   
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message);
                    Log.Error(ex.StackTrace);
                    throw;
                }
            }

And my types are:

public class Person
    {
        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }
    }

public class Person2 : Person 
    {
        public string OutMessage { get; set; }
    }

And the stored procedure:

create or replace procedure kwc_test_person_procedure2(
id in number,
first_name in varchar2,
last_name in varchar2,
out_message out varchar2
) is

begin
  insert into kwc_test_person(id, first_name, last_name) values (id, upper(first_name), upper(last_name));
  out_message := 'The person-ID ' || id || ' has been inserted to database on ' || sysdate;

End kwc_test_person_procedure2;

What am I doing wrong here?

Thanks in advance!

Executing a procedure for every item is going to be slow. You probably want a Table Parameter (not sure how you do that in Oracle, I think you need a UDT), then you pass it in Dapper via .AsTableValuedParameter. — Charlieface
@Charlieface, thank you for the suggestion. I am planning to use Parallel executions. And the program will run during the night, so slowness shouldn't be an issue. — KWC

Stefan Stefan · Accepted Answer · 2021-06-23T14:16:40

The parameter param of Query(...) has the wrong type. It needs to be a flat object which has a property or field for each of the parameters of the procedure you are calling. Something like this would work:

connection.Query("kwc_test_person_procedure2",
new { id = 1, first_name = "...", ...},
commandType: CommandType.StoredProcedure

Since you want to call the procedure for multiple person instances your code should look something like this:

 foreach(var personProjection in persons.Select(x => new  { id = x.Id, first_name = x.FirstName, last_name = x.LastName, out_message = x.OutMessage})
 {
                connection.Query("kwc_test_person_procedure2",
                personProjection,
                commandType: CommandType.StoredProcedure);     
 }

As for the return value of the procedure, I am not sure if that is how it works in Oracle; in MSSQL the return value would be the result of the last select in the procedure. It looks like it might be the same in oracle, see this.

Dapper - The member of type <>f__AnonymousType.....cannot be used as a parameter value

1 Answers