Returning ienumerable result from multiple query in dapper

Question

I've been trying to create methods where I use dapper's QueryMultiple. I've managed to get the multiple query to work, and when I debug I can see that the values from the database gets returned. However, I would like to see the data in Postman when calling the API. Here's where the problems start, since I can't seem to get any return type to work, it's almost always the same error. The error is almost always regarding "Cannot convert". Examples of errors are:

Error   CS1503  Argument 1: cannot convert from 'string' to \RequestRepo.cs 45  Active

Error   CS0266  Cannot implicitly convert type 'System.Collections.Generic.List<string>' to 'System.Collections.Generic.IEnumerable<V4ReactAPI.Models.V4RequestSite.ViewModels.RequestOverview>'. An explicit conversion exists (are you missing a cast?) 90    Active

The controller looks like:

    [HttpGet]
    public async Task<IActionResult> testa(RequestOverview model)
    {

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }
        try
        {
            await _request.testa(model);
        }
        catch (Exception ex)
        {

            return BadRequest(ex.Message);
        }
        return Ok();
    }

The model:

public class VehicleReg
{
    public string vehicleRegNr { get; set; } = "";
}

public class TestIgen
{
    public string ownerName { get; set; } = "";
}

(Name of the whole model class is RequestOverview)

Now the method, which i have in an own class:

    public async Task<IEnumerable<RequestOverview>> testa(RequestOverview model)
    {

        string query = "SELECT TOP 5 RegNumber as vehicleRegNr FROM [dbo].[Vehicle];
        SELECT TOP 5 Name as ownerName FROM [dbo].[Customer];";

        var result = _sqlconnection.QueryMultiple(query);
        var vehicles = result.Read<VehicleReg>();
        var test = result.Read<TestIgen>();
        List<string> allQueries = new List<string>();

        foreach (var item in vehicles)
        {
            allQueries.Add(item.vehicleRegNr);
        }

        foreach (var item in test)
        {
            allQueries.Add(item.ownerName);

        return allQueries.ToList();
    }

This throws an ERROR directly (The CS0266 as shown before).

Basically, I need to return the var vehicles and var test returned in some way, to be able to get the data to be shown when calling the api in postman for example.

I've also tried this method, but again I'm having trouble returning the data:

 public async Task<bool> Test()
        {

          string query = "SELECT TOP 5 RegNumber as vehicleRegNr FROM [dbo].[Vehicle];
          SELECT TOP 5 Name as ownerName FROM [dbo].[Customer];";

            var result = _sqlconnection.QueryMultiple(query);

            using (var multi = _sqlconnection.QueryMultiple(query, null))
            {
              List<VehicleReg> list1 = multi.Read<VehicleReg>().AsList();
              List<TestIgen> list2 = multi.Read<TestIgen>().AsList();
            }

            return true;
        }

Anyone have any ideas on how to return the data?

[Update] I have tried the following to avoid the converting error, but it's still not returning any data when I try it in Postman:

public async Task<List<string>> Test()
{

  string query = "SELECT TOP 5 RegNumber as vehicleRegNr FROM [dbo].[Vehicle];
  SELECT TOP 5 Name as ownerName FROM [dbo].[Customer];";
    List<string> allQueries = new List<string>();
    using (var multi = _sqlconnection.QueryMultiple(query, null))
   {
        //List<string> allQueries = new List<string>();
        List<VehicleReg> list1 = multi.Read<VehicleReg>().AsList();
        List<TestIgen> list2 = multi.Read<TestIgen>().AsList();
        //return list1.ToList();
        allQueries.Add(list1.ToString());
        allQueries.Add(list2.ToString());
    }
    return allQueries;
}

As stated in error, you are trying to return `List` where `testa` method expects to return type of `IEnumerable` — Selim Yildiz, Jul 21 '21 at 08:51
Your updated code won't build successfully. Please simplify your question — Selim Yildiz, Jul 21 '21 at 09:53
@SelimYildiz hmm it builds successfully for me... But basically, i would like to use async Task (in whatever form really, string/list/bool/ienumerable), and queryMultiple, but i would like to get the results/data from the querys to print in Postman, with the updated code i can see the results when i debug, but in postman it simply returns 200 ok, without the actual data — ajd871, Jul 21 '21 at 10:00
It seems `allQueries.Add(list1.ToString()); allQueries.Add(list2.ToString());` should be something like `allQueries.AddRange(list1.Select(i=>i.vehicleRegNr).ToList()); allQueries.AddRange(list2.Select(i=>i.ownerName).ToList());` — Selim Yildiz, Jul 21 '21 at 11:41

Selim Yildiz · Accepted Answer · 2021-07-21T12:11:09.193

If I understand correctly you want to return a list of vehicleRegNr and ownerName. So list1.ToString(); does not make any sense in that case since it is a type of List<VehicleReg>.

You need to select fields (vehicleRegNr from VehicleReg and ownerName from TestIgen) before adding to your allQueries.

So this

allQueries.Add(list1.ToString());
allQueries.Add(list2.ToString());

should be

allQueries.AddRange(list1.Select(i => i.vehicleRegNr));
allQueries.AddRange(list2.Select(i => i.ownerName));

And also if you want to return that value from API, you need to use OK with given result as follows:

[HttpGet]
public async Task<IActionResult> testa(RequestOverview model)
{

    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    try
    {
        return Ok(await _request.testa(model);
    }
    catch (Exception ex)
    {
        return BadRequest(ex.Message);
    }
    return Ok();
}

See ASP.NET Core return JSON with status code

thank you so much! changing to select and simply adding return Ok before the await in the controller fixed it, thank you! i'll accept this as an answer — ajd871, Jul 21 '21 at 12:19

Returning ienumerable result from multiple query in dapper

1 Answers1