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)
declare @schemaName varchar(50)

set @namespace = 'ConsoleApplication3.Data'
set @schemaName = 'dbo'


declare @typeMap table(sqlName varchar(50), dotNetName varchar(50), isNullable bit)
insert into @typeMap values('image', 'byte[]', 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('ServiceMethod')

print 'using System;'
print 'using System.Collections.Generic;'
print 'using System.ComponentModel.DataAnnotations;'
print 'using System.ComponentModel.DataAnnotations.Schema;'
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.object_id, Cast(x.value as varchar(4000)), s.name
		from sys.tables o
		left join sys.schemas s on s.schema_id = o.schema_id
		left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL) as x on o.name = x.objname collate database_default and x.name = 'MS_Description' 
		where o.type='U' 
		and o.name not in (select * from @excludedTables)
		and s.name = @schemaName
		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, @schemaName
while @@FETCH_STATUS = 0
begin
		print '    /// <summary>'
		print '    /// ' + Replace(Isnull(@tableDescription, ''), CHAR(13) + CHAR(10), CHAR(13) + CHAR(10) + '        /// ')
		print '    /// </summary>'
		print '    [Table("' + @tableName + '", Schema = "' + @schemaName + '")]'
		print '    public partial class ' + @tableName
		print '    {'

		declare columnsCursor cursor for
			select 
				c.column_id,
				c.name, 
				c.max_length,
				t.name,
				Cast(x.value as varchar(4000)) as [description],
				ic.key_ordinal,
				c.is_nullable,
				Cast(val.value as varchar(4000)) as ValidationType
			from sys.columns c
			inner join sys.types t on t.user_type_id = c.system_type_id
			left outer join sys.indexes i on i.object_id = @tableId and i.is_primary_key = 1
			left outer join sys.index_columns ic on ic.object_id = @tableId and ic.index_id = i.index_id and ic.column_id = c.column_id
			left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @tableName, 'column', NULL) as x on x.objname = c.name collate database_default and x.name = 'MS_Description'
			left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @tableName, 'column', NULL) as val on val.objname = c.name collate database_default and val.name = 'ValidationType'
			where c.object_id = @tableId
			order by c.name

			if 0=1 -- @tableName = 'User'
			begin
			select 
				c.column_id,
				c.name, 
				c.max_length,
				t.name,
				Cast(x.value as varchar(4000)) as [description],
				ic.key_ordinal,
				c.is_nullable,
				Cast(val.value as varchar(4000)) as ValidationType
			from sys.columns c
			inner join sys.types t on t.user_type_id = c.system_type_id
			left outer join sys.indexes i on i.object_id = @tableId and i.is_primary_key = 1
			left outer join sys.index_columns ic on ic.object_id = @tableId and ic.index_id = i.index_id and ic.column_id = c.column_id
			left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @tableName, 'column', NULL) as x on x.objname = c.name collate database_default and x.name = 'MS_Description'
			left outer join fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @tableName, 'column', NULL) as val on val.objname = c.name collate database_default and val.name = 'ValidationType'
			where c.object_id = @tableId
			order by c.name
			end

		declare @columnId int
		declare @columnName varchar(50)
		declare @columnLength int
		declare @columnType varchar(50)
		declare @columnDescription varchar(4000)
		declare @indexKeyOrdinal int
		declare @isNullable bit
		declare @oneToOneRelatedTables table(parentTable varchar(50), referencedTable varchar(50))
		declare @validationType varchar(50)
      
		open columnsCursor
		fetch next from columnsCursor into @columnId, @columnName, @columnLength, @columnType, @columnDescription, @indexKeyOrdinal, @isNullable, @validationType
		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)
			if @columnLength > 0
			begin
				set @dotNetType = Replace(IsNull(@dotNetType, ''), '@length', Cast(@columnLength as varchar(10)))
			end
			else
			begin
				set @dotNetType = Replace(IsNull(@dotNetType, ''), '@length', '')
			end
			print '        /// <summary>'
			print '        /// ' + Replace(Isnull(@columnDescription, ''), CHAR(13) + CHAR(10), CHAR(13) + CHAR(10) + '        /// ') 
			print '        /// </summary>'
			if @indexKeyOrdinal is not null
			begin
				print '        [Key]'
				print '        [Column(Order=' + cast(@indexKeyOrdinal as varchar) + ')]'
			end
			if @validationType is not null
			begin
				print '        [' + @validationType + ']'
			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)
			declare @oneToOneRelatedTableNames varchar(4000)
			set @oneToOneRelatedTableNames = ''
            
			fetch next from oneToOneRelations into @oneToOneRelatedTable
			while @@FETCH_STATUS = 0
			begin
					insert into @oneToOneRelatedTables values(@oneToOneRelatedTable, @tableName)
					set @oneToOneRelatedTableNames = @oneToOneRelatedTableNames + ',' + @oneToOneRelatedTable
					fetch next from oneToOneRelations into @oneToOneRelatedTable
			end
			close oneToOneRelations
			deallocate oneToOneRelations
            
			if len(@oneToOneRelatedTableNames) > 0
			begin
				set @oneToOneRelatedTableNames = substring(@oneToOneRelatedTableNames, 2, len(@oneToOneRelatedTableNames) - 1)
				print '        [ForeignKey("' + @oneToOneRelatedTableNames + '")]'
			end
			print '        public ' + @dotNetType + ' ' + @columnName + ' { get; set; }'
			print ''
            
			fetch next from columnsCursor into @columnId, @columnName, @columnLength, @columnType, @columnDescription, @indexKeyOrdinal, @isNullable, @validationType
		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, @schemaName
end

close tablesCursor


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

open tablesCursor

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

print ''
print '        public ' + db_name() + '()'
print '        { }'
print ''
print '        public ' + db_name() + '(string nameOrConnectionString)'
print '            : base(nameOrConnectionString)'
print '        { }'
print ''
print '        protected override void OnModelCreating(DbModelBuilder modelBuilder)'
print '        {'
print '            Database.SetInitializer<' + db_name() + '>(null);'
print '            base.OnModelCreating(modelBuilder);'
print '        }'
print ''

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