C# IQueryable - How to query a list inside another list

Question

I'm working in a project which displays a BoardDto that contains an IQueryable of ListDTO. Each ListDTO contains an IQueryable of CardDTO.

How could I get my BoardDto object with all the ListDtos loaded, but only with their Cards where CardType = 1 ?

public class BoardDTO
{
        public int BoardId { get; set; }
        public string Nome { get; set; }
        public IQueryable<BoardListDTO> Lists { get; set; }
}

public class BoardListDTO
{
        public int BoardListId { get; set; }
        public string Name { get; set; }
        public IQueryable<CardDTO> Cards { get; set; }
}

public class CardDTO
{
        public long CardId { get; set; }
        public CardType Type { get; set; }
}

Edit

The image bellow illustrates how the items must compose the DTO.

Solution

This solved my problem:

var query = _context.Boards
                    .AsNoTracking()
                    .ProjectTo<BoardDto>(_mapper)
                    .Where(x => x.BoardId == id)
                    .Select(x => new BoardDto
                             {
                                BoardId = x.BoardId,
                                Name = x.Name,
                                Lists = x.Lists.Select(list => new BoardListSearchDTO
                                                {
                                                    Cards = list.Cards.Where(x => x.Type == 1),
                                                }).FilterLists(filterListsBy, listSearchValue)
                                            });

Answer 1

Separate your concerns. Entities represent domain models. DTOs represent view or consumer models. While EF deals extensively with IQueryable, your consumers don't typically need IQueryable, most of the time they just need to be able to enumerate over the results, so IEnumerable is sufficient. Even within Entities, I don't represent relationships between entities with IQueryable, instead I use ICollection.

IQueryable is a structure to indicate that Linq expressions should be passed through to the source of the data. For EF Linq operations that allows Linq expressions to translated down to SQL. When it comes to dealing with consuming data that has been returned, you can use Linq against IEnumerable and the like still if you need to further filter or translate results. Entities should never really be passed outside of the scope of their DbContext as features like lazy loading requires the DbContext to be alive, and you cannot reliably assume lazy loads would never be tripped by a consuming view or serializer etc. Entities are data state, view state should be kept separate.

Given a domain model of entities consisting of Boards, BoardLists, and Cards my entities would look something like:

public class Board
{
    public int BoardId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<BoardList> Lists { get; set; } = new List<BoardList>();
}

public class BoardList
{
    public int BoardListId { get; set; }
    public string Name { get; set; }
    public Virtual ICollection<Card> Cards { get; set; } = new List<Card>();
}

public class Card
{
    public long CardId { get; set; }
    public CardType Type { get; set; }
}

The entities would typically reflect all of the data in the underlying table, and the relationships between entities. The child collections are declared as virtual to accommodate lazy loading if needed, and initialized as a new collection. This accommodates cases where we want to create a new entity and have it's child collection ready to go.

The DTOs represent just the data from the model that the consumer needs. This is where we also consider filtered views of data to suit the view. Rather than ICollection, we can just use IEnumerable. You can use ICollection or IList but IEnumerable is generally the recommendation because it says to the consumer "here's a set of data you can read, but don't try adding to it, etc.".

public class BoardDTO
{
    public int BoardId { get; set; }
    public string Name { get; set; }
    public IEnumerable<BoardListDTO> Lists { get; set; }
}

public class BoardListDTO
{
    public int BoardListId { get; set; }
    public string Name { get; set; }
    public IEnumerabe<CardDTO> Cards { get; set; }
}

public class CardDTO
{
    public long CardId { get; set; }
    public CardType Type { get; set; }
}

In this example the DTOs look identical to the entities. Typically though, the entity and underlying tables will contain many fields, where our view models or DTOs only need a few fields, or relationships.

When it comes to populating your collection of DTOs for the view, you leverage projection using Select. So if you want to list all boards that have at least 1 card of cardType 1, and then return those boards with only their CardType 1 cards:

This is assuming that CardType is mapped as an Enum. If CardType is mapped to a table, then it will need an entity / DTO as well.

var boards = context.Boards
   .Where(b => b.Lists
      .Any(bl => bl.Cards.Any(c => c.CardType == CardTypes.Type1)))
   .Select(b => new BoardDTO
   {
      BoardId = b.BoardId,
      Name = b.Name,
      Lists = b.Lists.Select(bl => new BoardListDTO
      {
         BoardListId = bl.BoardListId,
         Name = bl.Name,
         Cards = bl.Cards.Where(c => c.CardType == CardTypes.Type1)
            .Select(c => new CardDTO
            {
               CardId = c.CardId,
               CardType = c.CardType
            }).ToList()
      }).ToList()
   }).ToList()

This example does a lot of 1-to-1 mapping between entity and DTO. Often DTOs are flattened to represent a structure that the consuming view can use. This might consolidate the consumer model to just the Board and a list of cards with the board list name appearing within the related card DTO. (So a board DTO would just display a list of Cards that include their BoardList name.)

DTO/ViewModels like this are useful for filtering data because entities should always reflect the true data state so you cannot return entities with just a subset of some of the related cards. The Board will always associate with all board lists associated to it, and all cards associated to those. Using DTOs and Select you can customize what data the view/consumer actually needs.