I am trying to bind my dapper queries to some models I have laid out. 4 of my objects use a User model as a type with and fields INTERNAL_USER_NUMBER, FIRST_NAME, LAST_NAME etc. However in SQL Server, you cannot have a query with identical columns.
So in my Dapper multimap function I am not able to split on INTERNAL_USER_NUMBER 4 times to bind my data. Not sure how to solve this:
var certificationReviews2 = await connection.QueryAsync<ARMS_API.Models.CertificationReview>("select * from dbo.A2F_DMCP_0005_BrowseTest()",
types: new[] {
typeof(ARMS_API.Models.CertificationReview),
typeof(State),
typeof(ARMS_API.Models.Program),
typeof(ActuarialFirm),
typeof(User),
typeof(User),
typeof(User) ,
typeof(User)
},
map: (objects) => {
var certificationReview = (ARMS_API.Models.CertificationReview)objects[0];
certificationReview.STATE = (State)objects[1];
certificationReview.STATE.PROGRAMS.Add((ARMS_API.Models.Program)objects[2]);
certificationReview.ACTUARIAL_FIRM = (ActuarialFirm)objects[3];
certificationReview.ACTUARIAL_FIRM.CERTIFYING_ACTUARY.Add((User)objects[4]);
certificationReview.PRIMARY_REVIEWER = (User)objects[5];
certificationReview.SECONDARY_REVIEWER = (User)objects[6];
certificationReview.DMCP_ANALYST = (User)objects[7];
return certificationReview;
},
splitOn: "STATE_ID,PROGRAM_DD,ACTUARIAL_FIRM_DD,INTERNAL_USER_NUMBER,INTERNAL_USER_NUMBER,INTERNAL_USER_NUMBER,INTERNAL_USER_NUMBER");
Updated coding (4/2/24) :
var certificationReviews = await connection.QueryAsync<ARMS_API.Models.CertificationReview>("select * from dbo.A2F_DMCP_0005_BrowseTest()",
types: new[] {
typeof(ARMS_API.Models.CertificationReview),
typeof(State),
typeof(ARMS_API.Models.Program),
typeof(ActuarialFirm),
typeof(object),
typeof(object),
typeof(object) ,
typeof(object)
},
map: (objects) => {
var certificationReview = (ARMS_API.Models.CertificationReview)objects[0];
certificationReview.STATE = (State)objects[1];
certificationReview.STATE.PROGRAMS.Add((ARMS_API.Models.Program)objects[2]);
certificationReview.ACTUARIAL_FIRM = (ActuarialFirm)objects[3];
certificationReview.ACTUARIAL_FIRM.CERTIFYING_ACTUARY.Add(new User {
INTERNAL_USER_NUMBER = (int?)(object)objects[4].GetType().GetProperty("CERTIFYING_ACTUARY_ID"),
FIRST_NAME = (string?)(Object)objects[4].GetType().GetProperty("CERTIFYING_ACTUARY_FIRST_NAME"),
LAST_NAME = (string?)(Object)objects[4].GetType().GetProperty("CERTIFYING_ACTUARY_LAST_NAME")});
certificationReview.PRIMARY_REVIEWER = new User {
INTERNAL_USER_NUMBER = (int?)(object)objects[4].GetType().GetProperty("PRIMARY_ACTUARY_ID"),
FIRST_NAME = (string?)(Object)objects[4].GetType().GetProperty("PRIMARY_ACTUARY_FIRST_NAME"),
LAST_NAME = (string?)(Object)objects[4].GetType().GetProperty("PRIMARY_ACTUARY_LAST_NAME")};
certificationReview.SECONDARY_REVIEWER = new User {
INTERNAL_USER_NUMBER = (int?)(object)objects[4].GetType().GetProperty("SECONDARY_ACTUARY_ID"),
FIRST_NAME = (string?)(Object)objects[4].GetType().GetProperty("SECONDARY_ACTUARY_FIRST_NAME"),
LAST_NAME = (string?)(Object)objects[4].GetType().GetProperty("SECONDARY_ACTUARY_LAST_NAME")};
certificationReview.DMCP_ANALYST = new User {
INTERNAL_USER_NUMBER = (int?)(object)objects[4].GetType().GetProperty("DMCP_ANALYST_ID"),
FIRST_NAME = (string?)(Object)objects[4].GetType().GetProperty("DMCP_ANALYST_FIRST_NAME"),
LAST_NAME = (string?)(Object)objects[4].GetType().GetProperty("DMCP_ANALYST_LAST_NAME")};
return certificationReview;
},
splitOn: "STATE_ID,PROGRAM_DD,ACTUARIAL_FIRM_DD,CERTIFYING_ACTUARY_ID,PRIMARY_ACTUARY_ID,SECONDARY_ACTUARY_ID,DMCP_ANALYST_ID");
return (IEnumerable<CertificationReview>)certificationReviews;
}
}
This still is not binding the user data. I have the spliton set correctly. I manually map but use object instead of dynamic and type cast it to the specified value. Still null/not mapping.
You're facing a problem with Dapper's QueryAsync method when trying to bind data from a SQL Server query to your models. Your query involves multiple instances of a User model, SQL Server does not allow for queries to return multiple columns with the same name, so you cannot use the same splitOn parameter (e.g., INTERNAL_USER_NUMBER) multiple times for different User objects.
To resolve this issue you could use SQL column aliases to ensure that each instance of similar data (e.g., each User model's INTERNAL_USER_NUMBER, FIRST_NAME, LAST_NAME, etc.) has a unique name in the result set. This involves changing your SQL query so that each field related to different User objects has a distinct alias. For example, prefixing or suffixing the column names with something indicative of their role or position (e.g., CertifyingActuary_INTERNAL_USER_NUMBER, PrimaryReviewer_INTERNAL_USER_NUMBER, etc.).In your Dapper QueryAsync call, use the splitOn parameter to specify the unique alias for the first column of each set of data that you want to map to a different object. This tells Dapper exactly where to split the data for mapping to your models. The splitOn parameter should match the first aliased column name for each object set coming from your modified SQL query.
sql query:
SELECT
cr.*, -- this fetches CertificationReview fields
s.STATE_ID, s.OtherStateFields, -- State fields with aliases if needed
p.PROGRAM_DD, p.OtherProgramFields, -- Program fields with aliases if needed
af.ACTUARIAL_FIRM_DD, af.OtherActuarialFirmFields, -- ActuarialFirm fields
ca.INTERNAL_USER_NUMBER as CertifyingActuary_USER_NUMBER, ca.FIRST_NAME as CertifyingActuary_FIRST_NAME, -- etc
pr.INTERNAL_USER_NUMBER as PrimaryReviewer_USER_NUMBER, pr.FIRST_NAME as PrimaryReviewer_FIRST_NAME, -- etc
sr.INTERNAL_USER_NUMBER as SecondaryReviewer_USER_NUMBER, sr.FIRST_NAME as SecondaryReviewer_FIRST_NAME, -- etc
da.INTERNAL_USER_NUMBER as DmcpAnalyst_USER_NUMBER, da.FIRST_NAME as DmcpAnalyst_FIRST_NAME -- etc
FROM dbo.A2F_DMCP_0005_BrowseTest() -- Assuming this is your complex query or view
For the splitOn parameter, you now specify the first aliased column of each object to split on. just make sure these names match exactly what you've aliased them to in your SQL query.
var certificationReviews2 = await connection.QueryAsync<ARMS_API.Models.CertificationReview, State, ARMS_API.Models.Program, ActuarialFirm, dynamic, dynamic, dynamic, dynamic, ARMS_API.Models.CertificationReview>(
"your_modified_sql_here",
(certificationReview, state, program, actuarialFirm, certifyingActuaryDynamic, primaryReviewerDynamic, secondaryReviewerDynamic, dmcpAnalystDynamic) => {
// Map State, Program, ActuarialFirm as before
certificationReview.STATE = state;
certificationReview.STATE.PROGRAMS.Add(program);
certificationReview.ACTUARIAL_FIRM = actuarialFirm;
// Manually map each User, converting 'dynamic' to 'User' and assigning aliased fields to properties
certificationReview.ACTUARIAL_FIRM.CERTIFYING_ACTUARY.Add(new User {
INTERNAL_USER_NUMBER = certifyingActuaryDynamic.CertifyingActuary_USER_NUMBER,
FIRST_NAME = certifyingActuaryDynamic.CertifyingActuary_FIRST_NAME,
// map other properties similarly
});
// Repeat for primaryReviewer, secondaryReviewer, and dmcpAnalyst
return certificationReview;
},
splitOn: "STATE_ID,PROGRAM_DD,ACTUARIAL_FIRM_DD,CertifyingActuary_USER_NUMBER,PrimaryReviewer_USER_NUMBER,SecondaryReviewer_USER_NUMBER,DmcpAnalyst_USER_NUMBER"
).ConfigureAwait(false);