The 'x' property on 'y' could not be set to a 'System.Decimal' value. You must set this property to a non-null value of type 'System.Boolean'

Question

I have a MySQL stored procedure selecting data from specific table named tuser.

I'm using EntityFramework6, so I defined the result of procedure as an entity of tuser.

When I use the procedure in C# code, the following exception is thrown:

The 'bIsActive' property on 'tuser' could not be set to a 'System.Decimal' value. You must set this property to a non-null value of type 'System.Boolean'.

I cannot understand connection between the action I want to do and exception thrown.

Table definition in Database:

CREATE TABLE `tuser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sUserName` varchar(45) DEFAULT NULL,
`sUserNameMail` varchar(45) DEFAULT NULL,
`sMail` varchar(45) DEFAULT NULL,
`bIsActive` bit(1) DEFAULT b'1')
 ENGINE=InnoDB AUTO_INCREMENT=2225 DEFAULT CHARSET=utf8;

bIsActive definition in ef:

store procedure definition:

CREATE DEFINER=``@`` PROCEDURE `GetActiveUsers`()
BEGIN
  select u.* from tuser u
  where u.bIsActive=true;
END

error occures when executing following code line:

List<tuser> list = Context.GetActiveUsers().ToList();

inner GetActiveUsers code (auto generated):

public virtual ObjectResult<tuser> GetActiveUsers()
{    
   return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<tuser>("GetActiveUsers");
}

Looks like you have a boolean and you are trying to set it to decimal? tuser.blsActive is bool, not a decimal. — Stuart, Jun 22 '17 at 07:39
Show any code which assigns `blsActive` in `tuser` as `bool` property. — Tetsuya Yamamoto, Jun 22 '17 at 07:39
that's the point, bIsActive property is bit(1) in database and System.Boolean in ef. — Rachel Fishbein, Jun 22 '17 at 07:40
I know that `bit(1)` in SQL will materialize as `bool` property in EF, but how you assign its value in C# code? — Tetsuya Yamamoto, Jun 22 '17 at 07:41
"that's the point" - then why wasn't it in the question? *Please* include all relevant information in the question. Are you able to post a short example of the stored procedure that demonstrates the problem? (It doesn't need to be the real SP - just one that exhibits the same behaviour.) — Jon Skeet, Jun 22 '17 at 07:41
`select * from tuser where bIsActive=true` is a wrong assignment. Try `select * from tuser where bIsActive=1`. — Tetsuya Yamamoto, Jun 22 '17 at 07:53
it works for me. do you want to say that is the reason for error? — Rachel Fishbein, Jun 22 '17 at 07:53
There is something in your code trying to assign a `decimal` value as `bool` for `bIsActive` property, but you don't show it yet. You need to find out the line of code where the error has thrown. — Tetsuya Yamamoto, Jun 22 '17 at 07:58
@Tetsuya Yamamoto, the error apears before any action in my code. when I select the procedure result inside list, I get the error. Other than that, such a code would not have been compiled. — Rachel Fishbein, Jun 22 '17 at 08:00
What happens if you execute the `GetActiveUsers` stored proc from the MySQL tooling (i.e. not from C#)? Also, show us the specific code that is causing the error you are experiencing. — mjwills, Jun 22 '17 at 08:11
There's nothing wrong in your SP except changing to `u.bIsActive=(1)` or `bIsActive = b'1'`. Also ensure the results mapping is defined properly. — Tetsuya Yamamoto, Jun 22 '17 at 08:13
@mjwills in MySQL tooling there is no error. please see my edit, I wrote the specific line throwsthe error. — Rachel Fishbein, Jun 22 '17 at 08:17
@Ariela Please can you supply the code for method named `GetActiveUsers()`? — WonderWorker, Jun 22 '17 at 08:24
Are you able to perform simple queries against the table without using a stored proc? — Jon Skeet, Jun 22 '17 at 08:26
`Context.GetActiveUsers().ToList()` returns a list of `tuser` class, which depends on SP results. On your `tuser` probably has `public bool bIsActive { get; set; }`, but SP's mapping uses `public decimal bIsActive { get; set; }`. If you're using EDMX, open designer, check `Function Imports`, click the SP name & check for what `bIsActive` assigned to. — Tetsuya Yamamoto, Jun 22 '17 at 08:27
`tuser.bIsActive` is a decimal, it should be a boolean. How was `tuser` generated? — Gert Arnold, Jun 22 '17 at 08:29
@GertArnold it not a decimal but a boolean, please read all the question and see images. — Rachel Fishbein, Jun 22 '17 at 08:31
@Ariela Thank you. Please can you now supply the code for function named GetActiveUsers (that the method GetActiveUsers() calls)? — WonderWorker, Jun 22 '17 at 08:38
@Knickerless-Noggins I will give you everything can help but I did not understand what code you ask for. — Rachel Fishbein, Jun 22 '17 at 08:42
@Ariela return I'm trying to think of places where the boolean is being assigned a decimal. My hunch is that the developers of Entity Frameworks have been concentrating on SQL Server support over MySql, so somewhere in the generated code, a value isn't being properly converted to a boolean ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GetActiveUsers"); calls a function named GetActiveUsers. It's worth looking in there to see if bIsActive's value is being correctly cast/converted to a boolean. — WonderWorker, Jun 22 '17 at 08:46
@JonSkeet when I perform same query in place of stored proc I have no error. — Rachel Fishbein, Jun 22 '17 at 08:47
Okay, that's interesting. I suggest you try comparing the results of simple queries with stored procedures in a regular SQL tool. (Whatever MySQL provides for interactive database queries.) — Jon Skeet, Jun 22 '17 at 08:48
...an interim measure to get the code working might be to make bIsActive an integer instead of a boolean, where 1 represents true and 0 represents false. Just while we figure out what's going on. — WonderWorker, Jun 22 '17 at 08:49
Try changing `List` to a standard var assignment: `var list = Context.GetActiveUsers().ToList()` & use `foreach` loop to iterate it, then find out what data type `bIsActive` has. That's interesting to know what kind of result contained inside `GetActiveUsers` method. Also you may want to change `bIsActive` into `int` or `tinyint` data type for clarity. — Tetsuya Yamamoto, Jun 22 '17 at 08:51
@JonSkeet You're right but it is the last option i would like to use. I dont want to have to upload new version every time condition of query will be changed (for example: if I want to select only users with non-empty userName etc.). — Rachel Fishbein, Jun 22 '17 at 08:53
@Ariela: I think you replied to the wrong person or misunderstood me. I was only making a diagnostic suggestion - nothing about uploading new versions of anything. — Jon Skeet, Jun 22 '17 at 08:58
@TetsuyaYamamoto I tries your suggestion, but got same error. — Rachel Fishbein, Jun 22 '17 at 09:02
@JonSkeet ok I only explained why I dont want to use query inside the code. — Rachel Fishbein, Jun 22 '17 at 09:03
@Ariela Is `Context.GetActiveUsers()` still generating error even `ToList()` & `List` assignment has stripped out (just with a `var` & set breakpoint on it to check query results)? I know this problem is related to entity mapping with `MySql.Data.Entity` but I still don't have strong evidence to tell you what's going wrong. — Tetsuya Yamamoto, Jun 22 '17 at 09:07
@TetsuyaYamamoto the error thrown from ToList() function. When I used: `IEnumerable list = Context.GetActiveUsers();` in first line and `List users = list.ToList();` the next lint, the error thrown from second line. — Rachel Fishbein, Jun 22 '17 at 09:12
I assumed you get `IEnumerable` instance with query results, try iterating it with `foreach` loop. Don't add `ToList` at this point, the foreach local variable may reveal what data type declared for `bIsActive` property, e.g. `foreach (var items in list) { var type = typeof(items.bIsActive); }`. — Tetsuya Yamamoto, Jun 22 '17 at 09:20
@TetsuyaYamamoto I tried it but got the same error when executing `var item` words first. I think it because I defined the sp to return tuser type in ef. — Rachel Fishbein, Jun 22 '17 at 09:24
Is that `tuser` defined in an entity class? Possibly the assignment of return type from `GetActiveUsers` triggers the exception due to EF incorrectly mapped data type against this list: https://www.devart.com/dotconnect/mysql/docs/DataTypeMapping.html. — Tetsuya Yamamoto, Jun 22 '17 at 09:32
@Ariela: I wasn't suggesting using a query in code. I'm just trying to help you diagnose what's going wrong. If queries work and stored procedures don't, you need to look at what difference that makes in terms of what the database is returning. — Jon Skeet, Jun 22 '17 at 09:35

score 1 · Answer 1 · answered Jun 22 '17 at 10:28

1

Ok, Thanks to all the responders.

I still don't know what was the problem, but when I removed tuser table from model and added it again, problem has been solved.

answered Jun 22 '17 at 10:28

Rachel Fishbein

818
2
12
29

I imagine the issue was a corruption somewhere in the generated code/model. Well done Ariela! – WonderWorker Jun 22 '17 at 10:29
That's why I said that the property was decimal. It was decimal in the code base. You only updated the EDMX model but not the generated code. – Gert Arnold Jun 22 '17 at 13:46
@GertArnold2 It was not as you can see in screenshots. – Rachel Fishbein Jun 25 '17 at 07:23

WonderWorker · Answer 2 · 2017-06-22T10:15:05.077

The exception tells us that the boolean variable bIsActive is being assigned a decimal or not being cast properly somewhere around the following line:

List<tuser> list = Context.GetActiveUsers().ToList();

The solution is therefore a journey from the point the exception is thrown, to the point where the value has been misassigned.

To begin with, try changing...

public virtual ObjectResult<tuser> GetActiveUsers()
{    
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<tuser>("GetActiveUsers");
}

...with...

public virtual ObjectResult<tuser> GetActiveUsers()
{
    try
    {
        Stringbuilder sb = new Stringbuilder();

        IObjectContextAdapter a = ((IObjectContextAdapter)this); // Breakpoint this line and F11 step through the code, looking at local variables as you go

        sb.AppendLine(", a: " + a.ToString());

        var b = a.ObjectContext;

        sb.AppendLine(", b: " + b.ToString());

        var c = b.ExecuteFunction<tuser>("GetActiveUsers");

        sb.AppendLine(", c: " + c.ToString());

        MessageBox.Show("No exception: (" + sb.ToString() + ")");

        return c;

    }
    catch(Exception ex)
    {
        MessageBox.Show("Exception: (" + ex.message + ex.stacktrace + ")");

    }

}

I hope you can see what I'm trying to do there i.e. split up the code into bite-sized chunks and study the output of each one. The message box message tells you if an exception was thrown or not, and will give a StackTrace, which is very helpful on a journey like this.

Replace the method
Run the program
Paste a screenshot of the MessageBox into your post

The next step will probably be to take a look at the database function, GetActiveUsers.

How do you manage your MySql database? Do you use MySql workbench or another interface? You should be able to view the function from there.

Given that the original code *compiles* fine, I see no indication that type inference is causing a problem. Why do you think this will make a difference? — Jon Skeet, Jun 22 '17 at 08:25
@JonSkeet Please calm down, Jon. The exception is being thrown on the line `List list = Context.GetActiveUsers().ToList();`, but this only hints at how we discover the solution(s). When hunting for a solution, I like to approach from the north, the south, the east and the west. — WonderWorker, Jun 22 '17 at 09:23
If you're asking for more diagnostics, that's better done in comments - particularly if it consists of spurious suggestions like these, IMO. — Jon Skeet, Jun 22 '17 at 09:34
@Jon Skeet There is nothing spurious about homing in on the root of the problem from the point the exception is thrown. — WonderWorker, Jun 22 '17 at 10:04
Sure - as a comment. Not as an answer; it doesn't solve the OP's problem. To be honest, asking for the complete stack trace would have accomplished this in a rather simpler fashion... I'd be astonished if that didn't show that `ExecuteFunction` is throwing the exception; the local variables up until that point won't really help. I still maintain this is "not useful" as an answer, hence the downvote. — Jon Skeet, Jun 22 '17 at 10:09

The 'x' property on 'y' could not be set to a 'System.Decimal' value. You must set this property to a non-null value of type 'System.Boolean'

2 Answers2