C# Error: Sequence contains no element

C# Error: Sequence contains no element

This error happen when I was trying to call Single() command like this:
Held holdData = new HeldBusinessLogic().Get(id: cId).Single();

Since there was no record in the Held table, the Single() command results in error: “Sequence contains no elements”

To fix the issue I had to change the code to:
Held holdData = new HeldBusinessLogic().Get(id: cId).FirstOrDefault();

The FirstOrDefault() returns null when there is no record whereas Single() tries to retrieve an element from the sequence

This error can also result when you attempt to execute any aggregate method on an empty result set.

Example
int maxData = context.Surface.Max(s => s.Surface.GetValueOrDefault());

To fix this:
int maxData = context.Surface==null?0: context.Surface.Max(s => s.Surface.GetValueOrDefault());

Another Filter example

Error
claim.Held.First(c => c.ClaimId == claimId);
Solution
claim.Held.FirstOrDefault(c => c.ClaimId == claimId);

However this can happen with the following commands also:
First() – use first when you know the sequence will have at least one element
FirstAsync() – Asynchronously returns the first element of a sequence.
SingleAsync() – Asynchronously returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.
Last() – Returns the last element of a sequence.
LastOrDefault() – Returns the last element of a sequence, or a default value if the sequence contains no elements.
LastAsync()

Using ROW_NUMBER() with MS SQL SERVER

The introduction of ROW_NUMBER() function with SQL SERVER 2005 solves the problem of calculating row numbers. This is one of the exciting feature and eliminates the problem of creating additional temp tables to achieve the result.

ROW_NUMBER function generates unique incrementing integers from 1 and on. They are only allowed in the SELECT and ORDER BY clauses of a query and not allowed in the query WHERE clause.

Syntax:

ROW_NUMBER ( )
 OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Basics:

  • OVER – You define a set of rows for the function to work with per each underlying row or when using empty parentheses, the OVER clause represents the entire underlying query’s result set
  • PARTITION BY – The argument to partition  the query result set by the column name, the row number will reset based on the value changing in the columns supplied. It is like the using the GROUP BY clause
  • ORDER BY – orders the rows in each partition by the column name

Coding Example

Simple Table

AnimalID Name Type
100 Camel Mammal
101 Turkey Bird
102 Cat Mammal
103 Deer Mammal
104 Pigeon Bird
105 Duck Bird
Query:
SELECT AnimalID, Name, Type, ROW_NUMBER() OVER (Order By Name) as RowID
FROM Animal

Result:
AnimalID Name Type RowID
100 Camel Mammal 1
102 Cat Mammal 2
103 Deer Mammal 3
105 Duck Bird 4
104 Pigeon Bird 5
101 Turkey Bird 6
Query: 
SELECT AnimalID, Name, Type, ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Name) as RowID 
FROM Animal
ORDER BY Type, Name

Result:
AnimalID Name Type RowID
105 Duck Bird 1
104 Pigeon Bird 2
101 Turkey Bird 3
100 Camel Mammal 1
102 Cat Mammal 2
103 Deer Mammal 3

Difference between ROW_NUMBER(), RANK(), DENSE_RANK()

  • ROW_NUMBER(): This one generates a new row number for every row, regardless of duplicates within a partition.
  • RANK(): This one generates a new row number for every distinct row, leaving gaps between groups of duplicates within a partition.
  • DENSE_RANK(): This one generates a new row number for every distinct row, leaving no gaps between groups of duplicates within a partition.

Coding Example

WITH T(StyleID, ID)
     AS (SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,2)
SELECT *,
       RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS 'RANK',
       ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',
       DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'
FROM   T

 

StyleID ID RANK ROW_NUMBER DENSE_RANK
 1  1  1  1  1
 1 1  1  2  1
 1  1  1  3  1
 1  2  4  4  2

Filtering using the ROW_NUMBER()

As mentioned earlier in the post that you cannot have ROW_NUMBER() in the WHERE clause of your sql query but if you want to filter the query using the ROW_NUMBER() there is a workaround to create a nested query. Such as:

Select from ( Select row_number() over (partition by x order by x) SrNo,* from x )A where A.SrNo=1

Issues with ROW_NUMBER()

While it is exciting to use ROW_NUMBER() function but recently in one of my query it hurt my performance almost by 3 fold. ROW_NUMBER() are faster when using with simple queries with large number of rows. In my experience I was joining several tables with INNER JOIN and LEFT JOINS it cost me too much on performance. To solve this issue, I had to declare a temporary table and insert all the records and used temp tables to query.

Performance Result

Description CPU Time Elapsed Time Rows
Using ROW_NUMBER() 32141 ms 32608 ms 837
Using Temp Table 1169 ms 1314 ms 837

 

Happy Quering!!!