Returning a list of objects where objects contain lists with Dapper ORM

[neocon]

Say I have two tables in SQL that are related, photos table and a phototags table which are joined on photoid, where there is one to many relation.

I want to return a list of photos where each photo has a property called called tags that is list.

This is how I solved it, but I wonder if there is a better way of doing this? One where there is a single trip to the database.

        public List<Photo> GetAllPhotos()
        {
            List<PhotoBO> photoListBO= _photoAppRepository.GetAllPhotos().ToList();
            List<TagBO> listTagBO = _photoAppRepository.GetAllTags().ToList();

            List<Photo> list = new List<Photo>();
            foreach (PhotoBO p in photoListBO)
            {
                Photo photo = _autoMapperAdapter.Map<Photo>(p);
               
                List<string> listTags = new List<string>();
                foreach (TagBO t in listTagBO)
                {
                    if (p.PhotoID == t.PhotoID)
                    {
                        listTags.Add(t.TagName);
                    }
                }
                photo.PhotoTags = listTags;
                list.Add(photo);
            }
            return list;
        }

 

[philldutton]

Take a look at the "A parent object with its child objects" example here: https://www.tritac.com/developers-blog/dapper-net-by-example/

That should do what you need i think.

 

[chroniclard]

Surely you can do that in one SQL query?

 

[neocon]

Surely you can do that in one SQL query?

You can do this for example

        public IEnumerable<CategoryBO> GetCategoriesWithCount()
        {
            using (var connection = GetOpenConnection())
            {
                var sql = @"select 
                                tblCategories.PhotoCategoryID as [PhotoCategoryID], 
                                tblCategories.PhotoCategoryName as [PhotoCategoryName], 
                                Count(*) as [NumberOfPhotos] 
                            from tblCategories 
                            inner join tblPhotos on tblCategories.PhotoCategoryID =  tblPhotos.PhotoCategoryID 
                            group by tblCategories.PhotoCategoryID, tblCategories.PhotoCategoryName";
                return connection.Query<CategoryBO>(sql,null);

            }
        }

 [Table("tblCategories")]
    public class CategoryBO
    {
        // Tell Dapper this is the ID field
        [Dapper.Contrib.Extensions.Key]
        public string PhotoCategoryID { get; set; }

        public string PhotoCategoryName { get; set; }

        // Mark as computed so that Dapper knows it is not in the table
        [Computed]
        public string NumberOfPhotos { get; set; }
    }

but not sure how you could get a list object within?