Entity Framework - get SQL Server field data type

Question

I am new to SQL Server and trying to figure out if there is a way to get the data type of a column in a table that I have created and entity for.

For example, if I have a SQL Server table 'Employees' with the following column

employeeID int, empName varchar(255), empDept varchar(100)

Is there a way to find the data type for the empName field in C# / Entity Framework?

In the end what I am trying to see is the data type and the column length. So, if I were looping through all of the fields in the table/entity I would like to see: "varchar(255)", "varchar(100)", "int" etc.

Answer 1

You could either create a stored procedure and call that from EF passing in the table names, or run a raw sql command something like the following:

var sqlToRun = string.format(@"SELECT column_name as 'Column Name',
    data_type as 'Data Type',
    character_maximum_length as 'Max Length'
    FROM information_schema.columns
    WHERE table_name = '{0}'", myTableName);

using (var context = new dbContext()) 
{ 
    var tableFieldDetails= context.SqlQuery(sqlToRun).ToList(); 

    //do something with the tableFieldDetails collection
}

...where myTableName is the name of the table to return field info from.

Do note that a -1 is returned if you are using an nvarchar(MAX) or varchar(MAX) and I'm sure there may be some other anomalies as I have not tested all types.

Also I have not tested the c# above, so it may need a tweak to work, but the principle is good and would benefit from being encapsulated in a nice method ;-)

For more info on running raw sql in EF see https://msdn.microsoft.com/en-us/data/jj592907.aspx