CODEWITHSUNDEEP
c#web-servicesserializationdapper
surfmuggle
surfmuggle

Reputation: 5944

Return only certain columns / fields from Dapper connection.Query<T>(sql)

The problem and the question

Problem: I thought that connection.Query<myT>("Select A, C from myTable"); would only fill the selected columns and the matching fields of the underlying class / object. But it seems that this is not the case - (although the reason might be my misuse or how i serialize the result). Currently all fields of the underlying class are in serialized result.

Questions: What can i do to return only certain properties based on the select and serialize them?

The setup

Based on A look at dapper i put the following together:

A simple class for the entity / model:

public sealed class Category 
{
    public int Id { get; set; }
    public string A { get; set; }
    public string B { get; set; }
    public string C { get; set; }
    public DateTime DateAdded { get; set; }
    public DateTime DateModified { get; set; }
}

IDbConnection and Dapper Repository Class

Return an open IDbConnection

protected static IDbConnection OpenConnection()
{
    IDbConnection connection = new SqlConnection(ConnectionString);
    connection.Open();
    return connection;
}

use the above connection with dapper in the repository class

public IEnumerable<Category> GetCategories()
{
    using (IDbConnection connection = OpenConnection())
    {
        const string sql = " SELECT Id, A, C FROM Categories";
        return connection.Query<Category>(sql);
    }
}

Serialze the result and send it to client by Asmx

[WebMethod]
public string GetCategoriesTreelist()
{
    var repo = new CategoryRepository();
    IEnumerable<Category> categories = repo.GetCategories();            
    var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    return serializer.Serialize(categories);
}

Output

Currently every field is serialized instead of only the selected fields from the sql statement above.

Thanks

Update after Marc answered the question

Missing the forest for the trees: Like Marc said Dapper has no part that the serialization returns all members of the objects. This simple test shows that all members of the underlying class are serialized.

[WebMethod]
public string GetCategoriesTestObjectSerialization()
{
    
    List<Category> categoriesList =  new List<Category>(){
        new Category(){Id=1, A="a1", C="foo"},
        new Category(){Id=2, A="a2", C="bar"},
        new Category(){Id=3, A="a3", C="baz"}
    };
    IEnumerable<Category> categories= from c in categoriesList 
                                                where c.Id > 0
                                                select c;
    var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    return serializer.Serialize(categories);
}

Creating a simple Data Transfer Object solves the issue: IEnumerable<CategoryDTO> categories= from c in categoriesList where c.Id > 0 select c;

private class CategoryDTO
{
    public int Id { get; set; }
    public string A{ get; set; }
    public string C{ get; set; }
}

More on DTOs

Upvotes: 4

Views: 5424

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063704

This is fundamentally a serializer factor. Dapper explicitly never assigns to anything that it doesn't get at a column. However, I suspect that if you serialize a new Category() you'll see very similar behaviour. Options:

  • live with it
  • create a DTO model that fits what you want, and query into that instead
  • investigate whether the serializer supports conditional serialization (but note this can require a lot of boilerplate)

Upvotes: 2

Related Questions