CODEWITHSUNDEEP
c#asp.net-mvc-5dapper
Bagzli
Bagzli

Reputation: 6579

Call a function on each record from dapper result

I am trying to call a function on each record that will set some field values. When the results are meant to be paged then this is achieved in the following way:

public IDataWrapper GetPagedQuery<T>(string myQuery, object param, Action<T> customAction)
{
    var obj = new DataWrapper ();
    using (var oConn = CreateConnection(ConnectionString))
    {
        TotalPages totalRows = null;
        var list = oConn.Query<T, TotalPages, T>(myQuery, (e, t) =>
        {
            totalRows = t;
            if (mapAction != null) customAction(e);
            return e;
        }, param, splitOn: "SplitOn");
        obj.RowsFound = (IEnumerable<dynamic>)list;
        obj.TotalRows = totalRows == null ? 0 : totalRows.TotalRows;
    }
    return obj;
}

My problem comes when the results are not meant to be paged. My query in the first example includes split on column and this is why it all works, however my next query is a simple query, such as Select Column1, Column2 FROM MyAwesomeTable which will return all rows, columns etc...

The problem is that I still need to apply customAction on each result that comes back. Now lets imagine, that there is a potential of few million records coming back (believe me, this is not unrealistic given my situation), as such I do not want to loop through each record again aftermath and apply my method, I want that method applied as the dapper is returning results, just like it is in first case.

Here is what I tried:

public IDataWrapper GetNonPagedQuery<T>(string myQuery, object param, Action<T> customAction)
{
    var obj = new DataWrapper ();
    using (var oConn = CreateConnection(ConnectionString))
    {
        var list = oConn.Query<T>(myQuery, (e) =>
        {
            if (mapAction != null) customAction(e);
            return e;
        }, param).ToList();
        obj.RowsFound = (IEnumerable<dynamic>)list;
        obj.TotalRows = list.Count();
    }
    return obj;
}

I get an error for above code that it cannot resolve method Query<T>(etc... I understand it is because it doesn't exist. I am here asking what would be the best way to accomplish what I am trying to do? Is it possible with dapper.

Upvotes: 1

Views: 2775

Answers (3)

Lee Song
Lee Song

Reputation: 621

I'm not expert in paged query or non-paged query, but if anyone is looking to apply action for each rows, I've came up with this based on gbjbaanb's reference to Dapper documentation.

public static async IAsyncEnumerable<T> QueryLazyWithActionAsync<T>(this DbConnection self, string sql, object query, Action<T> action)
{
    using var reader = await self.ExecuteReaderAsync(sql, query);
    var parser = reader.GetRowParser<T>();
    while (await reader.ReadAsync())
    {
        var row = parser(reader);
        action(row);
        yield return row;
    }
}

I use it like so

var result = dmsConnection.QueryLazyWithActionAsync<CustomerResult>(sql, query, row =>
{
    Console.WriteLine($"Querying for customer {row.FullName}");
});

Upvotes: 2

gbjbaanb
gbjbaanb

Reputation: 52679

Yes, it possibly is:

See the section of the Dapper docs about Type Switching per Row. I doubt (ie not tried it myself) that it wouldn't work if you used the same system to massage the results into the format you want by reading each row and processing it explicitly.

Upvotes: 1

Dan Roberts
Dan Roberts

Reputation: 2329

I'm afraid that the short answer is that you can't do this.

The slightly longer answer is that in your paged query, it essentially is a second pass through the data since Dapper does some work to translate each row's data into an instance of "T" and then it makes a separate call to the "map" method that you use to call your "customAction".

So, there would be very little difference between that and doing a simple non-paged "conn.Query" call followed by a subsequent pass to execute your customAction. Something like the following:

public IDataWrapper GetNonPagedQuery<T>(string myQuery, object param, Action<T> customAction)
{
    var obj = new DataWrapper();
    using (var oConn = CreateConnection(ConnectionString))
    {
        var results = oConn
            .Query<T>(myQuery)
            .ToList();
        obj.TotalRows = results.Count();
        obj.RowsFound = results
            .Select(value =>
            {
                customAction(value);
                return value;
            })
            .Cast<dynamic>();
    }
    return obj;
}

If your concern is that you may be loading many, many records in the non-paged query then it's worth bearing in mind that all of those records will be loaded into memory at once; they will not be pulled from the database one-at-a-time as your enumerate over the returned results and this might be a considerable drain on resources (if you're talking about so much data that you wanted to avoid a second enumeration).

This has to be the case because the SQL connection is closed as GetNonPagedQuery returns and so there is no way that it could be left open to read the data "on demand" by the caller. If you really are working with massive amounts of data, perhaps a non-paged query isn't the best approach?

Note that in the code above, the "customAction" will only be called as you enumerate over the rows, those will not all be fired before GetNonPagedQuery returns. If "customAction" is potentially an expensive operation then this may be to you benefit. On the other hand, if you wanted "customAction" to be called for every result before GetNonPagedQuery returns then you would need one more ToList() call after the Cast<dynamic>(), it just depends which scenario is more useful to you.

Upvotes: 2

Related Questions