Unable to fetch parent entity with children that only match a certain criteria

[atrauzzi]

This is in EF Core, I'll use the following schema for my examples:

BagOfSkittles
-----------------
id
type


Skittle
-----------------
id
bag_id
colour

In short, if I want to fetch a specific BagOfSkittles (whether by type or id), how do I ensure I always get the matched BagOfSkittles, while filtering the Skittles that belong to it by colour. Even when there are no related Skittles that match.

I think what I'm after here is a left join, but the syntax to produce such a query isn't apparent to me and I haven't been able to find it in documentation (yet).

Perhaps something like this?

            var firstBagOfSkittles = await context.BagsOfSkittles
                .Include(b => b.Skittles)
                // This is where I'm a bit fuzzy.  Maybe something like...
                .Where(b => b.Skittles.Any(s => s.colour == "red" || s.colour == null))
                .FirstAsync()
            ;

[smitpatel]

@atrauzzi - Can you elaborate more what you are trying to fetch? If you know what your SQL should be then please post it. It will help in translating to LINQ.

[atrauzzi]

@smitpatel -

...how do I ensure I always get the matched BagOfSkittles, while filtering the Skittles that belong to it by colour. Even when there are no related Skittles that match.

So I guess if I had the following data:

+---------------+----+------------+
| BagOfSkittles | id | type       |
+---------------+----+------------+
|               |    |            |
+---------------+----+------------+
| 1             | 45 | 'tropical' |
+---------------+----+------------+
| 2             | 77 | 'original' |
+---------------+----+------------+
| 3             | 18 | 'sour'     |
+---------------+----+------------+

+---------+----+--------+----------+
| Skittle | id | bag_id | colour   |
+---------+----+--------+----------+
|         |    |        |          |
+---------+----+--------+----------+
| 1       | 1  | 45     | 'red'    |
+---------+----+--------+----------+
| 2       | 2  | 45     | 'blue'   |
+---------+----+--------+----------+
| 3       | 3  | 77     | 'red'    |
+---------+----+--------+----------+
| 4       | 4  | 77     | 'green'  |
+---------+----+--------+----------+
| 5       | 5  | 45     | 'pink'   |
+---------+----+--------+----------+
| 6       | 6  | 18     | 'orange' |
+---------+----+--------+----------+
| 7       | 7  | 45     | 'red'    |
+---------+----+--------+----------+

Using the psuedo-linq in my original description, my hope would be to always get every BagOfSkittles regardless of whether related rows matched from the Skittles table.

A further refinement would be to specify a specific bag of skittles, and to always get the record, even if none of the colours matched. For example: sour with blue doesn't appear as a combination in my data above. I would still want to get the top of the graph which is an instance of BagOfSkittles.

[smitpatel]

...how do I ensure I always get the matched BagOfSkittles, while filtering the Skittles that belong to it by colour. Even when there are no related Skittles that match.

This statement is ambiguous, It does not clarify if you want BagOfSkittles which does not have any matching Skittles.

If you are trying to get all BagOfSkittles with Skittles navigation property populated without any condition then
context.BagOfSkittles.Include(b => b.Skittles)

If you want to have only those BagOfSkittles which have at least 1 Skittle which satisfy condition then
context.BagOfSkittles.Where(b => b.Skittles.Any(condition))

If you want all BagOfSkittles but want to load only Skittles in memory which satisfy the condition then it is filtered include which is being tracked by #1833

[atrauzzi]

Yeah, definitely the third. The rest seems obvious otherwise. :)

[haythem]

@smitpatel, it seems that the second scenario is not working when using an array inside the .Any

var skittleNames = new string[] { "Lemon", "Orange", "Strawberry" };
context.BagOfSkittles.Where(b => b.Skittles.Any(s => skittleNames.Contains(s.Name)))

This will throw an exception since it cannot be translated to SQL

System.InvalidOperationException: The LINQ expression 'Any<Skittles>(
    source: Where<Skittle>(
        source: DbSet<Skittle>, 
        predicate: (u0) => Property<string>(EntityShaperExpression: 
            EntityType: Skittle
            ValueBufferExpression: 
                ProjectionBindingExpression: EmptyProjectionMember
            IsNullable: False
...

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Is there any alternative so this expression could be translated to SQL ?

Thanks in advance.

dotnet --info

.NET Core SDK (reflecting any global.json):
 Version:   3.0.100
 Commit:    04339c3a26

Runtime Environment:
 OS Name:     Mac OS X
 OS Version:  10.15
 OS Platform: Darwin
 RID:         osx.10.15-x64
 Base Path:   /usr/local/share/dotnet/sdk/3.0.100/

Host (useful for support):
  Version: 3.0.0
  Commit:  7d57652f33