Entity Framework SQL query throwing a conversion error

Question

I have this simple query where I want to return a column of a record from a table which can be either 1 or 0. I am trying to use this query but I get a conversion error:

System.InvalidCastException: 'Unable to cast object of type 'System.Boolean' to type 'System.Int32'.'

public int GetUserValidFlag(int userId)
{
    var query = from r in db.VisUsers
                where r.UserId == userId
                select r.IsValid;
    return Convert.ToInt32(query.FirstOrDefault());
}

I do have an instance of db in this class.

Context:

public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
    {
    }

    public DbSet<Category> Categories { get; set; }
    public DbSet<SubCategory> SubCategories { get; set; }
    public DbSet<VisUser> VisUsers { get; set; }
    public DbSet<Project> Projects { get; set; }
}

I have also tried with Find():

public int GetUserValidFlag(int userId)
{
    var record = db.VisUsers.Find(userId);
    return Convert.ToInt32(record.IsValid);
}

NOTE: these two attempts do not reach the return statements, the error occurs before the return.

I am unsure where/why this conversion error is occurring, I can make it work with stored procedure but I would like to understand why this version is failing.

Can you show your VisUser model please. Is this EF code first? — Peter Smith
– Peter Smith, Commented Sep 29, 2019 at 8:27
Two guesses come to mind: 1) r.IsValid is null for the particular user you're selecting, or 2) userId doesn't exist in db.VisUsers, so r.IsValid is null. Also, if it's a boolean flag why isn't the method signature public bool GetUserValidFlag(int userId)? — AlwaysLearning
– AlwaysLearning, Commented Sep 29, 2019 at 9:01
Convert.ToInt32 sure looks like a conversion to Int32 to me... why don't you start there? — Nick.Mc
– Nick.Mc, Commented Sep 29, 2019 at 13:44

Steve Py · Accepted Answer · 2019-09-29 12:14:50Z

Databases generally store Boolean flags as Bit fields with values of 1 or 0. EF will map these to entities as Boolean type. Typically in your code you would just use bool as the data type for methods and properties for a flag like this. If you do still want to convert it to an Int then you also need to consider whether the DB value was null-able or not:

If the DB value is not null-able:

return query.Single() ? 1 : 0;

FirstOrDefault should only be used with an order-by clause in cases where you expect 0 or many possible results. If you are expecting 1 user with that Id, use Single. This will throw an exception if the User ID doesn't match a record, or if more than 1 record was returned. Using the OrDefault varieties will gunk things up if a User record was not found. If you want to explicitly check and handle the possibility that no record exists, then use an OrDefault method, otherwise it's better to handle it as an exception.

If the DB value for the query is null-able:

bool? isValid = query.Single();
return isValid.HasValue && isValid.Value ? 1 : 0;

This checks the value and returns 1 if the value is present and True, otherwise it returns 0.

Collectives™ on Stack Overflow

Entity Framework SQL query throwing a conversion error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related