Skip to content

Instantly share code, notes, and snippets.

@jasenf
Last active May 16, 2020 11:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jasenf/da0dadff0deb9912d29b2820f79707e7 to your computer and use it in GitHub Desktop.
Save jasenf/da0dadff0deb9912d29b2820f79707e7 to your computer and use it in GitHub Desktop.
It's better to .Take() - 1
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; }
}
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()
};
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]
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()
};
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]
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();
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
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);
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