How to group SQL output using Dapper

Question

Suppose I have an output table like this:

Names |   Values
_______________
 A    |   value1
 A    |   value2
 B    |   value3
 B    |   value4
 C    |   value5

I use dapper to connect to the database. Instead of direct sql commands, I call a stored procedure that has multiple queries so I use QueryMultipleAsync to get the results but I got stuck on this one. For the sake of this example, the task returns only this one.

public async Task<Something> GetData()
{
    using (IDbConnection database = _mssqlDbConnectionFactory.Connect())
    {
        var results = await database.QueryMultipleAsync("spGetSomething");
        ...
        IEnumerable<Something> something = results.Read< > ...

        return something
    }
}

public class Something
{
    public string? Name { get; set; }
    public List<string>? Values { get; set; }
}

Besides, I use sql server and the query for this output is kinda long, so I didn't include it here, and if I'm correct, there is no way of grouping them together, since I need every value. What are my options here? Can I use some kind of linq to group them when I read the result?

Tell us what you want the result to be. Also show us the stored procedure because it's unclear how the "suppose I have output like" represents multiple query resultsets — Caius Jard
– Caius Jard, Commented Apr 25, 2022 at 5:30
For the sake of this example, the task returns only this one. - don't dumb anything down for us, you risk getting stuck because you can't translate the advice we give back to your situation. We can cope with full and accurate details - saying you're using QueryMultiple and then "there is only one" just causes confusion — Caius Jard
– Caius Jard, Commented Apr 25, 2022 at 5:32
output is kinda long - we don't need all million rows, just something adequately representative. For example "I have this sproc that runs 3 queries <insert a sample sproc that shows 3 short select statements>. In SSMS it produces these 3 result sets <show 3 result sets of 2 rows each>. I run it in c# like this <show c# code>. I want to get a single list of XYZ objects that looks like this <show some pseudoJson/list initializer representation of what you want>" — Caius Jard
– Caius Jard, Commented Apr 25, 2022 at 5:35
@CaiusJard these are all true, even tho I tried to make it as clear as possible that I think I did since I got my solution. I'll keep these in mind, thanks! — drazse
– drazse, Commented Apr 25, 2022 at 7:43

D-Shih · Accepted Answer · 2022-04-25 01:26:25Z

2

You can try to use a class as DataModel to relay your data from DB.

public class DataModel
{
    public string? Name { get; set; }
    public string Values { get; set; }
}

Then use lambda Groupby

IEnumerable<Something> something = results.Read<DataModel>()
   .GroupBy(x=> x.Name)
  .Select(x=> new Something(){
     Name = x.Key,
     Values = x.Select(y=>y.Values)
  });

answered Apr 25, 2022 at 1:26

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

drazse Over a year ago

Thank you, I was looking for something exactly like this!

Collectives™ on Stack Overflow

How to group SQL output using Dapper

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related