Linq to entities - One to many relationship - need left outer join instead of cross join

I have the following model:

I need a list of all fixtures and predictions (if that fixture has a prediction) for a specific user. The sql to return what I need is as follows:

SELECT * FROM Fixture f
LEFT OUTER JOIN Prediction p ON f.FixtureId = p.FixtureId
WHERE p.UserID = '06E4D3E0-8365-45BF-9054-3F8534C7AD5E' OR p.UserID IS NULL

I have tried:

var query = from f in c.Fixtures
    from p in c.Predictions.Where(pre => pre.UserId == new Guid("06E4D3E0-8365-45BF-9054-3F8534C7AD5E") || pre.UserId == null)
    select new
    {
        FixtureId = f.开发者_运维知识库FixtureId,
        HomeScore = f.HomeTeamScore,
        AwayScore = f.AwayTeamScore,
        PredictionId = p.PredictionId,
        HomePrediction = p.HomeTeamPrediction,
        AwayPrediction = p.AwayTeamPrediction
        };

But that generates (and gives the wrong results):

SELECT 
[Extent1].[FixtureId] AS [FixtureId], 
[Extent1].[HomeTeamScore] AS [HomeTeamScore], 
[Extent1].[AwayTeamScore] AS [AwayTeamScore], 
[Extent2].[PredictionId] AS [PredictionId], 
[Extent2].[HomeTeamPrediction] AS [HomeTeamPrediction], 
[Extent2].[AwayTeamPrediction] AS [AwayTeamPrediction]
FROM  [dbo].[Fixture] AS [Extent1]
CROSS JOIN [dbo].[Prediction] AS [Extent2]
WHERE cast('06e4d3e0-8365-45bf-9054-3f8534c7ad5e' as uniqueidentifier) = [Extent2].[UserId]

Adding DefaultIfEmpty to the second 'from' like:

var query = from f in c.Fixtures
    from p in c.Predictions.Where(pre => pre.UserId == new Guid("06E4D3E0-8365-45BF-9054-3F8534C7AD5E") || pre.UserId == null).DefaultIfEmpty()
    select new
    {
        FixtureId = f.FixtureId,
        HomeScore = f.HomeTeamScore,
        AwayScore = f.AwayTeamScore,
        PredictionId = p.PredictionId,
        HomePrediction = p.HomeTeamPrediction,
        AwayPrediction = p.AwayTeamPrediction
    };

Generates (and still gives the wrong results):

SELECT 
[Extent1].[FixtureId] AS [FixtureId], 
[Extent1].[HomeTeamScore] AS [HomeTeamScore], 
[Extent1].[AwayTeamScore] AS [AwayTeamScore], 
[Join1].[PredictionId] AS [PredictionId], 
[Join1].[HomeTeamPrediction] AS [HomeTeamPrediction], 
[Join1].[AwayTeamPrediction] AS [AwayTeamPrediction]
FROM  [dbo].[Fixture] AS [Extent1]
CROSS JOIN  (SELECT [Project1].[PredictionId] AS [PredictionId], [Project1].[HomeTeamPrediction] AS [HomeTeamPrediction], [Project1].[AwayTeamPrediction] AS [AwayTeamPrediction]
FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT 
    [Extent2].[PredictionId] AS [PredictionId], 
    [Extent2].[UserId] AS [UserId], 
    [Extent2].[HomeTeamPrediction] AS [HomeTeamPrediction], 
    [Extent2].[AwayTeamPrediction] AS [AwayTeamPrediction]
    FROM [dbo].[Prediction] AS [Extent2]
    WHERE cast('06e4d3e0-8365-45bf-9054-3f8534c7ad5e' as uniqueidentifier) = [Extent2].[UserId] ) AS [Project1] ON 1 = 1 ) AS [Join1]

Using the existing relationship as-is like (this was where I was going wrong, see answer below):

var query = from f in c.Fixtures
   from p in c.Predictions
   where c.Predictions.Any(pre => pre.UserId == new Guid("06E4D3E0-8365-45BF-9054-3F8534C7AD5E") || pre.UserId == null)
   select new
   {
      FixtureId = f.FixtureId,
      HomeScore = f.HomeTeamScore,
      AwayScore = f.AwayTeamScore,
      PredictionId = p.PredictionId,
      HomePrediction = p.HomeTeamPrediction,
      AwayPrediction = p.AwayTeamPrediction
   };

Generates:

SELECT 
[Extent1].[FixtureId] AS [FixtureId], 
[Extent1].[HomeTeamScore] AS [HomeTeamScore], 
[Extent1].[AwayTeamScore] AS [AwayTeamScore], 
[Extent2].[PredictionId] AS [PredictionId], 
[Extent2].[HomeTeamPrediction] AS [HomeTeamPrediction], 
[Extent2].[AwayTeamPrediction] AS [AwayTeamPrediction]
FROM  [dbo].[Fixture] AS [Extent1]
CROSS JOIN [dbo].[Prediction] AS [Extent2]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Prediction] AS [Extent3]
    WHERE cast('06e4d3e0-8365-45bf-9054-3f8534c7ad5e' as uniqueidentifier) = [Extent3].[UserId]
)

How do generate the query I need?

---

EF has generated navigation properties for you, so go ahead and use them!

Instead of

var query = 
    from f in c.Fixtures
    from p in c.Predictions
        .Where(pre => pre.UserId == new Guid("06E4D3E0-8365-45BF-9054-3F8534C7AD5E") 
            || pre.UserId == null)

try

var query = 
    from f in c.Fixtures
    from p in f.Predictions
        .Where(pre => pre.UserId == new Guid("06E4D3E0-8365-45BF-9054-3F8534C7AD5E") 
            || pre.UserId == null)

Note that using join and DefaultIfEmpty is often a mistake in EF.