Retrieving data from datatable

Question

I'm trying to retrieve a username from a DataTable by UserID using LINQ using this example.

//Example 1:
var user = (from dr in users.AsEnumerable()
               where dr.Field<int>("UserID") == 2
               select dr).First();
//Example 2:
string user = (from dr in users.AsEnumerable()
               where dr.Field<int>("UserID") == 3
               select dr.Field<string>("UserName")).First();

The above two examples are causing this cast error:

System.InvalidCastException: 'Specified cast is not valid.'

However, the query does work if my where clause searches by string instead of int.

string result = (from row in users.AsEnumerable()
                 where row.Field<string>("UserName") == "TUser"
                 select row.Field<string>("UserName")).First();

How do I retrieve the single username by userid?

UPDATE:

Not sure if it's relevant, but the datatable is getting populated by desirializing .json file using JSON.NET

A sample .json

[{ "UserID": 2, "UserName": "User", "StatusID": 1, "CreateDate": "2018-04-25T14:02:00", "CreateBy": "Admin", "ID": "9855626A-FEF1-4936-9EF6-DD896F80AE35" }, { "UserID": 3, "UserName": "TUser", "StatusID": 1, "CreateDate": "2018-04-25T14:02:00", "CreateBy": "Admin", "ID": "928D6099-0665-4FC8-BE4C-8E145E56E8BF" } ]

And populating datatable:

DataTable users = JsonConvert.DeserializeObject<DataTable>(File.ReadAllText(userPath));

What is the underlying type of `UserID`, then? – haim770 Apr 26 '18 at 18:07 — haim770, Apr 26 '18 at 18:07

score 3 · Accepted Answer · answered Apr 26 '18 at 18:08

3

You need to use the right datatype for UserID column

Check your UserID's column type in DB and use the following table

BigInt -> long
int -> int
SmallInt -> short
varchar/nvarchar -> string

answered Apr 26 '18 at 18:08

Steve

11,696
7
43
81

I have confirmed that the `UserID` is stored in DB as `int`. – smr5 Apr 26 '18 at 18:10
1

@smr5 how about users.Columns["UserID"].DataType and is it nullable? – Steve Apr 26 '18 at 18:12
I added some more details. – smr5 Apr 26 '18 at 18:16
@smr5 you haven't answer the question. Whats the .DataType output for the DataTable? – Steve Apr 26 '18 at 18:17
I was checking that...I'm very surprised with the output! `{Name = "Int64" FullName = "System.Int64"}`, since the data is stored in DB as int, yet it's converting to `Int64`. – smr5 Apr 26 '18 at 18:20
I have learned something new today. Thank you! My educated guess is that `JSON.NET` converted the type during deserialization or when I was actually creating json file from db. – smr5 Apr 26 '18 at 18:25

Retrieving data from datatable

1 Answers1