Can Dapper return mapped object & other non mapped values?

Question

I have this working sample query using dapper ( in reality I'm using a real table) :

async void Main()
{
    var sql = @"SELECT PersonId = 1,
                FirstName = 'john',
                LastName = 'Lennon'";
    using (var conn = new SqlConnection(@"Data Source=....;Initial Catalog=W...."))
    {
        var person = await conn.QueryAsync<Person>(sql);
          person.Dump();
    }
}


public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Result :

So mapping is working as expected. But sometimes I have queries which returns another value like :

SELECT PersonId = 1,
       FirstName = 'john',
       LastName = 'Lennon' , 
       cnt=(SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS) //example

Which is perfectly legal :

Question :

Is it possible to return a Person object and other non mapped values ( in the same select)

Something like :

 await conn.QueryAsync<Person,int>(sql)

A real example :

SELECT  [AddressId]
      ,[PersonName]
      ,[Street]
      ,[Address_2]
      ,[House] , 
       cnt=(COUNT(1) OVER (PARTITION BY house)  )
  FROM [WebERP].[dbo].[App_Address]

So I return an Address object with count which regards to the same table and I don't want another select.

Sergey Berezovskiy · Answer 1 · 2017-02-08T09:12:45.970

1

Yes, you can use QueryMultiple extension for that. But you should use separate select for getting count of columns:

SELECT PersonId = 1, FirstName = 'john', LastName = 'Lennon'
SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS)

Then get both results

using(var result = sqlConnection.QueryMultiple(sql))
{
    var person = result.Read<Person>().Single();
    int count = multi.Read<int>().Single();
}

Further reading: Multiple Results part.

Another way to return addition value is dynamic query. But in that case you will have to build Person object manually:

var sql = @"SELECT PersonId = 1,
            FirstName = 'john',
            LastName = 'Lennon',
            cnt=(SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS)";
var row = conn.Query(sql).Single();
var person = new Person { 
    PersonId = row.PersonId, 
    FirstName = row.FirstName, 
    LastName = row.LastName
};
int cnt = row.cnt;

AFAIK there is no other way to return both person and cnt.

edited Feb 08 '17 at 09:12

answered Feb 08 '17 at 08:13

Sergey Berezovskiy

232,247
41
429
459

I know that. but as I mentioned (_( in the same select)_) - there is no point of hitting the table twice. sometimes I add rownumber over partition stuff and other window functions so I need it in the same select – Royi Namir Feb 08 '17 at 08:14
@RoyiNamir you are not hitting table with first select – Sergey Berezovskiy Feb 08 '17 at 08:15
It was just an example (for simplicity). In reality I select from a table (and its columns) and return another windows function ( `sum over ( order by...)`) – Royi Namir Feb 08 '17 at 08:16
@RoyiNamir if you don't have additional fields in Person class, then either use multiple queries or return dynamic result and create person manually – Sergey Berezovskiy Feb 08 '17 at 08:42

score 1 · Accepted Answer · answered Feb 08 '17 at 08:21

the easiest thing to do would be to add cnt to your Person class. You could of course rename it to something more meaningful. Make it a nullable int so it will be set or not depending on its existance in the dataset.

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? cnt { get;set; }
}

Now, this cnt does not really belong to the person class, but normally you would not assign a dataset to such a class, you would assign it to something like a DTO and then map that into whatever model class or classes you have and use the extra properties any way you see fit. This will keep your business classes ( such as Person) pure.

MMM... make sense. So I can create a derived Person type named `PersonCounted` which will have this additional prop . — Royi Namir, Feb 08 '17 at 08:26
definitely, just change the name in the sql code "cnt as PersonCount" and it'll do the job or just PersonCount = whateevr — Andrei Dragotoniu, Feb 08 '17 at 08:31

score 1 · Answer 3 · answered Feb 08 '17 at 13:26

Are you sure you want to do this? Keep on the sunny side of life, as the song says. A query should have a return type. Different queries should have different return types. The magic line where you say conn.Query<ReturnType>(SQL) is you putting your hand on your heart and promising that SQL will fill ReturnType. If, in one place, the ReturnType has fields that the query is never intending to fill, that for me is a code smell. Someone maintaining that code will never understand why it's like that. If data from different queries is later fed into a single method, create an interface.

A polluting side effect of ORM thinking is the the desire to "normalize" your classes. Having dozens of Person objects is not necessarily a problem if they do different things in different situations. In your DB, you don't want the same data in two places. In your app, you don't want the same behaviour in two places. These are very different notions. If you were using QueryFirst (disclaimer: which I wrote) this would all be much simpler, and you wouldn't have to put your hand on your heart.

Thanks - So basically you're saying that `conn.Query(SQL) ` is a viable solution. right ? — Royi Namir, Feb 08 '17 at 13:30

score 0 · Answer 4 · edited May 23 '17 at 12:18

0

There is an option to use dynamic return type like described here I would prefer to have composite return type like Tuple however, it's basically a Multi Mapping described in Dapper's manual. (YMMV, I'm new to Dapper myself)

edited May 23 '17 at 12:18

Community

1
1

answered May 11 '17 at 08:58

Kostya Callmetofindout

33
6

Can Dapper return mapped object & other non mapped values?

4 Answers4