CODEWITHSUNDEEP
c#sql-serverentity-frameworklinqnull
tahdhaze09
tahdhaze09

Reputation: 2213

How do I check for a null value in a tuple or single value in a column using LINQ for Entity Framework?

I have a row that has a datetime value in it. If that value has a date, the user is confirmed and can log in. But, if the value is NULL, the user needs to find their confirmation email and click on the link to verify before log in.

I need to find out, using LINQ to EF, how to verify if that value in that row is NULL or not.

Here's what i have:

C#:

var verifiedquery = from q in db.applicants 
                    join r in db.UserActivations on q.id equals r.userID 
                    where q.ap_username.Equals(lgnUsers.UserName.Trim()) 
                    && q.ap_password.Equals(lgnUsers.Password) 
                    select r.dateVerified.Value;

if (!verifiedquery.Any())
{
    login.FailureText = "Confirm your account please.";
    e.Authenticated = false;
    return;
}

Upvotes: 0

Views: 1031

Answers (3)

Ouarzy
Ouarzy

Reputation: 3043

2 problems here:

1> from a query you get a list, you need to select only one (the first if you are sure that your query return only one result)

2> You want r.dateVerified, wich is a nullable dateTime, so that you can check if it's null or not after.

var verifiedquery = from q in db.applicants 
                        join r in db.UserActivations on q.id equals r.userID 
                        where q.ap_username.Equals(lgnUsers.UserName.Trim()) 
                        && q.ap_password.Equals(lgnUsers.Password) 
                        select r.dateVerified;
    var fstResult = verifiedQuery.First();
    if (!fstResult.HasValue)
    {
        login.FailureText = "Confirm your account please.";
        e.Authenticated = false;
        return;
    }

Upvotes: 1

DavidG
DavidG

Reputation: 118977

The problem is in your select:

select r.dateVerified.Value

You are getting the Value property of a nullable field. Instead you can just get your query to return a bool:

var isVerified = (from q in db.applicants 
                 join r in db.UserActivations on q.id equals r.userID 
                 where q.ap_username.Equals(lgnUsers.UserName.Trim()) 
                 && q.ap_password.Equals(lgnUsers.Password) 
                 && r.dateVerified.HasValue
                 select r.dateVerified)
    .Any();

Upvotes: 2

Adil Ansari
Adil Ansari

Reputation: 356

You can get your nullable datetime variable, and then use this variable to check either it has value or not. Use following line of code,

var verifiedquery = from q in db.applicants 
                    join r in db.UserActivations on q.id equals r.userID 
                    where q.ap_username.Equals(lgnUsers.UserName.Trim()) 
                    && q.ap_password.Equals(lgnUsers.Password) 
                    select r.dateVerified;

if (!verifiedquery.HasValue)
{
    login.FailureText = "Confirm your account please.";
    e.Authenticated = false;
    return;
}

Upvotes: 1

Related Questions