Generates POCO classes for entity framework for all the tables in current database. Considers primary keys, foreign keys, and writes object descriptions saved as extended properties to the comments of classes.

EfPocoGenerator.sql

set nocount on

declare @namespace varchar(500)
set @namespace = 'PersonnelManagement.Common.DataModel'

declare @typeMap table(sqlName varchar(50), dotNetName varchar(50), isNullable bit)
insert into @typeMap values('image', 'byte[2147483647]', null)
insert into @typeMap values('text', 'string', null)
insert into @typeMap values('uniqueidentifier', 'Guid', 0)
insert into @typeMap values('uniqueidentifier', 'Guid?', 1)
insert into @typeMap values('date', 'DateTime', 0)
insert into @typeMap values('date', 'DateTime?', 1)
insert into @typeMap values('time', 'TimeSpan', 0)
insert into @typeMap values('time', 'TimeSpan?', 1)
insert into @typeMap values('datetime2', 'DateTime', 0)
insert into @typeMap values('datetime2', 'DateTime?', 1)
insert into @typeMap values('tinyint', 'byte', 0)
insert into @typeMap values('tinyint', 'byte?', 1)
insert into @typeMap values('smallint', 'short', 0)
insert into @typeMap values('smallint', 'short?', 1)
insert into @typeMap values('int', 'int', 0)
insert into @typeMap values('int', 'int?', 1)
insert into @typeMap values('smalldatetime', 'DateTime', 0)
insert into @typeMap values('smalldatetime', 'DateTime?', 1)
insert into @typeMap values('real', 'float', 0)
insert into @typeMap values('real', 'float?', 1)
insert into @typeMap values('money', 'decimal', 0)
insert into @typeMap values('money', 'decimal?', 1)
insert into @typeMap values('datetime', 'DateTime', 0)
insert into @typeMap values('datetime', 'DateTime?', 1)
insert into @typeMap values('float', 'double', 0)
insert into @typeMap values('float', 'double?', 1)
insert into @typeMap values('ntext', 'string', null)
insert into @typeMap values('bit', 'bool', 0)
insert into @typeMap values('bit', 'bool?', 1)
insert into @typeMap values('decimal', 'decimal', 0)
insert into @typeMap values('decimal', 'decimal?', 1)
insert into @typeMap values('numeric', 'decimal', 0)
insert into @typeMap values('numeric', 'decimal?', 1)
insert into @typeMap values('smallmoney', 'decimal', 0)
insert into @typeMap values('smallmoney', 'decimal?', 1)
insert into @typeMap values('bigint', 'long', 0)
insert into @typeMap values('bigint', 'long?', 1)
insert into @typeMap values('varbinary', 'byte[@length]', null)
insert into @typeMap values('varchar', 'string', null)
insert into @typeMap values('binary', 'byte[@length]', null)
insert into @typeMap values('char', 'string', null)
insert into @typeMap values('timestamp', 'byte[8]', null)
insert into @typeMap values('nvarchar', 'string', null)
insert into @typeMap values('nchar', 'string', null)
insert into @typeMap values('xml', 'string', null)
insert into @typeMap values('sysname', 'string', null)

declare @excludedTables table(name varchar(50))
insert into @excludedTables values('sysdiagrams')
insert into @excludedTables values('Sheet1$')
insert into @excludedTables values('Relationships')

print 'using System;'
print 'using System.Collections.Generic;'
print 'using System.ComponentModel.DataAnnotations;'
print 'using System.Data.Entity;'
print 'using System.Linq;'
print 'using System.Text;'
print ''
print 'namespace ' + @namespace
print '{'

declare tablesCursor cursor for
      select o.name, o.id, Cast(x.value as varchar(4000))
      from sys.sysobjects o
      left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL) as x on o.name = x.objname collate database_default 
      where o.type='U'
      and o.name not in (select * from @excludedTables)
      order by o.name

open tablesCursor

declare @tableName varchar(50)
declare @tableId bigint
declare @tableDescription varchar(4000)

fetch next from tablesCursor into @tableName, @tableId, @tableDescription
while @@FETCH_STATUS = 0
begin
      print '    /// <summary>'
    print '    /// ' + Isnull(@tableDescription, '')
    print '    /// </summary>'
      print '    public class ' + @tableName
      print '    {'

      declare columnsCursor cursor for
            select 
                  c.colid,
                  c.name, 
                  c.length, 
                  t.name, 
                  Cast(x.value as varchar(4000)) as [description],
                  case 
                        when exists (
                             select 1
                             from sys.sysindexes i
                             inner join sys.sysindexkeys k on k.id = i.id and k.indid = i.indid
                             where i.status = 2066
                             and i.id = @tableId
                             and k.id = @tableId
                             and k.colid = c.colid
                        ) then 1
                        else 0
                  end
                  as IsPrimaryKey,
                  c.isnullable
            from sys.syscolumns c
            inner join sys.types t on c.xtype = t.user_type_id
            left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @tableName, 'column', NULL) as x on c.name = x.objname collate database_default 
            where c.id = @tableId
            order by c.name

      declare @columnId int
      declare @columnName varchar(50)
      declare @columnLength int
      declare @columnType varchar(50)
      declare @columnDescription varchar(4000)
      declare @isPrimaryKey bit
      declare @isNullable bit
      declare @oneToOneRelatedTables table(parentTable varchar(50), referencedTable varchar(50))
      
      open columnsCursor
      fetch next from columnsCursor into @columnId, @columnName, @columnLength, @columnType, @columnDescription, @isPrimaryKey, @isNullable
      if @@ERROR <> 0
      begin
            print 'hata çýktý' + @@ERROR
      end
      while @@FETCH_STATUS = 0
      begin
            declare @dotNetType varchar(50)
            select @dotNetType = dotNetName from @typeMap where sqlName = @columnType and (isNullable = @isNullable or isNullable is null)
            set @dotNetType = Replace(IsNull(@dotNetType, ''), '@length', Cast(@columnLength as varchar(10)))
            print '        /// <summary>'
            print '        /// ' + Replace(Isnull(@columnDescription, ''), CHAR(13) + CHAR(10), CHAR(13) + CHAR(10) + '        /// ') 
            print '        /// </summary>'
            if @isPrimaryKey = 1
            begin
                  print '        [Key]'
            end
            
            declare oneToOneRelations cursor for
                  select distinct tableName
                  from (
                        select o.name as tableName, i.name as indexName, count(*) as [count]
                        from sys.foreign_key_columns fkc
                        inner join sys.sysindexes i on i.id = fkc.parent_object_id
                        inner join sys.sysindexkeys ik on ik.id = fkc.parent_object_id and ik.indid = i.indid and ik.colid = fkc.parent_column_id
                        inner join sys.sysobjects o on o.id = fkc.parent_object_id
                        where (i.status = 2066 or i.status = 2)
                        and fkc.referenced_object_id = @tableId
                        and fkc.referenced_column_id = @columnId
                        group by o.name, i.name
                        having count(*) = 1
                  ) as oneToOneRelationQuery
            open oneToOneRelations
            declare @oneToOneRelatedTable varchar(50)
            fetch next from oneToOneRelations into @oneToOneRelatedTable
            while @@FETCH_STATUS = 0
            begin
                  print '        [ForeignKey("' + @oneToOneRelatedTable + '")]'
                  insert into @oneToOneRelatedTables values(@oneToOneRelatedTable, @tableName)
                  fetch next from oneToOneRelations into @oneToOneRelatedTable
            end
            close oneToOneRelations
            deallocate oneToOneRelations
            
            print '        public ' + @dotNetType + ' ' + @columnName + ' { get; set; }'
            print ''
            
            fetch next from columnsCursor into @columnId, @columnName, @columnLength, @columnType, @columnDescription, @isPrimaryKey, @isNullable
      end
      close columnsCursor
      deallocate columnsCursor
      
      declare foreignKeys cursor for
            select o.name, c.name
            from sys.foreign_keys k
            inner join sys.foreign_key_columns kc on kc.constraint_object_id = k.object_id and kc.parent_object_id = k.parent_object_id
            inner join sys.sysobjects o on o.id = k.parent_object_id
            inner join sys.syscolumns c on c.id = kc.parent_object_id and c.colid = kc.parent_column_id
            where k.referenced_object_id = @tableId
            and o.name not in (select * from @excludedTables)
            order by o.name
      
      declare @foreignTableName varchar(50)
      declare @foreignColumnName varchar(50)
      
      open foreignKeys
      fetch next from foreignKeys into @foreignTableName, @foreignColumnName
      while @@FETCH_STATUS = 0
      begin
            if not exists (select * from @oneToOneRelatedTables where parentTable = @foreignTableName and referencedTable = @tableName)
            begin
                  print '        [ForeignKey("' + @foreignColumnName + '")]'
                  print '        public virtual IList<' + @foreignTableName + '> ' + @foreignTableName + ' { get; set; }'
            end
            else
            begin
                  print '        public virtual ' + @foreignTableName + ' ' + @foreignTableName + ' { get; set; }'
            end
            
            fetch next from foreignKeys into @foreignTableName, @foreignColumnName
      end
      
      close foreignKeys
      deallocate foreignKeys
      
      print '    }'
      print ''
      fetch next from tablesCursor into @tableName, @tableId, @tableDescription
end

close tablesCursor

print '    public class ' + db_name() + ' : DbContext'
print '    {'

open tablesCursor

fetch next from tablesCursor into @tableName, @tableId, @tableDescription
while @@FETCH_STATUS = 0
begin
      print '        public DbSet<' + @tableName + '> ' + @tableName + ' { get; set; }'
      fetch next from tablesCursor into @tableName, @tableId, @tableDescription
end
close tablesCursor
deallocate tablesCursor

print '    }'
print ''
print '}'
print ''