C#•12mo ago

✅ (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 🌊💃•12mo ago

You can use mapping

ch3rry•12mo ago

i'm already doing so?

Hazel 🌊💃•12mo 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•12mo ago

yeah i am, problem is it only works (i think) with multiple rows

Hazel 🌊💃•12mo ago

You're saying you already have a mapping in place and you just want a single object back? Like a single record

ch3rry•12mo 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 🌊💃•12mo 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•12mo 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 🌊💃•12mo ago

It's just returning Task<IEnumerable<Request>>, so 1 sec

ch3rry•12mo ago

is connection.Query a blocking function?

Hazel 🌊💃•12mo ago

Yeah But you can get around that

ch3rry•12mo 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 🌊💃•12mo 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•12mo 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 🌊💃•12mo 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

Gaming

Programming

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