gistfile1.cs

using System;
using System.Data;
using System.Linq;
using Dapper;
using IzoobleBackend.MainDomain.DB;
using IzoobleBackend.MainDomain.Dtos;

namespace IzoobleBackend.MainDomain.Queries
{
    public class GetGlobalStreamQuery : IQuery<StreamDto>
    {
        private readonly DateTime? reviewedBefore;
        private readonly DateTime? reviewedAfter;
        private readonly int? nrOfReviews;

        public GetGlobalStreamQuery(DateTime? reviewedBefore = null, DateTime? reviewedAfter = null, int? nrOfReviews = null)
        {
            this.reviewedBefore = reviewedBefore;
            this.reviewedAfter = reviewedAfter;
            this.nrOfReviews = nrOfReviews;
        }

        public StreamDto Get(IDbConnection connection)
        {
            var param = new DynamicParameters();
            
            param.Add("@i_nr_of_reviews", nrOfReviews, dbType: DbType.Int32);
            param.Add("@i_reviewed_before", reviewedBefore, dbType: DbType.DateTime);
            param.Add("@i_reviewed_after", reviewedAfter, dbType: DbType.DateTime);

            using (var multi = connection.QueryMultiple("sp_get_global_stream", param: param, commandType: CommandType.StoredProcedure))
            {
                var reviews = multi.Read().ToArray();
                var comments = multi.Read().ToArray();

                var streamItems = (from r in reviews
                                   join c in comments on r.review_id equals c.review_id
                                   select new ReviewDto
                {
                    Id = r.review_id,
                    Rating = r.review_rating,
                    Text = r.review_text,
                    CreatedAt = r.review_created_at,

                    Product = new ProductDto()
                    {
                        Id = r.product_id,
                        Name = r.product_name,
                        CoverPic = r.product_cover_pic,
                        Url = r.product_url,
                    },

                    Author = new UserDto()
                    {
                        Id = r.user_id,
                        Name = r.user_name,
                        ProfilePic = r.user_profile_pic,
                        Username = r.user_username,
                    },

                    Comments =  new CommentDto
                    {
                        Id = c.id,
                        ReviewId = c.review_id,
                        Text = c.text,
                        CreatedAt = c.created_at,
                        Author = new UserDto()
                        {
                            Id = c.user_id,
                            Username = c.user_username,
                            Name = c.user_name,
                            ProfilePic = c.user_profile_pic
                        }
                    })
                }).ToArray();
                return new StreamDto() { Reviews = streamItems };
            }
        }
    }
}