{"action":"created","issue":{"url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/525","repository_url":"https://api.github.com/repos/fsprojects/SQLProvider","labels_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/525/labels{/name}","comments_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/525/comments","events_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/525/events","html_url":"https://github.com/fsprojects/SQLProvider/issues/525","id":302008354,"number":525,"title":"MapTo causes crashes when used with Option types.","user":{"login":"matthewblott","id":1231417,"avatar_url":"https://avatars3.githubusercontent.com/u/1231417?v=4","gravatar_id":"","url":"https://api.github.com/users/matthewblott","html_url":"https://github.com/matthewblott","followers_url":"https://api.github.com/users/matthewblott/followers","following_url":"https://api.github.com/users/matthewblott/following{/other_user}","gists_url":"https://api.github.com/users/matthewblott/gists{/gist_id}","starred_url":"https://api.github.com/users/matthewblott/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/matthewblott/subscriptions","organizations_url":"https://api.github.com/users/matthewblott/orgs","repos_url":"https://api.github.com/users/matthewblott/repos","events_url":"https://api.github.com/users/matthewblott/events{/privacy}","received_events_url":"https://api.github.com/users/matthewblott/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-03-03T15:49:57Z","updated_at":"2018-03-04T10:51:35Z","closed_at":null,"author_association":"NONE","body":"Consider the following. I have a really simple ```Users``` table with three fields: ```Id```, ```FirstName``` and ```LastName```. ```FirstName``` is required but ```LastName``` isn't and accepts a ```null``` value such as that in the following insert query.\r\n\r\n```\r\ninsert into Users (FirstName, LastName) values ('Joe', null)\r\n```\r\n\r\nI create a simple function to extract a single record based on its ```Id``` value like so.\r\n\r\n```\r\nlet getById id =\r\n  query {\r\n          for user in users do\r\n          where (user.Id = id)\r\n          select user\r\n          exactlyOne\r\n  }\r\n\r\n```\r\n\r\nI wish to check the value of the ```LastName``` field for a record and do so with the following.\r\n\r\n```\r\nlet user = getById 1\r\n\r\nuser.LastName\r\n```\r\n\r\nThis returns the following as expected.\r\n\r\n```\r\nval it : Option<string> = None\r\n```\r\n\r\nNow I want to make things a bit more organised and introduce a record type.\r\n\r\n```\r\ntype User = { \r\n  Id : int ;\r\n  FirstName : string ;\r\n  LastName : Option<string> ;\r\n}\r\n```\r\n\r\nSo I adjust my ```getById``` function accordingly.\r\n\r\n```\r\nlet getById id =\r\n  query {\r\n          for user in users do\r\n          where (user.Id = id)\r\n          select (user.MapTo<User>())\r\n          exactlyOne\r\n  }\r\n\r\n```\r\n\r\nI then execute the code from earlier to extract the ```LastName``` value and it works as before. However, if I update the field and provide a value other than ```null``` the code crashes. I have to adjust my record type and make the ```LastName``` field of type ```string``` and not ```Option<string>```. This lets my code run but it means I'm getting the default value for whatever the data type maps to the corresponding SQL type (```0``` for ```int```, ```01/01/0001 00:00:00``` for ```DateTime``` etc). Is there any way round this so I can use the ```Option``` type and still have the option of returning ```None``` for values that don't exist in the database?\r\n\r\n* Used database\r\nSQL Server 2017 (on Linux)\r\n* Operating system\r\nmacOS\r\n* Branch\r\nMono 5.8.0.127\r\n\r\n\r\n### Update after sometime messing around with this!\r\n\r\nUnfortunately it seems to work differently to how I thought and the returned field type is based on the data for that row and not the schema itself. For example, a nullable field of type ```datetime``` will return a ```None``` value for the field type ```Option<DateTime>```. So to make this match the record type must have a corresponding field of ```Option<DateTime>```. However, if this is set and another row has a positive ```datetime``` value there is a runtime error saying ```DateTime``` cannot be matched with ```Option<DateTime>```. This is quite confusing and difficult to work around."},"comment":{"url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/comments/370219396","html_url":"https://github.com/fsprojects/SQLProvider/issues/525#issuecomment-370219396","issue_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/525","id":370219396,"user":{"login":"matthewblott","id":1231417,"avatar_url":"https://avatars3.githubusercontent.com/u/1231417?v=4","gravatar_id":"","url":"https://api.github.com/users/matthewblott","html_url":"https://github.com/matthewblott","followers_url":"https://api.github.com/users/matthewblott/followers","following_url":"https://api.github.com/users/matthewblott/following{/other_user}","gists_url":"https://api.github.com/users/matthewblott/gists{/gist_id}","starred_url":"https://api.github.com/users/matthewblott/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/matthewblott/subscriptions","organizations_url":"https://api.github.com/users/matthewblott/orgs","repos_url":"https://api.github.com/users/matthewblott/repos","events_url":"https://api.github.com/users/matthewblott/events{/privacy}","received_events_url":"https://api.github.com/users/matthewblott/received_events","type":"User","site_admin":false},"created_at":"2018-03-04T10:51:35Z","updated_at":"2018-03-04T10:51:35Z","author_association":"NONE","body":"Yeah that seems to be the way to go. I created the following record type.\r\n\r\n```\r\ntype User = { \r\n  Id : int ;\r\n  FirstName : string ;\r\n  LastName : Option<string> ;\r\n  Active : Option<bool>\r\n  CreatedOn : Option<DateTime>\r\n}\r\n\r\n```\r\n\r\nAnd adjusted my ```getById``` function accordingly.\r\n\r\n```\r\nlet getById id =\r\n  let user =\r\n    query {\r\n            for user in users do\r\n            where (user.Id = id)\r\n            select user\r\n            exactlyOne\r\n    }\r\n  { \r\n    Id = user.Id ;\r\n    FirstName = user.FirstName ;\r\n    LastName = user.LastName ;\r\n    Email = user.Email ;\r\n    Active = user.Active ;\r\n    CreatedOn = user.CreatedOn\r\n  }\r\n\r\nList.map (fun x -> (getById x)) [1..5]\r\n\r\n```\r\n\r\nAnd tested it with the following.\r\n\r\n```\r\nList.map (fun x -> (getById x)) [1..5]\r\n```\r\n\r\n... using the following records.\r\n\r\n![s 2018-03-04 at 10 43 01](https://user-images.githubusercontent.com/1231417/36944647-df6e4016-1f98-11e8-93da-de93f9efab0d.jpg)\r\n\r\nNo crashes. It's a bit strange as I'm not doing any explicit checking but if I substitute the field assignment for a simple ```MapTo``` it crashes. It would be nice to just use ```MapTo``` and avoid all the boilerplate of field assignment but at least it works."}}
{"actor":{"display_login":"matthewblott"}}