I have been on the search for a rather complex set of Dapper code that will allow me to call a stored procedure and have it read 3 cursors. But there's a twist...the first cursor returns a single object that has 7 mapped objects within it (seven one-to-one relationships within the class). The other two cursors return multiple objects (one to many) that the class contains and stores in a collection of objects.
So, I can get the multiple cursors working using QueryMultipe and then calling Read & ReadSingle. That populates the base class without any complex types being populated but it does populate the two lists in the class.
Separately, I can call Query with 8 types in the "types" parameter (since there is a limit of number of types you can map to that exceeds the 7 provided by the Func<> that does the construction).
But...there seems to be no ReadSingle method that accepts more than 7 types to map to and the same change they made to Query (to allow unlimited types to be sent in) didn't get added to the ReadSingle method via this change in their repository...or at least I can't find it or figure it out.
Has anyone come across this limitation and was able to get around it without having to rewrite the Stored Procedure? (Rewriting the stored proc is problematic for this project - long story as to why.)
To clarify, I have a class like the one below that gets populated by one stored procedure call - I'm looking for a Dapper call for this one stored procedure to populate this object all at once:
public class Foo
{
private long simpleTypeA;
private string simpleTypeB;
private Complex1 complexType1; // complexType1 thru 7 get sent in with the 1st cursor as part of joins on tables in the SQL
private Complex2 complexType2;
private Complex3 complexType3;
private Complex4 complexType4;
private Complex5 complexType5;
private Complex6 complexType6;
private Complex7 complexType7;
private List<Complex8> complexType8; // Gets sent in with 2nd cursor
private List<Complex9> complexType9; // Gets sent in with 3rd cursor
// ... property implementation & constructor here
}
I ended up having to split up my SQL stored procedures to return the bulk of the data in one procedure and the cursors for the list of objects (one to many) related in another procedure. It seems this is a lacking quality in Dapper although it could be argued that stored procedures in any database should not return such a complex set of data all at once. That's an argument I can get behind especially since I didn't write these procedures originally. ;)