.Net Framework Entity Framework: How to avoid inner select?
I am using Entity Framework for the following:
Get the latest row where some columns are equal to X and Y. The LINQ looks like this (I have also tried to rewrite it as "FROM WHERE" statement, among other things, to no avail):
var logEvent = Context.Set<Log>().Where(e =>
e.Id == id && e.MessageType == messageType)
.OrderByDescending(e => e.DateTime)
.FirstOrDefault();
I would expect this to generate the following:
SELECT TOP (1) *
FROM [dbo].[Log]
WHERE [Log].[Id] = @p__linq__0
AND [Log].[MessageType] = @p__linq__1
ORDER BY [Log].[DateTime] DESC
Instead it generates an inner Select that just iterates over the whole table, and the pick TOP 1 from the ordered rows.
(I have indexed DateTime and Id)
SELECT TOP (1) *
FROM ( SELECT *
FROM [dbo].[Log] AS [Extent1]
WHERE ([Extent1].[Id] = @p__linq__0)
AND ([Extent1].[MessageType] = @p__linq__1)
) AS [Project1]
ORDER BY [Project1].[DateTime] DESC
GO
What is causing this inner SELECT?
c# sql .net
sql-server entity-framework asked Nov 15 '18 at 13:12
NixxonNixxon
347416
|
show 1 more comment
I am using Entity Framework for the following:
Get the latest row where some columns are equal to X and Y. The LINQ looks like this (I have also tried to rewrite it as "FROM WHERE" statement, among other things, to no avail):
var logEvent = Context.Set<Log>().Where(e =>
e.Id == id && e.MessageType == messageType)
.OrderByDescending(e => e.DateTime)
.FirstOrDefault();
I would expect this to generate the following:
SELECT TOP (1) *
FROM [dbo].[Log]
WHERE [Log].[Id] = @p__linq__0
AND [Log].[MessageType] = @p__linq__1
ORDER BY [Log].[DateTime] DESC
Instead it generates an inner Select that just iterates over the whole table, and the pick TOP 1 from the ordered rows.
(I have indexed DateTime and Id)
SELECT TOP (1) *
FROM ( SELECT *
FROM [dbo].[Log] AS [Extent1]
WHERE ([Extent1].[Id] = @p__linq__0)
AND ([Extent1].[MessageType] = @p__linq__1)
) AS [Project1]
ORDER BY [Project1].[DateTime] DESC
GO
What is causing this inner SELECT?
c# sql .net
sql-server entity-framework asked Nov 15 '18 at 13:12
NixxonNixxon
347416
2
I would guess that it's how they chose to implement their linq-to-sql. It easily allows to select some specific columns and do more complex thing in your EF query, (that would be reflected in the inner select), while theselect top(1) *is just the translation of theFirstOrDefault. However, is there any actual issue you are experiencing ? Or is it just out of curiosity ? EF (non-core) is closed source, one might decompile and explain the code, but the root design reason might be info only available to the EF dev team.
– Pac0
Nov 15 '18 at 13:18
2
how did you get the query? is it using SQL Profiler? what happens if you reorder your query to beContext.Set<Log>().OrderByDescending(e => e.DateTime).FirstOrDefault(x => e.Id == id && e.MessageType == messageType)does it result the same query? Entit Frameworks aims to make query easier to be written on code.. though it may sacrifice performance on some cases.
– Bagus Tesa
Nov 15 '18 at 13:18
2
In SQL, you tell the system what you want, not how to do it. Especially, the system isn't stupid enough to "iterates over the whole table" of the subselect before it limits itself to one row. We'd expect both of your SQL queries to generate near-identical execution plans.
– Damien_The_Unbeliever
Nov 15 '18 at 13:20
exactly as Damin_The_Unbeliever said, there is no problem per se with this request. That is just not how a human would have written it, but apart from that you have nothing to worry. (Well... you will worry when you see really bad-performing EF-generated request, but this is not one of them ;) )
– Pac0
Nov 15 '18 at 13:22
(please share the SQL-Server execution plan for further discussion on how the query is performing)
– Pac0
Nov 15 '18 at 13:24
|
show 1 more comment
I am using Entity Framework for the following:
Get the latest row where some columns are equal to X and Y. The LINQ looks like this (I have also tried to rewrite it as "FROM WHERE" statement, among other things, to no avail):
var logEvent = Context.Set<Log>().Where(e =>
e.Id == id && e.MessageType == messageType)
.OrderByDescending(e => e.DateTime)
.FirstOrDefault();
I would expect this to generate the following:
SELECT TOP (1) *
FROM [dbo].[Log]
WHERE [Log].[Id] = @p__linq__0
AND [Log].[MessageType] = @p__linq__1
ORDER BY [Log].[DateTime] DESC
Instead it generates an inner Select that just iterates over the whole table, and the pick TOP 1 from the ordered rows.
(I have indexed DateTime and Id)
SELECT TOP (1) *
FROM ( SELECT *
FROM [dbo].[Log] AS [Extent1]
WHERE ([Extent1].[Id] = @p__linq__0)
AND ([Extent1].[MessageType] = @p__linq__1)
) AS [Project1]
ORDER BY [Project1].[DateTime] DESC
GO
What is causing this inner SELECT?
c# sql .net
sql-server entity-framework asked Nov 15 '18 at 13:12
NixxonNixxon
347416
I am using Entity Framework for the following:
Get the latest row where some columns are equal to X and Y. The LINQ looks like this (I have also tried to rewrite it as "FROM WHERE" statement, among other things, to no avail):
var logEvent = Context.Set<Log>().Where(e =>
e.Id == id && e.MessageType == messageType)
.OrderByDescending(e => e.DateTime)
.FirstOrDefault();
I would expect this to generate the following:
SELECT TOP (1) *
FROM [dbo].[Log]
WHERE [Log].[Id] = @p__linq__0
AND [Log].[MessageType] = @p__linq__1
ORDER BY [Log].[DateTime] DESC
Instead it generates an inner Select that just iterates over the whole table, and the pick TOP 1 from the ordered rows.
(I have indexed DateTime and Id)
SELECT TOP (1) *
FROM ( SELECT *
FROM [dbo].[Log] AS [Extent1]
WHERE ([Extent1].[Id] = @p__linq__0)
AND ([Extent1].[MessageType] = @p__linq__1)
) AS [Project1]
ORDER BY [Project1].[DateTime] DESC
GO
What is causing this inner SELECT?
c# sql .net
sql-server entity-framework c# sql .net
sql-server entity-framework asked Nov 15 '18 at 13:12
NixxonNixxon
347416
asked Nov 15 '18 at 13:12
NixxonNixxon
347416
asked Nov 15 '18 at 13:12
NixxonNixxon
347416
asked Nov 15 '18 at 13:12
NixxonNixxon
347416
asked Nov 15 '18 at 13:12
NixxonNixxon
347416
347416
2
I would guess that it's how they chose to implement their linq-to-sql. It easily allows to select some specific columns and do more complex thing in your EF query, (that would be reflected in the inner select), while theselect top(1) *is just the translation of theFirstOrDefault. However, is there any actual issue you are experiencing ? Or is it just out of curiosity ? EF (non-core) is closed source, one might decompile and explain the code, but the root design reason might be info only available to the EF dev team.
– Pac0
Nov 15 '18 at 13:18
2
how did you get the query? is it using SQL Profiler? what happens if you reorder your query to beContext.Set<Log>().OrderByDescending(e => e.DateTime).FirstOrDefault(x => e.Id == id && e.MessageType == messageType)does it result the same query? Entit Frameworks aims to make query easier to be written on code.. though it may sacrifice performance on some cases.
– Bagus Tesa
Nov 15 '18 at 13:18
2
In SQL, you tell the system what you want, not how to do it. Especially, the system isn't stupid enough to "iterates over the whole table" of the subselect before it limits itself to one row. We'd expect both of your SQL queries to generate near-identical execution plans.
– Damien_The_Unbeliever
Nov 15 '18 at 13:20
exactly as Damin_The_Unbeliever said, there is no problem per se with this request. That is just not how a human would have written it, but apart from that you have nothing to worry. (Well... you will worry when you see really bad-performing EF-generated request, but this is not one of them ;) )
– Pac0
Nov 15 '18 at 13:22
(please share the SQL-Server execution plan for further discussion on how the query is performing)
– Pac0
Nov 15 '18 at 13:24
|
show 1 more comment
2
I would guess that it's how they chose to implement their linq-to-sql. It easily allows to select some specific columns and do more complex thing in your EF query, (that would be reflected in the inner select), while theselect top(1) *is just the translation of theFirstOrDefault. However, is there any actual issue you are experiencing ? Or is it just out of curiosity ? EF (non-core) is closed source, one might decompile and explain the code, but the root design reason might be info only available to the EF dev team.
– Pac0
Nov 15 '18 at 13:18
2
how did you get the query? is it using SQL Profiler? what happens if you reorder your query to beContext.Set<Log>().OrderByDescending(e => e.DateTime).FirstOrDefault(x => e.Id == id && e.MessageType == messageType)does it result the same query? Entit Frameworks aims to make query easier to be written on code.. though it may sacrifice performance on some cases.
– Bagus Tesa
Nov 15 '18 at 13:18
2
In SQL, you tell the system what you want, not how to do it. Especially, the system isn't stupid enough to "iterates over the whole table" of the subselect before it limits itself to one row. We'd expect both of your SQL queries to generate near-identical execution plans.
– Damien_The_Unbeliever
Nov 15 '18 at 13:20
exactly as Damin_The_Unbeliever said, there is no problem per se with this request. That is just not how a human would have written it, but apart from that you have nothing to worry. (Well... you will worry when you see really bad-performing EF-generated request, but this is not one of them ;) )
– Pac0
Nov 15 '18 at 13:22
(please share the SQL-Server execution plan for further discussion on how the query is performing)
– Pac0
Nov 15 '18 at 13:24
2
2
I would guess that it's how they chose to implement their linq-to-sql. It easily allows to select some specific columns and do more complex thing in your EF query, (that would be reflected in the inner select), while the
select top(1) * is just the translation of the FirstOrDefault. However, is there any actual issue you are experiencing ? Or is it just out of curiosity ? EF (non-core) is closed source, one might decompile and explain the code, but the root design reason might be info only available to the EF dev team.– Pac0
Nov 15 '18 at 13:18
I would guess that it's how they chose to implement their linq-to-sql. It easily allows to select some specific columns and do more complex thing in your EF query, (that would be reflected in the inner select), while the
select top(1) * is just the translation of the FirstOrDefault. However, is there any actual issue you are experiencing ? Or is it just out of curiosity ? EF (non-core) is closed source, one might decompile and explain the code, but the root design reason might be info only available to the EF dev team.– Pac0
Nov 15 '18 at 13:18
2
2
how did you get the query? is it using SQL Profiler? what happens if you reorder your query to be
Context.Set<Log>().OrderByDescending(e => e.DateTime).FirstOrDefault(x => e.Id == id && e.MessageType == messageType) does it result the same query? Entit Frameworks aims to make query easier to be written on code.. though it may sacrifice performance on some cases.– Bagus Tesa
Nov 15 '18 at 13:18
how did you get the query? is it using SQL Profiler? what happens if you reorder your query to be
Context.Set<Log>().OrderByDescending(e => e.DateTime).FirstOrDefault(x => e.Id == id && e.MessageType == messageType) does it result the same query? Entit Frameworks aims to make query easier to be written on code.. though it may sacrifice performance on some cases.– Bagus Tesa
Nov 15 '18 at 13:18
2
2
In SQL, you tell the system what you want, not how to do it. Especially, the system isn't stupid enough to "iterates over the whole table" of the subselect before it limits itself to one row. We'd expect both of your SQL queries to generate near-identical execution plans.
– Damien_The_Unbeliever
Nov 15 '18 at 13:20
In SQL, you tell the system what you want, not how to do it. Especially, the system isn't stupid enough to "iterates over the whole table" of the subselect before it limits itself to one row. We'd expect both of your SQL queries to generate near-identical execution plans.
– Damien_The_Unbeliever
Nov 15 '18 at 13:20
exactly as Damin_The_Unbeliever said, there is no problem per se with this request. That is just not how a human would have written it, but apart from that you have nothing to worry. (Well... you will worry when you see really bad-performing EF-generated request, but this is not one of them ;) )
– Pac0
Nov 15 '18 at 13:22
exactly as Damin_The_Unbeliever said, there is no problem per se with this request. That is just not how a human would have written it, but apart from that you have nothing to worry. (Well... you will worry when you see really bad-performing EF-generated request, but this is not one of them ;) )
– Pac0
Nov 15 '18 at 13:22
(please share the SQL-Server execution plan for further discussion on how the query is performing)
– Pac0
Nov 15 '18 at 13:24
(please share the SQL-Server execution plan for further discussion on how the query is performing)
– Pac0
Nov 15 '18 at 13:24
|
show 1 more comment
2 Answers
2
active
oldest
votes
FirstOrDefault() is the reason of translating LINQ query into SELECT TOP (1) FROM (another select)
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
add a comment |
@Bagus Tesa directed me to the correct answer. Thank you all.
Doing the OrderBy() FIRST was the solution.
The following LINQ query generates the expected query:
var logEvent = Context.Set<Log>()
.OrderByDescending(e => e.DateTime)
.FirstOrDefault(e => (e.Id == id) && e.MessageType == messageType));
I also tested in case I wanted more than 1 result row:
var importLogEvent1 = Context.Set<ImportLog>()
.OrderByDescending(e => e.DateTime)
.Where(e => (e.Id == id) && e.MessageType == messageType))
.Take(5);
This also generated a "clean" SQL query.
NOTE: As some have pointed out, the generated SQL query even though it looked "bad", might very well have resulted in a equally good execution plan. I did not look into this.
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53320291%2fnet-framework-entity-framework-how-to-avoid-inner-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
FirstOrDefault() is the reason of translating LINQ query into SELECT TOP (1) FROM (another select)
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
add a comment |
FirstOrDefault() is the reason of translating LINQ query into SELECT TOP (1) FROM (another select)
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
add a comment |
FirstOrDefault() is the reason of translating LINQ query into SELECT TOP (1) FROM (another select)
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
FirstOrDefault() is the reason of translating LINQ query into SELECT TOP (1) FROM (another select)
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
answered Nov 15 '18 at 13:19
Piotr RyszardPiotr Ryszard
213
213
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
add a comment |
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
Could you add some more information on this? (How, why, sources)
– HoneyBadger
Nov 15 '18 at 13:26
add a comment |
@Bagus Tesa directed me to the correct answer. Thank you all.
Doing the OrderBy() FIRST was the solution.
The following LINQ query generates the expected query:
var logEvent = Context.Set<Log>()
.OrderByDescending(e => e.DateTime)
.FirstOrDefault(e => (e.Id == id) && e.MessageType == messageType));
I also tested in case I wanted more than 1 result row:
var importLogEvent1 = Context.Set<ImportLog>()
.OrderByDescending(e => e.DateTime)
.Where(e => (e.Id == id) && e.MessageType == messageType))
.Take(5);
This also generated a "clean" SQL query.
NOTE: As some have pointed out, the generated SQL query even though it looked "bad", might very well have resulted in a equally good execution plan. I did not look into this.
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
add a comment |
@Bagus Tesa directed me to the correct answer. Thank you all.
Doing the OrderBy() FIRST was the solution.
The following LINQ query generates the expected query:
var logEvent = Context.Set<Log>()
.OrderByDescending(e => e.DateTime)
.FirstOrDefault(e => (e.Id == id) && e.MessageType == messageType));
I also tested in case I wanted more than 1 result row:
var importLogEvent1 = Context.Set<ImportLog>()
.OrderByDescending(e => e.DateTime)
.Where(e => (e.Id == id) && e.MessageType == messageType))
.Take(5);
This also generated a "clean" SQL query.
NOTE: As some have pointed out, the generated SQL query even though it looked "bad", might very well have resulted in a equally good execution plan. I did not look into this.
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
add a comment |
@Bagus Tesa directed me to the correct answer. Thank you all.
Doing the OrderBy() FIRST was the solution.
The following LINQ query generates the expected query:
var logEvent = Context.Set<Log>()
.OrderByDescending(e => e.DateTime)
.FirstOrDefault(e => (e.Id == id) && e.MessageType == messageType));
I also tested in case I wanted more than 1 result row:
var importLogEvent1 = Context.Set<ImportLog>()
.OrderByDescending(e => e.DateTime)
.Where(e => (e.Id == id) && e.MessageType == messageType))
.Take(5);
This also generated a "clean" SQL query.
NOTE: As some have pointed out, the generated SQL query even though it looked "bad", might very well have resulted in a equally good execution plan. I did not look into this.
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
@Bagus Tesa directed me to the correct answer. Thank you all.
Doing the OrderBy() FIRST was the solution.
The following LINQ query generates the expected query:
var logEvent = Context.Set<Log>()
.OrderByDescending(e => e.DateTime)
.FirstOrDefault(e => (e.Id == id) && e.MessageType == messageType));
I also tested in case I wanted more than 1 result row:
var importLogEvent1 = Context.Set<ImportLog>()
.OrderByDescending(e => e.DateTime)
.Where(e => (e.Id == id) && e.MessageType == messageType))
.Take(5);
This also generated a "clean" SQL query.
NOTE: As some have pointed out, the generated SQL query even though it looked "bad", might very well have resulted in a equally good execution plan. I did not look into this.
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
answered Nov 16 '18 at 10:07
NixxonNixxon
347416
347416
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53320291%2fnet-framework-entity-framework-how-to-avoid-inner-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
I would guess that it's how they chose to implement their linq-to-sql. It easily allows to select some specific columns and do more complex thing in your EF query, (that would be reflected in the inner select), while the
select top(1) *is just the translation of theFirstOrDefault. However, is there any actual issue you are experiencing ? Or is it just out of curiosity ? EF (non-core) is closed source, one might decompile and explain the code, but the root design reason might be info only available to the EF dev team.– Pac0
Nov 15 '18 at 13:18
2
how did you get the query? is it using SQL Profiler? what happens if you reorder your query to be
Context.Set<Log>().OrderByDescending(e => e.DateTime).FirstOrDefault(x => e.Id == id && e.MessageType == messageType)does it result the same query? Entit Frameworks aims to make query easier to be written on code.. though it may sacrifice performance on some cases.– Bagus Tesa
Nov 15 '18 at 13:18
2
In SQL, you tell the system what you want, not how to do it. Especially, the system isn't stupid enough to "iterates over the whole table" of the subselect before it limits itself to one row. We'd expect both of your SQL queries to generate near-identical execution plans.
– Damien_The_Unbeliever
Nov 15 '18 at 13:20
exactly as Damin_The_Unbeliever said, there is no problem per se with this request. That is just not how a human would have written it, but apart from that you have nothing to worry. (Well... you will worry when you see really bad-performing EF-generated request, but this is not one of them ;) )
– Pac0
Nov 15 '18 at 13:22
(please share the SQL-Server execution plan for further discussion on how the query is performing)
– Pac0
Nov 15 '18 at 13:24