CODEWITHSUNDEEP
sqlsql-serverentity-frameworkasp.net-core
dannyriv
dannyriv

Reputation: 87

Entity Framework SQL query throwing a conversion error

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.

Upvotes: 0

Views: 569

Answers (1)

Steve Py
Steve Py

Reputation: 34698

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.

Upvotes: 1

Related Questions