У меня есть следующая модель:
Мне нужен список всех приборов и прогнозов (если у этого прибора есть прогноз) для конкретного пользователя. SQL для возврата того, что мне нужно, выглядит следующим образом:
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
};
Но это генерирует (и дает неправильные результаты):
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
ко второму «от», например:
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
};
Генерирует (и все еще дает неправильные результаты):
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]
Используя существующие отношения как есть (здесь я ошибся, см. ответ ниже):
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]
)
Как сгенерировать нужный мне запрос?
DefaultIfEmpty
? Вы можете показать нам весь запрос? - person Piotr Auguscik   schedule 20.09.2011DefaultIfEmpty
использование. - person Sprintstar   schedule 20.09.2011