Entity framework select all related items in fact table with condition in dimension table

Question

Suppose I have three tables like below:

TShirt Table

TShirt_Color Table

Color Table

And the desired result on the screen would be:

It's a token input letting user to filter the t-shirt with color selected and when the user clicks search, the t-shirts containing the colors will be displayed in the grid below.

I'm using Entity framework for the search and after user selects the color, the system will search using the below Linq (userSelectedColor is the List which user selected the color):

var results = from a in TShirt
              join b in TShirt_Color on a.Id equals b.TShirtId into c
              from d in c.DefaultIfEmpty()
              join e in Color on c.ColorId equals e.Id into f
              from g in f.DefaultIfEmpty()
              where userSelectedColor.Contains(g.Id)
              group new {a, g} by
              new
              {
                   a.Id,
                   a.Name
              } into h
              select new 
              {
                  ID = a.Id,
                  TShirtname = a.Name,
                  AvailableColors = h.Select(i=>i.g.ColorName)
              }

But the above query have a problem:

If the user selects "Yellow", the result will be:

It filtered out the other colors.

How to fix this problem?

For your reference, I'm using EF6 and SQL server 2014

Answer 1

You should filter your groups, not the colors:

var results = from a in TShirt
              join b in TShirt_Color on a.Id equals b.TShirtId into c
              from d in c.DefaultIfEmpty()
              join e in Color on c.ColorId equals e.Id into f
              from g in f.DefaultIfEmpty()
              group new {a, g} by
              new
              {
                   a.Id,
                   a.Name
              } into h
              where userSelectedColor.Intersect(h.Select(z=>z.g.Id)).Any()
              select new 
              {
                  ID = a.Id,
                  TShirtname = a.Name,
                  AvailableColors = h.Select(i=>i.g.ColorName)
              }

This way, you create the groups with all the colors and fully remove the groups that doesn't contain the selected color without changing the other groups, instead of not including those color on the groups.