Linq到实体-一对多关系-需要左外部联接而不是交叉联接 [英] Linq to entities - One to many relationship - need left outer join instead of cross join

问题描述

我有以下模型:

我需要特定用户的所有固定装置和预测(如果该固定装置具有预测)的列表.返回我需要的sql如下:

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

我尝试过:

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]

将DefaultIfEmpty添加到第二个"from"中，例如:

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
   };

生成:

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为您生成了导航属性，请继续使用它们！

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

代替

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)

尝试

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)

请注意，使用join和DefaultIfEmpty通常是一个错误EF .

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

这篇关于Linq到实体-一对多关系-需要左外部联接而不是交叉联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！