Using Sql, how can I hydrate child objects from within a single Query?

c# dapper entity-framework sql tsql

Question

I'm using Dapper, but I think this applies to general SQL:

I have the following:

 var dapperQuery = "select * from ( select * from [User] where " +
                                  "AccountDisabled <> 1 and " +
                                  "Rate <= @maxRate and " +
                                  "(datediff(day, Birthdate, @today) / 365) between @minage and @maxage";

 // some conditional stuff

 dapperQuery = dapperQuery + " order by LastOnline desc offset @skip rows fetch next 40 rows only ) As [Limit1] " +
                              "LEFT OUTER JOIN Photo AS [Extent2] ON [Limit1].[UserId] = [Extent2].[UserId]";

 var dbProfiles = connection.Query<User>(dapperQuery, new {
                    maxRate = query.SearchMaxRate,
                    userPoint = "POINT (" + user.LocationLong + " " + user.LocationLat + ")",
                    searchRadius = radius,
                    today = DateTime.UtcNow,
                    minAge = query.SearchLowerAge,
                    maxAge = query.SearchUpperAge,
                    skip = query.Index * 40
                }).ToList();

Now, this is returning the results that I expect, but the User.Photos property is always empty. How do I go about hydrating this from within the query?

My User class:

[Table("User")]
public class User {

    public User() {
        Photos = new List<Photo>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string Username { get; set; }

    public DateTime Birthdate { get; set; }
    [Index("LastOnlineIndex")]
    public DateTime LastOnline { get; set; }

    public string NumberConfirmationCode { get; set; }

    public int Rate { get; set; }

    public int SearchRadius { get; set; }
    public int SearchLowerAge { get; set; }
    public int SearchUpperAge { get; set; }
    public int SearchMaxRate { get; set; }

    public bool AccountDisabled { get; set; }

    public virtual ICollection<Photo> Photos { get; set; }

}

My Photo class:

public class Photo {

    public Photo() {}

    [Key]
    public int PhotoId { get; set; }
    public string Url { get; set; }
    public bool IsProfilePhoto { get; set; }

    [ForeignKey("User")]
    public int UserId { get; set; }
    public virtual User User { get; set; }

}
 public class PhotoConfiguration : EntityTypeConfiguration<Photo> {
    public PhotoConfiguration() {
        // One-to-Many
        HasRequired(s => s.User).WithMany(s => s.Photos).HasForeignKey(s => s.UserId);
    }
}

Accepted Answer

First of all, I'm not sure why you have posted the Entity Framework fluent configuration if you are using Dapper.NET for fetching entities.

If you want to fetch an entity together with one-to-many related entities, check the Dapper.NET readme on https://github.com/StackExchange/dapper-dot-net. The following example from this readme should be applicable for your case:

var sql = 
@"select * from #Posts p 
left join #Users u on u.Id = p.OwnerId 
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});

Consider each User has a collection Posts and you want to fill that collection using fetched associations. You can use Linq to Objects to achieve this:

var users = data.GroupBy(p => p.Owner.Id).Select(g =>
{
    var user = g.First().Owner;
    user.Posts = g.ToList();
    return user;
 });


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why