Azure SQL C# Backend Querying Float Caues "Internal Server Error"

Question

I have a pretty bazar one for you guys that I just can't figure out or even wrap my head around. I've created a few Azure databases in the past and I believe this is my second one using a .NET backend. I seem to have everything pulling/inserting correctly except for floating point variables.

Condensed class client side:

class MealItem
{
    public string ID { get; set; }
    public string Name { get; set; }
    public float Price { get; set; }
}

Condensed class backend side:

public class MealItem : EntityData
{
    public string Name { get; set; }
    public float Price { get; set; }
}

And on the server side, the Price column (and other floats of the class) are set to the Data Type "float" with nulls allowed.

I am using sync tables, so the error is shown running this line:

await client.GetSyncTable<MealItem>().PullAsync("All", client.GetSyncTable<MealItem>().CreateQuery(), 
CancellationToken.None);

I also tried this for the heck of it:

await client.GetTable<MealItem>().ToListAsync();

Everything works when I comment out the floating point variables on both the client and backend. I've spent a good amount of time on this one and can't seem to figure it out. Any ideas would be greatly appreciated! Thanks!

`Make sure a conversion of varchar to float is not happening. — Alberto Morillo, Mar 20 '20 at 19:14
Where would that be happening? To me it looks like floats all the way up. — Joshua Stevens, Mar 20 '20 at 19:14
Could you try to convert it to float before insert using CAST as float or save it as varchar then convert each value to float using a SELECT statement? Just to verify a conversion error is not happening — Alberto Morillo, Mar 20 '20 at 19:21
I suppose I don't understand. The insert is being done with: ``` await client.GetTable().InsertAsync(me); ``` Where would I first cast it? — Joshua Stevens, Mar 20 '20 at 19:24
Forget about that just use varchar to save the data, then with a select statement do this SELECT Try_convert(float,[price]) as [Varchar to float] pay attention to NULL values received as result of the TRY_CONVERT — Alberto Morillo, Mar 20 '20 at 19:29
Forgive me, my actual SQL skills are sub-par at best. After deleting the contents of the table and changing the column to VARCHAR(50) I ran SELECT Try_convert(float,[dbo].[MealItems].[Price]) as [Varchar to float] but got an error stating "The multi-part identifier "dbo.MealItems.Price" could not be bound." — Joshua Stevens, Mar 20 '20 at 19:38
3-part and 4-part names are not allowed on Azure SQL Database. Create an alias for your table in the from "SELECT Try_convert(M.[Price]) as [Varchar to float] FROM dbo.MealItems as M" — Alberto Morillo, Mar 20 '20 at 19:41
Thank you. After inserting two new rows (one without a price and one with) I get NULL and the value 12.5 I inputted. — Joshua Stevens, Mar 20 '20 at 19:47
I also tried INSERT INTO [dbo].[MealItems] (TenantID, Name, Price) VALUES ('1234', 'Apples', Try_convert(varchar,13.5)) SELECT Try_convert(float,M.[Price]) as [Varchar to float] FROM [dbo].[MealItems] as M And I got the correct value of 13.5 — Joshua Stevens, Mar 20 '20 at 19:55
That null value is a value that cannot be inserted on the table when price had a float data type. You will have to validate data type before inserting to a price cloumn with a float data type — Alberto Morillo, Mar 20 '20 at 20:04
Before, I had set a default value of zero to the column thinking that if the row was inserted with that column null is would automatically become 0. Is that not correct? — Joshua Stevens, Mar 20 '20 at 20:05
No. Set default value to zero. ALTER TABLE `table` ADD COLUMN `column` FLOAT(10,2) NOT NULL DEFAULT '0.00' — Alberto Morillo, Mar 20 '20 at 20:08
It looks like the row is inserted correctly from the application. I'm able to see valid information on SSMS. I'm seeing that I'm only get issues trying to pull a float. — Joshua Stevens, Mar 20 '20 at 20:23
Try to store it as varchar and then convert it ti float when needed for calculations. If convertions return null then the value stored on the varchar column is not a valid float. — Alberto Morillo, Mar 20 '20 at 22:58

score 1 · Answer 1 · answered Mar 20 '20 at 20:07

That error is related to a failed conversion from varchar to float data type that takes place when you are inserting data into the table. You wll have to validate "price" data before you can insert it to the table. If the TRY_CONVERT returns NULL you cannot insert that record because price value is not valid.

score 0 · Accepted Answer · answered Mar 20 '20 at 23:09

Foreshadowing While continuing to search for my problem, I looked at my database in SSMS and noticed that my "float" was taking 8 bytes.

The steps I took leading up to finding the issue were as follows. First, on the backend, I logged a Query().toString(); to get the SQL string being sent to the SQL database. Something like:

SELECT[Extent1].[Id] AS [Id],[Extent1].[Name] AS [Name],[Extent1].[ItemType] AS [ItemType], [Extent1].[Price] AS [Price],[Extent1].[Version] AS [Version],[Extent1].[CreatedAt] AS [CreatedAt],[Extent1].[UpdatedAt] AS [UpdatedAt],[Extent1].[Deleted] AS [Deleted]FROM [dbo].[MealItems] AS [Extent1]

I tried logging the result of this statement as an error but got nothing. Trying to poke around with the solution @albert Morillo posted, I tried

SELECT[Extent1].[Id] AS [Id],[Extent1].[Name] AS [Name],[Extent1].[ItemType] AS [ItemType], Try_convert(float,[Extent1].[Price]) AS [Price],[Extent1].[Version] AS [Version],[Extent1].[CreatedAt] AS [CreatedAt],[Extent1].[UpdatedAt] AS [UpdatedAt],[Extent1].[Deleted] AS [Deleted]FROM [dbo].[MealItems] AS [Extent1]

but still got no result. I finally had the bright of putting this statement in a try catch and logging the error there. It spat back the following:

"Error: The 'Price' property on 'MealItem' could not be set to a 'System.Double' value. You must set this property to a non-null value of type."

Not knowing what this meant, I looked for a double column type in SSMS. Not finding one, I decided to change my floats to doubles on the backend app. Magically, this seems to have done the trick.

I'm not sure if this is the proper solution, but it appears to work for me. Makes sense though since the SQL database is saving and 8 byte number and a C# double is 8 bytes.

It seems an implicit conversion from double to float was taking place. Mark your reply as the correct answer in a few days. Thank you for sharing the results of your tests and the solution. — Alberto Morillo, Mar 21 '20 at 00:54

Azure SQL C# Backend Querying Float Caues "Internal Server Error"

2 Answers2