Why result of IQueryable type converts to empty object?

Question

public IHttpActionResult GetAllCollections(CollectionsDTO collectionsDTO)
        {
            if (!ModelState.IsValid)
                return BadRequest();

            try
            {
                var collectionsSectionRolesFlatDTO = (from c in db.Collections
                                                      join sr in db.SectionRole
                                                      on c.SectionRoleId equals sr.Id
                                                      select new CollectionsSectionRolesFlatDTO
                                                      {
                                                          Collections = new CollectionsDTO
                                                          {
                                                              CollectionTitleAr = c.CollectionTitleAr,
                                                              CollectionTitleEn = c.CollectionTitleEn,
                                                              CoverImagePath = c.CoverImagePath,
                                                              SectionRoleId = c.SectionRoleId,
                                                          },
                                                          SectionRole = new SectionRoleDto
                                                          {
                                                              NameAr = sr.NameAr,
                                                              NameEn = sr.NameEn
                                                          }
                                                      })
                                                      .AsQueryable();

                var collectionsDto = Mapper.Map<List<CollectionsSectionRolesDTO>>(collectionsSectionRolesFlatDTO);

                return Ok(collectionsDto);
            }
            catch (Exception ex)
            {
                return BadRequest("GetAllCollection: "+ ex.ToString());
            }

        }

DTOs:

public class CollectionsDTO
    {
        public int Id { get; set; }
        public string CollectionTitleEn { get; set; }
        public string CollectionTitleAr { get; set; }
        public string CoverImagePath { get; set; }
        public int? SectionRoleId { get; set; }

    }

    public class CollectionsSectionRolesFlatDTO
    {
       public SectionRoleDto SectionRole { get; set; }
       public CollectionsDTO Collections { get; set; }
    }

    public class CollectionsSectionRolesDTO
    {
        public int Id { get; set; }
        public string CollectionTitleEn { get; set; }
        public string CollectionTitleAr { get; set; }
        public string CoverImagePath { get; set; }
        public int? SectionRoleId { get; set; }

        //section role
        public string NameAr { get; set; }
        public string NameEn { get; set; }

    }

public class SectionRoleDto
{
    public int Id { get; set; }

    public string NameEn { get; set; }

    public string NameAr { get; set; } 
}

Automapper:

   CreateMap<Collections, CollectionsDTO>();
   CreateMap<CollectionsSectionRolesFlatDTO, CollectionsSectionRolesDTO>();

Now the problem is mindboggling.

this line

var collectionsDto = Mapper.Map<List<CollectionsSectionRolesDTO>>(collectionsSectionRolesFlatDTO);

is supposed to return the data but instead it returns what I have passed in the function param.

Why is this happening? I have spent literally hours but nothing. I checked all the automapper setting but this doesn't work.

I am new to c# so any help regarding it would be appreciated. thanks.

Answer 1

Why would you be going through the trouble of double projection? Just configure Automapper with the information to get a desired DTO out of the object graph. Ensure you have navigation properties set up for your relationships to make querying against the object graph a lot simpler. Linq & EF does not need to be written as a substitute for SQL. (explicit joins) Navigation properties allow EF to provide those joins entirely behind the scenes for simpler querying.

First, the mapping:

CreateMap<Collections, CollectionsSectionRolesDTO>()
    // Id, NameAr, and NameEn should auto-resolve.
    .ForMember(x => x.CollectionTitleEn, opt => opt.MapFrom(src => src.Collection.CollectionTitleEn))
    .ForMember(x => x.CollectionTitleAr, opt => opt.MapFrom(src => src.Collection.CollectionTitleAr));
    // Continue for fields coming from Collection...

then to query using the automapper config. (config)

var results = db.Collections
     .ProjectTo<CollectionsSectionRolesDTO>(config)
     .ToList();

Done and dusted. No need to select and flatten data first into memory then use Automapper to create a new in-memory collection of the desired view model. Just project down to the desired view model directly within the query.

Double-projection is useful where you need to flatten data down first in order to perform conversions or transforms that cannot be converted in SQL. The first projection (Select) would typically be to an anonymous type or a DTO using ProjectTo, then fed into code that would provide the necessary transformations to produce the end result view models/DTOs.