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

Work smarter not harder.

The following SQL with generate a base class..

Declare @properties nvarchar(max)
Declare @tableName nvarchar(128)
--================================================
-- Parameters
Set @tableName = 'Organisation'
--================================================
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 +
       + '    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
              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 '}'

Your welcome 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.