Skip to content

Loading two different Poco objects from the same resultset

Cleve Littlefield edited this page Feb 1, 2016 · 1 revision

Assuming we have two Poco models:

public class Project
{
    public int Id { get; set; }
    public string Name { get; set; }
    public User ProjectManager { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

And a two map definitions like this:

var projectMapDefiniton = new MapDefiniton<Project>();
projectMapDefinition.Map(model => model.Id, "ProjectId");
projectMapDefinition.Map(model => model.Name, "ProjectName");

var userMapDefiniton = new MapDefiniton<User>();
userMapDefinition.Map(model => model.Id, "ProjectManagerId");
userMapDefinition.Map(model => model.Name, "ProjectManagerName");

Execute and map reader (remember all the SqlConnection/SqlCommand can be moved to boilerplate code, just here for a complete example).

using (var connection = new SqlConnection("connectionstring"))
{
    using (var command = new SqlCommand(
            @"SELECT P.Id AS ProjectId, P.Name AS ProjectName, U.Id AS ProjectManagerId, U.Name AS ProjectManagerName 
                FROM Project P
                JOIN User U
                  ON P.ProjectManagerId = U.Id", connection))
    {
        await connection.OpenAsync();
        using (var reader = command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
        {
            var projectMap = projectMapDefinition.CreateMap();
            var userMap = userMapDefinition.CreateMap();
            projectMap.LoadOrdinals(reader);
            userMap.LoadOrdinals(reader);
            
            var list = new List<Project>();
            while (await reader.ReadAsync())
            {
                var project = new Project();
                projectMap.Load(project, reader);
                var user = new User();
                userMap.Load(user, reader);
                project.ProjectManager = user;
                list.Add(project);
            }

            return list;
        }
    } 
}
Clone this wiki locally