Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why SingleOrDefault result TOP(2) in SQL?

I am using EF4.0, and I wrote a query:

var query = context.Post.Where(p => p.Id == postId).SingleOrDefault(); 

I need only One post from this query. I thought SingleOrDefault() will generate "SELECT TOP(1) ...", but when I look into SQL Profiler, It was:

exec sp_executesql N'SELECT TOP (2)  [Extent1].[Id] AS [Id],  [Extent1].[Title] AS [Title],  [Extent1].[Slug] AS [Slug],  [Extent1].[PubDate] AS [PubDate],  [Extent1].[PostContent] AS [PostContent],  [Extent1].[Author] AS [Author],  [Extent1].[CommentEnabled] AS [CommentEnabled],  [Extent1].[AttachmentId] AS [AttachmentId],  [Extent1].[IsPublished] AS [IsPublished],  [Extent1].[Hits] AS [Hits],  [Extent1].[CategoryId] AS [CategoryId] FROM [dbo].[Post] AS [Extent1] WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 uniqueidentifier',@p__linq__0='ECD9F3BE-3CA9-462E-AE79-2B28C8A16E32' 

I wonder why EF result in SELECT TOP (2)? I only need one post.

like image 843
Edi Wang Avatar asked Aug 07 '12 04:08

Edi Wang


People also ask

Does SingleOrDefault throw exception?

SingleOrDefault() throws an exception on more than one element.

Which is better SingleOrDefault or FirstOrDefault?

When you want a default value is returned if the result set contains no record, use SingleOrDefault. When you always want one record no matter what the result set contains, use First or FirstOrDefault. When you want a default value if the result set contains no record, use FirstOrDefault.

What is difference between single and SingleOrDefault in Linq?

Single : It returns a single specific element from a collection of elements if element match found. An exception is thrown, if none or more than one match found for that element in the collection. SingleOrDefault: It returns a single specific element from a collection of elements if element match found.

What is the difference between FirstOrDefault () and SingleOrDefault () extension method in Linq?

SingleOrDefault() Vs. FirstOrDefault() in LINQ QuerySingleOrDefault() – Same as Single(), but it can handle the null value. First() - There is at least one result, an exception is thrown if no result is returned. FirstOrDefault() - Same as First(), but not thrown any exception or return null when there is no result.


1 Answers

It selects top 2 so that if there are actually 2 or more than 2 records in the database, an exception would be thrown. If it only selects top 1 there would be no way to error out.

like image 132
Todd Li Avatar answered Sep 19 '22 17:09

Todd Li