{"id":1919,"date":"2023-11-28T10:48:14","date_gmt":"2023-11-27T23:48:14","guid":{"rendered":"https:\/\/ntsblog.homedev.com.au\/?p=1919"},"modified":"2024-11-08T10:22:52","modified_gmt":"2024-11-07T23:22:52","slug":"generate-entityframework-codefirst-model-from-sqlserver","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2023\/11\/28\/generate-entityframework-codefirst-model-from-sqlserver\/","title":{"rendered":"Generate EntityFramework CodeFirst Model from SQLServer"},"content":{"rendered":"<div id=\"ntsbl-1792995299\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<h2 class=\"wp-block-heading\">The issue<\/h2>\n\n\n\n<p>You are creating your code first entity framework classes and you need to create a Dto class (Data Transfer Object) to represent your table. <\/p>\n\n\n\n<p>Lots of typing required&#8230; or<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The solution<\/h2>\n\n\n\n<p>The following SQL statement will generate and print out a model class definition.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">Declare @properties nvarchar(max)\nDeclare @tableName nvarchar(128)\n--================================================\n-- Parameters\nSet @tableName = 'MyTable'\n--================================================\nset @properties = ''\nselect\n       @properties = @properties +\n       Case When c.is_computed = 1 Then '    [NotMapped]' + Char(10) else '' End +\n       Case When c.is_identity = 1 Then '    [Key]' + Char(10) else '' End +\n       Case When c.system_type_id = 189 Then '    [Timestamp]' + Char(10) else '' End\n       + '    public '\n        + Case c.system_type_id\n              When 36 Then 'Guid' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 48 Then 'byte' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 52 Then 'short' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 56 Then 'int' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 40 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 43 Then 'DateTimeOffset' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 60 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 58 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 61 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 62 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 104 Then 'bool' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 106 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 108 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 127 Then 'long' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 165 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 189 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End -- RowVersion\n              Else 'string'\n        end\n        + ' ' + c.name + ' { get; set; }' + Char(10)\nfrom sys.tables t\n       inner join sys.columns c on t.object_id = c.object_id\nwhere t.name  = @tableName\n \nprint '[Table(\"' + @tableName + '\")]'\nprint 'public class '+  @tableName + 'Dto'\nprint '{'\nprint @properties\nprint '}'<\/code><\/pre>\n\n\n\n<p>Work smarter not harder. <\/p>\n\n\n\n<p>The following SQL with generate a base class..<\/p>\n\n\n\n<p>Your welcome \ud83d\ude42<\/p>\n\n\n\n<p>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<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Schema Version<\/h3>\n\n\n\n<p>If your able has is in a schema the following will support generating a model that supports the schema<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">Declare @properties nvarchar(max)\nDeclare @tableName nvarchar(128)\nDeclare @schema nvarchar(128)\n--================================================\n-- Parameters\nSet @tableName = 'Table'\nSet @schema = 'Schema'\n--================================================\nset @properties = ''\nselect\n       @properties = @properties +\n       Case When c.is_computed = 1 Then '    [NotMapped]' + Char(10) else '' End +\n       Case When c.is_identity = 1 Then '    [Key]' + Char(10) else '' End +\n       Case When c.system_type_id = 189 Then '    [Timestamp]' + Char(10) else '' End\n       + '    public '\n        + Case c.system_type_id\n              When 36 Then 'Guid' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 48 Then 'byte' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 52 Then 'short' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 56 Then 'int' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 40 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 43 Then 'DateTimeOffset' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 60 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 58 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 61 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 62 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 104 Then 'bool' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 106 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 108 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 127 Then 'long' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 165 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 189 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End -- RowVersion\n              Else 'string'\n        end\n        + ' ' + c.name + ' { get; set; }' + Char(10)\nfrom sys.tables t\n       inner join sys.columns c on t.object_id = c.object_id\n\t   inner join sys.schemas s on t.schema_id = s.schema_id\nwhere t.name  = @tableName and s.name = @schema\n \nprint '[Table(\"' + @tableName + '\", Schema=\"' + @schema + '\")]'\nprint 'public class '+  @tableName + 'Dto'\nprint '{'\nprint @properties + '}'<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Update for .Net 8.0<\/h2>\n\n\n\n<p>With the new string property changes this is an updated version <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">Declare @properties varchar(max)\nDeclare @tableName nvarchar(128)\n--================================================\n-- Parameters\nSet @tableName = 'MyTable'\n--================================================\nset @properties = ''\nselect\n       @properties = @properties +\n       Case When c.is_computed = 1 Then '    [NotMapped]' + Char(10) else '' End +\n       Case When c.is_identity = 1 Then '    [Key]' + Char(10) else '' End +\n       Case When c.system_type_id = 189 Then '    [Timestamp]' + Char(10) else '' End\n       + '    public '\n        + Case c.system_type_id\n              When 36 Then 'Guid' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 48 Then 'byte' + Case when c.is_nullable = 1 Then '?' else '' End\n\t\t\t  When 52 Then 'short' + Case when c.is_nullable = 1 Then '?' else '' End\n\t\t\t  When 56 Then 'int' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 40 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 43 Then 'DateTimeOffset' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 60 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 58 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 61 Then 'DateTime' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 62 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 104 Then 'bool' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 106 Then 'double' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 108 Then 'decimal' + Case when c.is_nullable = 1 Then '?' else '' End\n              When 127 Then 'long' + Case when c.is_nullable = 1 Then '?' else '' End\n\t\t\t  When 165 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End\n\t\t\t  When 189 Then 'byte[]' + Case when c.is_nullable = 1 Then '?' else '' End -- RowVersion\n              Else \n\t\t\t\t Case when c.is_nullable = 0 Then 'required ' else '' End + 'string' + Case when c.is_nullable = 1 Then '?' else '' End\n        end\n        + ' ' + c.name + ' { get; set; }' + Char(10)\nfrom sys.tables t\n       inner join sys.columns c on t.object_id = c.object_id\nwhere t.name  = @tableName\n \nprint '[Table(\"' + @tableName + '\")]'\nprint 'public class '+  @tableName + 'Dto'\nprint '{'\nprint @properties\nprint '}'<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Generate EntityFramework Entity Class from SQL Server Table<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[37,5,54,13],"tags":[],"class_list":["post-1919","post","type-post","status-publish","format-standard","hentry","category-net-core","category-c","category-entityframework","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1919","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=1919"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/1919\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=1919"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=1919"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=1919"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}