I need next SQL query:
SELECT [t1].[monthp], [t1].[yearp], [t1].[coeftarif]
FROM [dbo].[sal_hightariff] AS [t0]
INNER JOIN [dbo].[sal_tarifstav] AS [t1] ON ((
SELECT TOP (1) [t3].[code_no]
FROM [dbo].[sal_tarifstav] AS [t3]
WHERE ([t3].[codetnum] = @codetnum) AND ([t3].[yearp] = [t0].[yearp]) AND ([t3].[monthp] = [t0].[monthp])
ORDER BY [t3].[datestart]
)) = [t1].[code_no]
WHERE [t0].[yearp] * 12 + [t0].[monthp] >= @value1 AND [t0].[yearp] * 12 + [t0].[monthp] <= value2
So, I write this code:
var coeflist = from high in dbContext.sal_hightariff
join stav in dbContext.sal_tarifstav on (from tarifstav in dbContext.sal_tarifstav
where tarifstav.codetnum == vacationcodetnum
&& tarifstav.yearp == high.yearp
&& tarifstav.monthp == high.monthp
orderby tarifstav.datestart
select tarifstav.code_no).FirstOrDefault() equals stav.code_no
where high.yearp * 12 + high.monthp >= minday.Year * 12 + minday.Month
&& high.yearp * 12 + high.monthp <= vacationdatestart.Year
* 12 + vacationdatestart.Month
select new
{
stav.monthp,
stav.yearp,
stav.coeftarif
};
But my code generates:
SELECT [t1].[monthp], [t1].[yearp], [t1].[coeftarif]
FROM [dbo].[sal_hightariff] AS [t0]
INNER JOIN [dbo].[sal_tarifstav] AS [t1] ON ((
SELECT TOP (1) [t3].[code_no]
FROM (
SELECT [t2].[code_no], [t2].[codetnum], [t2].[yearp], [t2].[monthp]
FROM [dbo].[sal_tarifstav] AS [t2]
ORDER BY [t2].[datestart]
) AS [t3]
WHERE ([t3].[codetnum] = @p0) AND ([t3].[yearp] = [t0].[yearp]) AND ([t3].[monthp] = [t0].[monthp])
)) = [t1].[code_no]
WHERE ((((CONVERT(Int,[t0].[yearp])) * @p1) + (CONVERT(Int,[t0].[monthp]))) >= @p2)
AND ((((CONVERT(Int,[t0].[yearp])) * @p3) + (CONVERT(Int,[t0].[monthp]))) <= @p4)
In which there is an error:
Msg 1033, Level 15, State 1, Line 13 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
So, if I use methods FirstorDefault() or Take(1) in subquery, LINQ generates SELECT TOP (1) FROM SELECT ... But I need TOP (1) inside subquery to use ORDER BY statement.