How can I convert this SQL-Query to a LINQ-Query in EF Core?

Question

I want to translate the following SQL-Query into an EFCore LINQ-Query

SELECT t.*
FROM Trend t
LEFT JOIN Trend t1 
    on  t1.ArticleId = t.ArticleId
    and t1.Country = t.Country
    and t1.Created > t.Created
WHERE t1.ArticleId is null

So what I've got is this

from t1 in ctx.Trend
join t2 in ctx.Trend
    on new 
    { 
        x1 = t1.ArticleId, 
        x2 = t1.Country
    }
    equals new 
    { 
        x1 = t2.ArticleId, 
        x2 = t2.Country
    }
where t2.ArticleId == null
   && t1.Created > t2.Created 
select new { t1, t2 };

Which translates to

SELECT [t].[Id], [t].[Country], [t].[Created], [t].[ArticleId], [t].[Price], [t0].[Id], [t0].[Country], [t0].[Created], [t0].[ArticleId], [t0].[Price]
FROM [Trend] AS [t]
INNER JOIN [Trend] AS [t0] ON ([t].[ArticleId] = [t0].[ArticleId]) AND ((([t].[Country] = [t0].[Country]) AND ([t].[Country] IS NOT NULL AND [t0].[Country] IS NOT NULL)) OR ([t].[Country] IS NULL AND [t0].[Country] IS NULL))
WHERE CAST(0 AS bit) = CAST(1 AS bit)

I dont understand why there is a WHERE true = false condition.

Then I moved the t2.ArticleId == null condition up to ctx.Trend

from t1 in ctx.Trend
join t2 in ctx.Trend.Where(trend => trend.ArticleId == null)
    on new 
    { 
        x1 = t1.ArticleId, 
        x2 = t1.Country
    }
    equals new 
    { 
        x1 = t2.ArticleId, 
        x2 = t2.Country,
    }
where t1.Created > t2.Created 
select new { t1, t2 };

Which translates to

SELECT [t].[Id], [t].[Country], [t].[Created], [t].[Discount], [t].[ArticleId], [t].[Price], [t1].[Id], [t1].[Country], [t1].[Created], [t1].[Discount], [t1].[ArticleId], [t1].[Price]
FROM [Trend] AS [t]
INNER JOIN (
    SELECT [t0].[Id], [t0].[Country], [t0].[Created], [t0].[Discount], [t0].[ArticleId], [t0].[Price]
    FROM [Trend] AS [t0]
    WHERE CAST(0 AS bit) = CAST(1 AS bit)
) AS [t1] ON ([t].[ArticleId] = [t1].[ArticleId]) AND ((([t].[Country] = [t1].[Country]) AND ([t].[Country] IS NOT NULL AND [t1].[Country] IS NOT NULL)) OR ([t].[Country] IS NULL AND [t1].[Country] IS NULL))
WHERE [t].[Created] > [t1].[Created]

The query is based on this question for more context. But it should not be necessary.

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Feb 03 '20 at 21:47

score 0 · Answer 1 · answered Feb 04 '20 at 01:02

0

How about this?

from t1 in ctx.Trend
from t2 in ctx.Trend.Where(trend => trend.ArticleId == null && trend.ArticleId == t1.ArticleId && trend.Country == t1.Country && trend.Created > t1.Created).DefaultIfEmpty() 
select new { t1, t2 };

answered Feb 04 '20 at 01:02

Asherguru

1,687
1
5
10

Thanks for your answer. This poduces a weird outer apply `SELECT * FROM Trend AS t OUTER APPLY ( SELECT * FROM Trend AS t0 WHERE CAST(0 AS bit) = CAST(1 AS bit) ) AS t1` (slightly edited the query for readability) – boop Feb 05 '20 at 19:08

How can I convert this SQL-Query to a LINQ-Query in EF Core?

1 Answers1