FirstOrDefault() when retrieving a single field value

Question

This type of thing has always bugged me so I thought I'd seek out a "better way" from you smart people.

I have a query where I want to return the value of a single field, which is an int value.

int thivalue = (from q in context.tablename    
    where q.ID == id
    orderby q.fieldname descending
    select q).FirstOrDefault().fieldname;

Problem is the query might return no results in which case I want thisvalue to be 0.

Of course if there are no results I get an exception trying to access the nonexistent field. So it seems my choices are to a) return the row (which I don't need), so I can test for null and go from there, or b) wrap a try catch around it and set the value to 0 there, which seems kinda clunky.

I thought maybe DefaultIfEmpty() would help me but it seems not if what I want is to end up with just the single value.

So what is the proper way to do this? Am I just being stubborn about not wanting to return the entire row when I only need a single value from it?

Addendum: (if anyone is interested)

In his answer berkeleybross gave me two apparently equivalent choices. But only the second one gave me the correct result. Something seemed amiss with the OrderByDescending. Using Glimpse I looked at the query for each.

    var nextSequence =  db.PaneContents
                       .Where(q=>q.QuizPaneID == quizPaneId)
                       .OrderByDescending(q=>q.Sequence)
                       .Select (q=>q.Sequence)
                       .DefaultIfEmpty()
                       .First();

yielded this query:

SELECT 
CASE WHEN ([Limit1].[C1] IS NULL) THEN 0 ELSE [Limit1].[Sequence] END AS [C1]
FROM ( SELECT TOP (1) 
    [Project1].[Sequence] AS [Sequence], 
    [Project1].[C1] AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN  (SELECT 
        [Extent1].[Sequence] AS [Sequence], 
        cast(1 as tinyint) AS [C1]
        FROM [dbo].[PaneContents] AS [Extent1]
        WHERE [Extent1].[QuizPaneID] = 274 /* @p__linq__0 */ ) AS [Project1] ON 1 = 1
)  AS [Limit1]

whereas

var nextSequence = (from q in db.PaneContents
                                where q.QuizPaneID == quizPaneId
                                orderby q.Sequence descending
                                select q.Sequence).FirstOrDefault();

yielded this query:

SELECT TOP (1) 
[Project1].[Sequence] AS [Sequence]
FROM ( SELECT 
    [Extent1].[Sequence] AS [Sequence]
    FROM [dbo].[PaneContents] AS [Extent1]
    WHERE [Extent1].[QuizPaneID] = 274 /* @p__linq__0 */
)  AS [Project1]
ORDER BY [Project1].[Sequence] DESC

Looks like in the first example the OrderByDescending() got lost in translation somehow? Anyway for my purposes DefaultIfEmpty() was not needed and the second example is more straightforward. I just thought it odd that it didn't work (by 'didn't work' I mean it selected the wrong value because it didn't sort descending) because it looked like it should.

Answer 1

If I understand you correctly, you could try this:

int thisvalue = context.tablename
                       .Where(q => q.ID == id)
                       .OrderByDescending(q => q.fieldname)
                       .Select(q => q.fieldname)
                       .FirstOrDefault();

or

int thisvalue = (from q in context.tablename    
                 where q.ID == id
                 orderby q.fieldname descending
                 select q.fieldname).FirstOrDefault();

Selecting q.fieldname before calling FirstOrDefault() will make the SQL return just the first items fieldname (and not the entire first row).

---

Also, is q.ID unique? If so, then you don't need to order by fieldname and you could use SingleOrDefault instead of FirstOrDefault. Single ensures there is exactly one element whereas First ensures there is at least one. Single should be used to state your assumption it is unique for more self documenting code.

int thisvalue = context.tablename
                       .Where(q => q.ID == id)
                       .Select(q => q.fieldname)
                       .SingleOrDefault();

Answer 2

I would do something like this

int thisvalue = context.tablename
    .Where(q => q.ID == id)
    .OrderByDescending(q => q.fieldname)
    .ToList()
    .DefaultIfEmpty(new myObject {fieldname = 0})
    .FirstOrDefault().fieldname;