Last active
May 16, 2020 11:53
-
-
Save jasenf/da0dadff0deb9912d29b2820f79707e7 to your computer and use it in GitHub Desktop.
It's better to .Take() - 1
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class Component | |
{ | |
public int Id { get; set; } | |
public virtual ComponentStatus LatestComponentStatus { get; set; } | |
} | |
public class ComponentStatus { | |
public int Id { get; set; } | |
public DateChanged { get; set; } | |
public StatusEnum Status { get; set; } | |
public in ComponentId { get; set; } | |
} |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var query = from c in Components | |
select new { | |
component=c, | |
componentstatus=(from cs in ComponentStatus | |
where cs.ComponentId==c.Id && cs.DateChanged<DateTime.UtcNow | |
select cs).OrderBy(cs=>cs.Id).FirstOrDefault() | |
}; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT [s].[Id], [t0].[ID], [t0].[ComponentId], [t0].[DateChanged], [t0].[Status] | |
FROM [Component] AS [s] | |
LEFT JOIN ( | |
SELECT [t].[ID], [t].[ComponentId], [t].[DateChanged], [t].[Status] | |
FROM ( | |
SELECT [c].[ID], [c].[ComponentId], [c].[DateChanged], [c].[Status], ROW_NUMBER() OVER(PARTITION BY [c].[ComponentId] ORDER BY [c].[ID]) AS [row] | |
FROM [ComponentStatus] AS [c] | |
WHERE [c].[DateChanged] < GETUTCDATE() | |
) AS [t] | |
WHERE [t].[row] <= 1 | |
) AS [t0] ON [s].[Id] = [t0].[ComponentId] |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var query = from c in Components | |
select new { | |
component=c, | |
componentstatus=(from cs in ComponentStatus | |
where cs.ComponentId==c.Id && cs.DateChanged<DateTime.UtcNow | |
select cs).OrderBy(cs=>cs.Id).Take(1).FirstOrDefault() | |
}; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT [s].[Id], [t0].[ID], [t0].[ComponentId], [t0].[DateChanged], [t0].[Status] | |
FROM [Component] AS [s] | |
OUTER APPLY ( | |
SELECT TOP(1) [t].[ID], [t].[ComponentId],[t].[DateChanged], [t].[Status] | |
FROM ( | |
SELECT TOP(1) [c].[ID], [c].[ComponentId], [c].[DateChanged], [c].[Status] | |
FROM [ComponentStatus] AS [c] | |
WHERE ([c].[ComponentId] = [s].[Id]) AND ([c].[DateChanged] < GETUTCDATE()) | |
ORDER BY [c].[ID] | |
) AS [t] | |
ORDER BY [t].[ID] | |
) AS [t0] |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var query1 = ComponentStatus | |
.Where(cs=>cs.DateChanged<DateTime.UtcNow) | |
.Select(cs=>cs.Id) | |
.OrderBy(cs=>cs) | |
.FirstOrDefault(); | |
var query2 = ComponentStatus | |
.Where(cs=>cs.DateChanged<DateTime.UtcNow) | |
.Select(cs=>cs.Id) | |
.OrderBy(cs=>cs) | |
.Take(1) | |
.FirstOrDefault(); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT TOP(1) [c].[ID] | |
FROM [ComponentStatus] AS [c] | |
WHERE [c].[DateChanged] < GETUTCDATE() | |
ORDER BY [c].[ID] | |
GO | |
SELECT TOP(1) [t].[ID] | |
FROM ( | |
SELECT TOP(1) [c].[ID] | |
FROM [ComponentStatus] AS [c] | |
WHERE [c].[DateChanged] < GETUTCDATE() | |
ORDER BY [c].[ID] | |
) AS [t] | |
ORDER BY [t].[ID] | |
GO |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var query = from c in Components | |
select new { | |
component=c, | |
componentstatus=(from cs in ComponentStatus | |
where cs.ComponentId==c.Id && cs.DateChanged<DateTime.UtcNow | |
select cs).OrderBy(cs=>cs.Id).Take(1).FirstOrDefault() | |
}; | |
var results = await query.ToListAsync(); | |
results.forEach(x=>x.component.CurrentComponentStatus = x.componentstatus); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var query5 = from c in Components | |
select new Component { | |
Id=c.Id, | |
ComponentStatus=(from cs in ComponentStatus | |
where cs.ComponentId==c.Id && cs.DateChanged<DateTime.UtcNow | |
select new { cs.Status, cs.Id }).OrderBy(cs=>cs.Id).Take(1).FirstOrDefault() | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment