Entity Framework Core LINQ trouble creating (select case exists) queries

I'm trying to list all items with an extra column describing whether it's owned by the current user.

So I'm looking for a Linq query that generates something like the following SQL:

SELECT *,
   CASE WHEN
     EXISTS (
       SELECT NULL FROM OwnedItems
       WHERE OwnedItems.UserId = @UserId AND OwnedItems.ItemId = Items.Id
   )
   THEN 'true'
   ELSE 'false'
   END AS Owned
FROM Items;

According to the internet as well as a successful LinqPad experiment this code should work.

from item in Items
select new
{
   Owned = OwnedItems.Any(own => own.UserId == userId && own.ItemId == item.Id),
   Item = item
}

In LinqPad this code generates the exact same SQL as I want. But in my project it does something completely different.

My code is a .Net Core 2.1 project using Entity Framework Core 2.1. Since it is a Core project I can't directly test it in LinqPad as it isn't supported yet.

In my project this code results in an unfiltered SELECT statement querying every Item, then for each of them a separate query to check if it exists in the OwnedItems table. Like this:

1 instance of this query runs:

Executed DbCommand (68ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
  SELECT *
  FROM [Items] AS [item]

Followed by hundreds of these queries taking multiple seconds to run:

 Executed DbCommand (32ms) [Parameters=[@__userId_0='?' (DbType = Int32), @_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
  SELECT CASE
      WHEN EXISTS (
          SELECT 1
          FROM [OwnedItems] AS [ownedItems]
          WHERE ([ownedItems].[UserId] = @__userId_0) AND ([ownedItems].[ItemId] = @_outer_Id))
      THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
  END

Some further info, maybe it helps: If I use the same line as part of a where clause, it works perfectly.

var q = from item in Items
    where OwnedItems.Any(o => o.UserId == userId && o.ItemId == item.Id)
    select item;

The above linq results in this nice sql:

SELECT *
  FROM [Items] AS [item]
  WHERE EXISTS (
      SELECT 1
      FROM [OwnedItems] AS [o]
      WHERE ([o].[UserId] = @__userId_0) AND ([o].[ItemId] = [item].[Id]))

Notes:

The above code has been mangled manually so there might be typos in there. Please disregard them.
I understand that this particular query can be done using a left join and checking for nulls but my actual one is more complex and I need (nested) exists clauses.

UPDATE FOR SOLUTION

As @KorsG pointed out if the Item isn't materialized, the proper query is generated. What I found is that not materializing Item works even if I write the following:

from item in Items
select new
{
   Owned = OwnedItems.Any(own => own.UserId == userId && own.ItemId == item.Id),
   // Item = item  //THIS LINE GENERATES BAD QUERY
   Item = new Item {
       Id = item.Id,
       Name = item.Name,
       ...
       [Literally every single property listed one by one] = item.CorrespondingProperty
       ...
   }
}

So I can actually materialize the full item, I just have to explicitly type every last property. FUN!

Solution

You probably need to enable eager loading for the "OwnedItems" navigation property in the query: https://learn.microsoft.com/en-us/ef/core/querying/related-data#eager-loading

Please post your full linq query if I should give an example.

UPDATE 1

Seems like subqueries have N+1 problems in EF Core and it will maybe be fixed in version 3.

Reference: https://github.com/aspnet/EntityFrameworkCore/issues/10001

UPDATE 2

If you don't need to fully materialize "Items" you should be able to do something like this where you create an anoymous object instead which should "trick" EF into what you want:

from item in Items
select new
{
   Owned = OwnedItems.Any(own => own.UserId == userId && own.ItemId == item.Id),
   Item = new { Id = item.Id, Name = item.Name }
}

Reference: https://github.com/aspnet/EntityFrameworkCore/issues/11186