Is there a way to have a default when a sequence contains no elements when using .first with LINQ?

Question

I am using the following code:

public class BtnCountViews
{
    public int btnCount { get; set; }
    public int views { get; set; }
}

var btns = "(BTNACOUNT + BTNBCOUNT) AS BtnCount, VIEWS As Views";
Counts.btnCountViewsToday = 
   db2.Query<BtnCountViews>("SELECT " + btns + " FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).First();

When there is no record returned for the day yymmdd then this errors out with a message saying sequence contains no elements.

I do understand why this is happening but would like some suggestions on how I could stop it throwing an exception.

What I would like to do if there are no elements is for Counts.btnCountViewsToday to contain an object populated with a 0 in the btnCount and the views fields.

Aside from anything else, the *first* thing to do is start using parameterized SQL. — Jon Skeet, Dec 07 '17 at 07:15
You mean like `FirstOrDefault()`? This returns `null` if the sequence is empty. If you have a specific non-null default element in mind, you can do `query.FirstOrDefault() ?? yourDefaultObject` — René Vogt, Dec 07 '17 at 07:16
Can you give an example of how I could use the ?? here to return an object with a 0 in both of the fields. — Alan2, Dec 07 '17 at 07:19
@RenéVogt gave you an example in his comment `var count = query.FirstOrDefault() ?? 0;` — Ashley Pillay, Dec 07 '17 at 07:24
So instead of the 0 in this case could I just put something like new BtnCountViews() { btncount = 0, views = 0 } or just new BtnCountViews() ? I think an example as an answer would be a good help for others. Also I'm not really clear on the use of ?? so I need to look that up. — Alan2, Dec 07 '17 at 07:25

score 4 · Accepted Answer · edited Dec 07 '17 at 17:06

4

Try to use FirstOrDefault

 var btnCountViewsToday = 
   db2.Query<BtnCountViews>("SELECT " + btns +
    " FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).FirstOrDefault();

If you want to create a default object for possible null response you can use DefaultIfEmpty like this;

var btnCountViewsToday = 
   db2.Query<BtnCountViews>("SELECT " + btns + 
   " FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).
   DefaultIfEmpty(new BtnCountViews()).FirstOrDefault();

edited Dec 07 '17 at 17:06

Pranay Rana

175,020
35
237
263

answered Dec 07 '17 at 07:15

lucky

12,734
4
24
46

Change type for btnCountViewsToday from int to int? – jdweng Dec 07 '17 at 07:16
The problem is that if there's no records I would still like to create an object but with a 0 in the fields and not null. – Alan2 Dec 07 '17 at 07:18
1

Answer updated... – lucky Dec 07 '17 at 07:26
Once you have `.DefaultIfEmpty( ... )`, you can just end with `.First()`; no need for `FirstOrDefault` in that case. – Jeppe Stig Nielsen Dec 07 '17 at 08:16
@Jeppe Stig Nielsen, you are right. – lucky Dec 07 '17 at 08:18

Pranay Rana · Answer 2 · 2017-12-07T07:19:28.143

yes you can make use of FistOrDefault function , but it will return you null in this case when you make use of it.

Counts.btnCountViewsToday = 
   db2.Query<BtnCountViews>("SELECT " + btns + " 
       FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).FirstOrDefault();
if( Counts.btnCountViewsToday != null)
{
}
else 
  //here i am assuming that its object type 
   Counts.btnCountViewsToday =  new ViewTodayObject();

so suggest you perform null check once you do FirstOrDefault(). if you type is value type than you need to do like this

   var count = db2.Query<BtnCountViews>("SELECT " + btns + " 
           FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).FirstOrDefault();
    if( count != null)
    {}

as you are sayin it int type than do like this

   int? count = db2.Query<BtnCountViews>("SELECT " + btns + " 
           FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).FirstOrDefault();
    if( count.HasValue)
    {
          Counts.btnCountViewsToday = count.Value;
    }
    else 
        Counts.btnCountViewsToday = 0;

Thanks for the suggestions but my problem is that if there's no records I would still like to create an object but with a 0 in the fields and not null. — Alan2, Dec 07 '17 at 07:18
@Alan2 - juse perform null check and assing value in else part that will also do ... — Pranay Rana, Dec 07 '17 at 07:20

Vishal Sharma · Answer 3 · 2017-12-07T11:34:48.310

2

You should consider using FirstOrDefault Method

   var btnCountViewsToday = 
   db2.Query<BtnCountViews>("SELECT " + btns + " FROM CLICKHISTORY WHERE YYMMDD = " + yymmdd).FirstOrDefault();

You should also consider formatting your query to avoid parameter sniffing.

When to use .First and when to use .FirstOrDefault with LINQ?

Returns the first element of a sequence, or a default value if no element is found.

MSDN

edited Dec 07 '17 at 11:34

answered Dec 07 '17 at 07:16

Vishal Sharma

2,773
2
24
36

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Gilad Green Dec 07 '17 at 07:22
i suggest you add some code otherwise your answer will get deleted and moved to comment as it just have link....please follow that pattern for rest of the answer ...becuase sometime link got removed and your answer will be of no use as no one knows whats there in that link – Pranay Rana Dec 07 '17 at 11:16

Is there a way to have a default when a sequence contains no elements when using .first with LINQ?

3 Answers3