C
C#•15mo ago
ch3rry

✅ (Solved) (Dapper) how to make a single query with one-to-many objects?

connection.QueryAsync<Request, Patient, Registrant, Request>
connection.QueryAsync<Request, Patient, Registrant, Request>
i don't think QuerySingleAsync (or QuerySingleOrDefaultAsync) accepts more than 1 template object, or am i missing something here?
36 Replies
Hazel 🌊💃
Hazel 🌊💃•15mo ago
You can use mapping
ch3rry
ch3rryOP•15mo ago
i'm already doing so?
Hazel 🌊💃
Hazel 🌊💃•15mo ago
https://stackoverflow.com/questions/40703015/custom-mapping-in-dapper
Stack Overflow
Custom mapping in Dapper
I'm attempting to use a CTE with Dapper and multi-mapping to get paged results. I'm hitting an inconvenience with duplicate columns; the CTE is preventing me from having to Name columns for example...
ch3rry
ch3rryOP•15mo ago
yeah i am, problem is it only works (i think) with multiple rows
Hazel 🌊💃
Hazel 🌊💃•15mo ago
You're saying you already have a mapping in place and you just want a single object back? Like a single record
ch3rry
ch3rryOP•15mo ago
yeah pretty much 
Error    CS1061    'MySqlConnection' does not contain a definition for 'QuerySingleOrDefaultAsync' and no accessible extension method 'QuerySingleOrDefaultAsync' accepting a first argument of type 'MySqlConnection' could be found (are you missing a using directive or an assembly reference?)
Error    CS1061    'MySqlConnection' does not contain a definition for 'QuerySingleOrDefaultAsync' and no accessible extension method 'QuerySingleOrDefaultAsync' accepting a first argument of type 'MySqlConnection' could be found (are you missing a using directive or an assembly reference?)
Hazel 🌊💃
Hazel 🌊💃•15mo ago
To my knowledge, it doesn't exist. You'd have to manually grab the single You could always create your own extension for MySqlConnection though if you want to hide that away
ch3rry
ch3rryOP•15mo ago
can i get around it? like doing a normal query but returning the first element i'm having trouble with async/ienumerable, i can't get a function to return [0]
Hazel 🌊💃
Hazel 🌊💃•15mo ago
It's just returning Task<IEnumerable<Request>>, so 1 sec
ch3rry
ch3rryOP•15mo ago
is connection.Query a blocking function?
Hazel 🌊💃
Hazel 🌊💃•15mo ago
Yeah But you can get around that
ch3rry
ch3rryOP•15mo ago
ah so it's fine then, i don't use tasks properly i always await i'm working on a multi-threaded application so it doesn't matter
Hazel 🌊💃
Hazel 🌊💃•15mo ago
I'd just do something like: 
Task<IEnumerable<Request>> queryTask = connection.QueryAsync<Request, Patient, Registrant, Request>(...);
...
IEnumerable<Request> queryResults = await queryTask;
var single = queryResults.SingleOrDefault();
Task<IEnumerable<Request>> queryTask = connection.QueryAsync<Request, Patient, Registrant, Request>(...);
...
IEnumerable<Request> queryResults = await queryTask;
var single = queryResults.SingleOrDefault();
Personally If you don't need the record right away Otherwise you have to await upfront.
ch3rry
ch3rryOP•15mo ago
yeah i do, hence i await every db query so if it's a blocking function and i don't need to map it (.then) i'm cool with it but let me try your snippet
Hazel 🌊💃
Hazel 🌊💃•15mo ago
Well, if you need the record up front, do this instead: 
IEnumerable<Request> queryResults = await connection.QueryAsync<Request, Patient, Registrant, Request>(...);
Request request = queryResults.SingleOrDefault();
IEnumerable<Request> queryResults = await connection.QueryAsync<Request, Patient, Registrant, Request>(...);
Request request = queryResults.SingleOrDefault();
The first snippet I sent is great for when you have some work you can do before you absolutely need the result. You can also wrap a blocking call to consume it asynchronously by just creating a method that returns a task: 
public Task<Result> GetRequest(...) {
    IEnumerable<Request> queryResults = connection.Query<Request, Patient, Registrant, Request>(...);
    Request request = queryResults.SingleOrDefault();
    return Task.FromResult(request);
}
public Task<Result> GetRequest(...) {
    IEnumerable<Request> queryResults = connection.Query<Request, Patient, Registrant, Request>(...);
    Request request = queryResults.SingleOrDefault();
    return Task.FromResult(request);
}
Something like that Then await that instead If you find the async versions aren't working for your needs but the sync versions do
ch3rry
ch3rryOP•15mo ago
yeahhh this is exactly what i'm tryna do ^ let me see 
        public static Task<Request> GetRequest(int ID)
        {
            var result = DBHandler.connection.QueryAsync<Request, Patient, Registrant, Request>("SELECT * FROM Requests INNER JOIN Patients ON Patients.PersonalID = Requests.PatientID LEFT JOIN Users ON Users.ID = Requests.RegistrantID WHERE Requests.ID = @ID;",
                (Request request, Patient patient, Registrant registrant) =>
                {
                    request.Patient = patient;
                    request.Registrant = registrant;
                    return request;
                }, splitOn: "PersonalID,ID",
                param: 
                new
                {
                    ID = ID
                });
            
            // ...
        }
        public static Task<Request> GetRequest(int ID)
        {
            var result = DBHandler.connection.QueryAsync<Request, Patient, Registrant, Request>("SELECT * FROM Requests INNER JOIN Patients ON Patients.PersonalID = Requests.PatientID LEFT JOIN Users ON Users.ID = Requests.RegistrantID WHERE Requests.ID = @ID;",
                (Request request, Patient patient, Registrant registrant) =>
                {
                    request.Patient = patient;
                    request.Registrant = registrant;
                    return request;
                }, splitOn: "PersonalID,ID",
                param: 
                new
                {
                    ID = ID
                });
            
            // ...
        }
so in the end, how can i output the first result? i'm sorry if i'm being unclear but this is my first time using dapper lol
Hazel 🌊💃
Hazel 🌊💃•15mo ago
result is just Task<IEnumerable<Request>> right now.
ch3rry
ch3rryOP•15mo ago
yea
Hazel 🌊💃
Hazel 🌊💃•15mo ago
Since you're in a method that can be marked as async, do that, and await before you get the result. That'll turn result into IEnumerable<Request>. Then you can return result?.SingleOrDefault() 
        public static async Task<Request> GetRequest(int ID)
        {
            var result = await DBHandler.connection.QueryAsync<Request, Patient, Registrant, Request>("SELECT * FROM Requests INNER JOIN Patients ON Patients.PersonalID = Requests.PatientID LEFT JOIN Users ON Users.ID = Requests.RegistrantID WHERE Requests.ID = @ID;",
                (Request request, Patient patient, Registrant registrant) =>
                {
                    request.Patient = patient;
                    request.Registrant = registrant;
                    return request;
                }, splitOn: "PersonalID,ID",
                param: 
                new
                {
                    ID = ID
                });
            
            return result?.SingleOrDefault();
        }
        public static async Task<Request> GetRequest(int ID)
        {
            var result = await DBHandler.connection.QueryAsync<Request, Patient, Registrant, Request>("SELECT * FROM Requests INNER JOIN Patients ON Patients.PersonalID = Requests.PatientID LEFT JOIN Users ON Users.ID = Requests.RegistrantID WHERE Requests.ID = @ID;",
                (Request request, Patient patient, Registrant registrant) =>
                {
                    request.Patient = patient;
                    request.Registrant = registrant;
                    return request;
                }, splitOn: "PersonalID,ID",
                param: 
                new
                {
                    ID = ID
                });
            
            return result?.SingleOrDefault();
        }
ch3rry
ch3rryOP•15mo ago
yeah, but is there any way to return it as a task? thinking again, i probably dont wanna await in the function where i wanna await tho is in function calls, e.g when retreiving the requests
Hazel 🌊💃
Hazel 🌊💃•15mo ago
var request = await GetRequest(ID: 1);
var request = await GetRequest(ID: 1);
Correct? You want to await the full invocation?
ch3rry
ch3rryOP•15mo ago
yep exactly
Hazel 🌊💃
Hazel 🌊💃•15mo ago
You're still fine 1 sec I'll set up an example right here in eval so you can see it in action
ch3rry
ch3rryOP•15mo ago
it won't create unnecessary blocking right?
Hazel 🌊💃
Hazel 🌊💃•15mo ago
Inside of GetRequest you have to wait on the result to come back at some point. Considering there's no work between the execution of the query and when you return the result in GetRequest there's no reason to do it the hard way, just use await. If you're worried about blocking the UI, then append .ConfigureAwait(false) to the end of your query async invocation. From a consumption of GetRequest perspective If you want to start the query but have other work to do, you assign the task to start it and await it when you need it The difference: 
// waits for completion now:
var request = await GetRequest(ID: 1);

// waits for completion later:
var getTask = GetRequest(ID: 1);
DoSomething();
var request = await getTask;
UseRequest(request);
// waits for completion now:
var request = await GetRequest(ID: 1);

// waits for completion later:
var getTask = GetRequest(ID: 1);
DoSomething();
var request = await getTask;
UseRequest(request);
An eval might help
ch3rry
ch3rryOP•15mo ago
yeah i think i'll figure that on my own i feel like im wasting your time thanks a lot for your answers blobthumbsup
MODiX
MODiX•15mo ago
Hazel | へいぜる
REPL Result: Success
public Task<string> GetStringFromDb(string input) => Task.FromResult(input);
public async Task<string> GetString(string input) => await GetStringFromDb(input);

string foo = await GetString("foo");
Console.WriteLine(foo);

Task<string> getBarString = GetString("bar");
Console.WriteLine("Waiting for bar.");

string bar = await getBarString;
Console.WriteLine(bar);
public Task<string> GetStringFromDb(string input) => Task.FromResult(input);
public async Task<string> GetString(string input) => await GetStringFromDb(input);

string foo = await GetString("foo");
Console.WriteLine(foo);

Task<string> getBarString = GetString("bar");
Console.WriteLine("Waiting for bar.");

string bar = await getBarString;
Console.WriteLine(bar);
Console Output
foo
Waiting for bar.
bar
foo
Waiting for bar.
bar
Compile: 639.555ms | Execution: 109.927ms | React with ❌ to remove this embed.
Hazel 🌊💃
Hazel 🌊💃•15mo ago
Not wasting my time, I'm just listening in on a meeting 🙂 hopefully that eval helps a bit Actually, a better example 1 sec
ch3rry
ch3rryOP•15mo ago
well as long as the inside of GetRequest(ID: 1) doesn't block the program because of that await DBHandler.connection.QueryAsync it's good. if it blocks when calling it (await GetRequest(ID: 1)) it doesn't matter because i'm calling the function in another thread anyway
MODiX
MODiX•15mo ago
Hazel | へいぜる
REPL Result: Success
public static void Print(string message) => Console.WriteLine($"{DateTime.Now}: {message}");
public async Task<string> GetStringFromDb(string input) {
    Print($"Wating for `{input}`.");
    await Task.Delay(3000);
    return input;
}

public async Task<string> GetString(string input) {
    Print($"Getting `{input}` from database.");
    return await GetStringFromDb(input);
}

string foo = await GetString("foo");
Print("Waiting for foo to finish.");
Print(foo);

Task<string> getBarString = GetString("bar");
Print("Waiting for bar to finish.");

string bar = await getBarString;
Print(bar);
public static void Print(string message) => Console.WriteLine($"{DateTime.Now}: {message}");
public async Task<string> GetStringFromDb(string input) {
    Print($"Wating for `{input}`.");
    await Task.Delay(3000);
    return input;
}

public async Task<string> GetString(string input) {
    Print($"Getting `{input}` from database.");
    return await GetStringFromDb(input);
}

string foo = await GetString("foo");
Print("Waiting for foo to finish.");
Print(foo);

Task<string> getBarString = GetString("bar");
Print("Waiting for bar to finish.");

string bar = await getBarString;
Print(bar);
Console Output
09/12/2023 18:48:24: Getting `foo` from database.
09/12/2023 18:48:24: Wating for `foo`.
09/12/2023 18:48:27: Waiting for foo to finish.
09/12/2023 18:48:27: foo
09/12/2023 18:48:27: Getting `bar` from database.
09/12/2023 18:48:27: Wating for `bar`.
09/12/2023 18:48:27: Waiting for bar to finish.
09/12/2023 18:48:30: bar
09/12/2023 18:48:24: Getting `foo` from database.
09/12/2023 18:48:24: Wating for `foo`.
09/12/2023 18:48:27: Waiting for foo to finish.
09/12/2023 18:48:27: foo
09/12/2023 18:48:27: Getting `bar` from database.
09/12/2023 18:48:27: Wating for `bar`.
09/12/2023 18:48:27: Waiting for bar to finish.
09/12/2023 18:48:30: bar
Compile: 662.170ms | Execution: 6140.932ms | React with ❌ to remove this embed.
Hazel 🌊💃
Hazel 🌊💃•15mo ago
Notice the difference in timestamps here: 
09/12/2023 18:48:27: Waiting for foo to finish.
09/12/2023 18:48:27: foo
09/12/2023 18:48:27: Waiting for foo to finish.
09/12/2023 18:48:27: foo
Versus here: 
09/12/2023 18:48:27: Waiting for bar to finish.
09/12/2023 18:48:30: bar
09/12/2023 18:48:27: Waiting for bar to finish.
09/12/2023 18:48:30: bar
You can see that we had to wait on foo to notify the consumer we were wating on it, versus with bar we started the task, then notified, and when it finished it printed the result because we needed bar to continue. So you're more concerned with blocking the UI? Then do: 
        public static async Task<Request> GetRequest(int ID)
        {
            var result = await DBHandler.connection.QueryAsync<Request, Patient, Registrant, Request>("SELECT * FROM Requests INNER JOIN Patients ON Patients.PersonalID = Requests.PatientID LEFT JOIN Users ON Users.ID = Requests.RegistrantID WHERE Requests.ID = @ID;",
                (Request request, Patient patient, Registrant registrant) =>
                {
                    request.Patient = patient;
                    request.Registrant = registrant;
                    return request;
                }, splitOn: "PersonalID,ID",
                param: 
                new
                {
                    ID = ID
                }).ConfigureAwait(false);
            
            return result?.SingleOrDefault();
        }
        public static async Task<Request> GetRequest(int ID)
        {
            var result = await DBHandler.connection.QueryAsync<Request, Patient, Registrant, Request>("SELECT * FROM Requests INNER JOIN Patients ON Patients.PersonalID = Requests.PatientID LEFT JOIN Users ON Users.ID = Requests.RegistrantID WHERE Requests.ID = @ID;",
                (Request request, Patient patient, Registrant registrant) =>
                {
                    request.Patient = patient;
                    request.Registrant = registrant;
                    return request;
                }, splitOn: "PersonalID,ID",
                param: 
                new
                {
                    ID = ID
                }).ConfigureAwait(false);
            
            return result?.SingleOrDefault();
        }
ch3rry
ch3rryOP•15mo ago
yeah done well this is pretty much the API of the program, i'll have other scripts (cs-script) which will poll this function
Hazel 🌊💃
Hazel 🌊💃•15mo ago
You'll have to employ similar strategies where you consume the API to prevent blocking.
ch3rry
ch3rryOP•15mo ago
yeah i appreciate your help
Hazel 🌊💃
Hazel 🌊💃•15mo ago
No problem 🙂
Accord
Accord•15mo ago
Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.
Want results from more Discord servers?
Add your server