Generate EntityFramework CodeFirst Model from SQLServer

The issue

You are creating your code first entity framework classes and you need to create a Dto class (Data Transfer Object) to represent your table.

Lots of typing required… or

The solution

The following SQL statement will generate and print out a model class definition.

Declare @properties nvarchar(max)
Declare @tableName nvarchar(128)
--================================================
-- Parameters
Set @tableName = 'MyTable'
--================================================
set @properties = ''
select
       @properties = @properties +
       Case When c.is_computed = 1 Then '    [NotMapped]' + Char(10) else '' End +
       Case When c.is_identity = 1 Then '    [Key]' + Char(10) else '' End +
       Case When c.system_type_id = 189 Then '    [Timestamp]' + Char(10) else '' End
       + '    public '
        + Case c.system_type_id
              When 36 Then 'Guid' + Case when c.is_nullable = 1 Then '?' else '' End
              When 48 Then 'byte' + Case when c.is_nullable = 1 Then '?' else '' End
              When 52 Then 'short' + Case when c.is_nullable = 1 Then '?' else '' End
              When 56 Then 'int' + Case when c.is_nullable = 1 Then '?' else '' End
              When 40 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 43 Then 'DateTimeOffset' + Case when c.is_nullable = 1 Then '?' else '' End
              When 60 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End
              When 58 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 61 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 62 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End
              When 104 Then 'bool' + Case when c.is_nullable = 1 Then '?' else '' End
              When 106 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End
              When 108 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End
              When 127 Then 'long' + Case when c.is_nullable = 1 Then '?' else '' End
              When 165 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End
              When 189 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End -- RowVersion
              Else 'string'
        end
        + ' ' + c.name + ' { get; set; }' + Char(10)
from sys.tables t
       inner join sys.columns c on t.object_id = c.object_id
where t.name  = @tableName
 
print '[Table("' + @tableName + '")]'
print 'public class '+  @tableName + 'Dto'
print '{'
print @properties
print '}'

Work smarter not harder.

The following SQL with generate a base class..

Your welcome 🙂

NOTE: I may have missed one or two edge case values for the different datatypes that come back from SQL Server, so as you hit them, (like for example 165 is a blob object, then just copy and past to the case statement

Schema Version

If your able has is in a schema the following will support generating a model that supports the schema

Declare @properties nvarchar(max)
Declare @tableName nvarchar(128)
Declare @schema nvarchar(128)
--================================================
-- Parameters
Set @tableName = 'Table'
Set @schema = 'Schema'
--================================================
set @properties = ''
select
       @properties = @properties +
       Case When c.is_computed = 1 Then '    [NotMapped]' + Char(10) else '' End +
       Case When c.is_identity = 1 Then '    [Key]' + Char(10) else '' End +
       Case When c.system_type_id = 189 Then '    [Timestamp]' + Char(10) else '' End
       + '    public '
        + Case c.system_type_id
              When 36 Then 'Guid' + Case when c.is_nullable = 1 Then '?' else '' End
              When 48 Then 'byte' + Case when c.is_nullable = 1 Then '?' else '' End
              When 52 Then 'short' + Case when c.is_nullable = 1 Then '?' else '' End
              When 56 Then 'int' + Case when c.is_nullable = 1 Then '?' else '' End
              When 40 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 43 Then 'DateTimeOffset' + Case when c.is_nullable = 1 Then '?' else '' End
              When 60 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End
              When 58 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 61 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 62 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End
              When 104 Then 'bool' + Case when c.is_nullable = 1 Then '?' else '' End
              When 106 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End
              When 108 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End
              When 127 Then 'long' + Case when c.is_nullable = 1 Then '?' else '' End
              When 165 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End
              When 189 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End -- RowVersion
              Else 'string'
        end
        + ' ' + c.name + ' { get; set; }' + Char(10)
from sys.tables t
       inner join sys.columns c on t.object_id = c.object_id
	   inner join sys.schemas s on t.schema_id = s.schema_id
where t.name  = @tableName and s.name = @schema
 
print '[Table("' + @tableName + '", Schema="' + @schema + '")]'
print 'public class '+  @tableName + 'Dto'
print '{'
print @properties + '}'

Update for .Net 8.0

With the new string property changes this is an updated version

Declare @properties varchar(max)
Declare @tableName nvarchar(128)
--================================================
-- Parameters
Set @tableName = 'MyTable'
--================================================
set @properties = ''
select
       @properties = @properties +
       Case When c.is_computed = 1 Then '    [NotMapped]' + Char(10) else '' End +
       Case When c.is_identity = 1 Then '    [Key]' + Char(10) else '' End +
       Case When c.system_type_id = 189 Then '    [Timestamp]' + Char(10) else '' End
       + '    public '
        + Case c.system_type_id
              When 36 Then 'Guid' + Case when c.is_nullable = 1 Then '?' else '' End
              When 48 Then 'byte' + Case when c.is_nullable = 1 Then '?' else '' End
			  When 52 Then 'short' + Case when c.is_nullable = 1 Then '?' else '' End
			  When 56 Then 'int' + Case when c.is_nullable = 1 Then '?' else '' End
              When 40 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 43 Then 'DateTimeOffset' + Case when c.is_nullable = 1 Then '?' else '' End
              When 60 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End
              When 58 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 61 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End
              When 62 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End
              When 104 Then 'bool' + Case when c.is_nullable = 1 Then '?' else '' End
              When 106 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End
              When 108 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End
              When 127 Then 'long' + Case when c.is_nullable = 1 Then '?' else '' End
			  When 165 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End
			  When 189 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End -- RowVersion
              Else 
				 Case when c.is_nullable = 0 Then 'required ' else '' End + 'string' + Case when c.is_nullable = 1 Then '?' else '' End
        end
        + ' ' + c.name + ' { get; set; }' + Char(10)
from sys.tables t
       inner join sys.columns c on t.object_id = c.object_id
where t.name  = @tableName
 
print '[Table("' + @tableName + '")]'
print 'public class '+  @tableName + 'Dto'
print '{'
print @properties
print '}'

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30