Do you prefer to use Code First (POCO) than Model First (EDMX) when using Entity Framework? Writing POCO classess may waste your time. So why we don’t simplyfiy our work? Instead, use this T-SQL script to generate POCO classes. This's code snippet will generate VB.NET code, if you're looking for C# version, then try my previous post
/* ----- CONFIGURATION ----- */ declare @table varchar(max), --table name @enableAnnotation bit; --show data annotation? set @table = 'TableName'; set @enableAnnotation = 1; /* ----- END OF CONFIGURATION ----- */ declare @column varchar(max), @length int, @type varchar(max), @isNullable bit, @pocoType varchar(max), @attribute varchar(max), @poco varchar(max), @linebreak char(2), @enter char(1), @tab char(1), @totalPK int, @indexPK int, @isPK bit, @isFirstRow bit, @identitySeed bigint, @identityIncrement int; declare @identities table(ColumnName varchar(max), Seed bigint, Increment int); declare @primaryKeys table(ColumnName varchar(max)); set @enter = char(13); set @tab = char(9); set @linebreak = @enter + @tab; set @isFirstRow = 1; set nocount on; --find all primary key insert into @primaryKeys(ColumnName) select ccu.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME where tc.CONSTRAINT_TYPE = 'Primary Key' and tc.TABLE_NAME = @table; select @totalPK = count('') from @primaryKeys; set @indexPK = 0; --find all identity columns insert into @identities(ColumnName, Seed, Increment) select id.name, cast(id.seed_value as bigint), cast(id.increment_value as int) from sys.identity_columns as id inner join sys.objects as so on object_name(id.object_id)=so.name where so.type='u' and so.name = @table and id.is_identity = 1 declare cursorPoco cursor fast_forward for select COLUMN_NAME ColumnName, CHARACTER_MAXIMUM_LENGTH, DATA_TYPE, case IS_NULLABLE when 'YES' then 1 when 'NO' then 0 else null end IsNullable from information_schema.columns where TABLE_NAME = @table; if (@enableAnnotation = 1) print '<Table("' + @table + '")>'; print 'Public Class ' + @table; open cursorPoco; fetch cursorPoco into @column, @length, @type, @isNullable; while @@FETCH_STATUS = 0 begin set @pocoType = ''; set @poco = 'Public Property '; set @isPK = 0; set @identitySeed = null; set @identityIncrement = null; if (@isFirstRow = 1) begin set @attribute = @tab; set @isFirstRow = 0; end else begin set @attribute = @linebreak; end --determine wether column is primary key or not if exists (select 1 from @primaryKeys where ColumnName = @column) begin set @attribute = @attribute + '<Key()>' + @linebreak; set @isPK = 1; end if (@isPK = 1 and @totalPK > 1) begin set @attribute = @attribute + '<Column("' + @column + '", Order = ' + cast(@indexPK as varchar) + ')>' + @linebreak; set @indexPK = @indexPK + 1; end else set @attribute = @attribute + '<Column("' + @column + '")>' + @linebreak; --determine wether column is identity or not select top 1 @identitySeed = Seed, @identityIncrement = Increment from @identities where ColumnName = @column; if (@identityIncrement is not null and @identitySeed is not null) begin set @attribute = @attribute + '<DatabaseGenerated(DatabaseGeneratedOption.Identity)>' + @linebreak; end if (@type in ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext')) begin set @pocoType = 'String'; if (@length is not null and @length > 0) set @attribute = @attribute + '<MaxLength(' + cast(@length as varchar) + ')>' + @linebreak; if (@isNullable = 0) set @attribute = @attribute + '<Required>' + @linebreak; if (@type in ('ntext', 'text')) set @attribute = @attribute + '<DataType(DataType.MultilineText)>' + @linebreak; end else begin if (@type = 'int') set @pocoType = 'Int32'; else if (@type = 'bigint') set @pocoType = 'Int64'; else if (@type = 'tinyint') set @pocoType = 'Int16'; else if (@type in ('date', 'datetime', 'time')) begin set @pocoType = 'System.DateTime'; if (@type = 'date') set @attribute = @attribute + '<DataType(DataType.Date)>' + @linebreak; else if (@type = 'time') set @attribute = @attribute + '<DataType(DataType.Time)>' + @linebreak; end else if (@type = 'bit') set @pocoType = 'Boolean'; else if (@type in ('decimal', 'money', 'numeric', 'smallmoney')) begin set @pocoType = 'Decimal'; if (@type in ('money', 'smallmoney')) set @attribute = @attribute + '<DataType(DataType.Currency)>' + @linebreak; end else set @pocoType = @type; if (@isNullable = 1) set @pocoType = 'System.Nullable(Of ' + @pocoType + ')'; end if (@enableAnnotation = 1) set @poco = @attribute + @poco + @column + ' As ' + @pocoType; else set @poco = @tab + @poco + @column + ' As ' + @pocoType; print @poco; fetch cursorPoco into @column, @length, @type, @isNullable; end close cursorPoco; deallocate cursorPoco; print 'End Class'; set nocount off;
