FromRawSql HasNoKey nullable bool conversion

Question

I want to create a raw sql query this way and map result to a ViewModel class:

Here is the view model class:

class MyViewModel
{
   public String field1 { get; set; }
   public bool? field2 { get; set; }
}

Here is what i've put in dbcontext class (in OnModelCreating function):

modelBuilder.Entity<MyViewModel>.HasNoKey();

Here is my query:

   var query = db.Set< MyViewModel>(). FromRawSql("SELECT field1, null as field2 FROM MyTable");

I get an error about field2 because EF can't cast Null to field2. I do not understand why because field2 is nullable in MyViewModel.

I have also tried to put 0 or 1. I have a cast error Int32 vs bool. How can i make it work ?

Thanks a lot

Answer 1

There is no bool type in T-SQL. Bit is used instead. Just remove field2 from your SQL query

var query = db.Set< MyViewModel>(). FromRawSql("SELECT field1 FROM MyTable");

or try

class MyViewModel
{
   public string field1 { get; set; }
   public int? field2 { get; set; }
   public bool? field2b { get if (field2 != null) return Convert.ToBoolean(field2) 
  else return null;
   }
}

Or you can use case, add it to your SQL script

CASE 
    WHEN column2 IS NULL 
        THEN CAST(NULL AS bit)
        ELSE column2 
END AS field2

I can give more details, since I don't see what table are you querying.