Can Dapper return mapped object & other non mapped values?

Question

Can Dapper return mapped object & other non mapped values?

.net-4.6 c# dapper

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.

Royi Namir

Popular Answer

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.

bbsimonbb

View more on Stack Overflow

Andrei Dragotoniu · Accepted Answer · 2/8/2017 8:21:29 AM

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.

Can Dapper return mapped object & other non mapped values?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator