Skip to content

Instantly share code, notes, and snippets.

@ycaroafonso

ycaroafonso/Mapeamento do entityframework.sql

Last active September 23, 2015 20:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ycaroafonso/6e0fe27e3e24ec2634db to your computer and use it in GitHub Desktop.
Save ycaroafonso/6e0fe27e3e24ec2634db to your computer and use it in GitHub Desktop.
Download ZIP
Mapeamento de tabela no entityframework para o modo "Code First from Database"
Raw
Mapeamento do entityframework.sql
DECLARE @Tabela VARCHAR(200) = 'Nome da Tabela',
@TABLE_SCHEMA VARCHAR(200)= 'dbo'
-- Por Ycaro Afonso 2015-06-07
/*
DROP TABLE #VW_FRAMEWORK_COLUMN
DROP TABLE #VW_FRAMEWORK_TABLE
DROP TABLE #VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
DROP TABLE #VW_FRAMEWORK_CONSTRAINT
DROP TABLE #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES
DROP TABLE #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS
DROP TABLE #VW_FRAMEWORK_MAPEAMENTO_ENTITY_MODEL_BUILDER
*/
IF (SELECT COUNT(1)
FROM tempdb.dbo.sysobjects
WHERE name LIKE '#VW_FRAMEWORK_COLUMN%'
AND ftcatid IS NOT NULL) = 0
BEGIN
SELECT T.TABLE_SCHEMA,
T.TABLE_NAME,
C.COLUMN_NAME,
C.ORDINAL_POSITION,
C.COLUMN_DEFAULT,
C.IS_NULLABLE,
C.DATA_TYPE,
ISNULL(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION) TAMANHO,
C.NUMERIC_SCALE
INTO #VW_FRAMEWORK_COLUMN
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
SELECT T.TABLE_CATALOG,
T.TABLE_TYPE,
T.TABLE_SCHEMA,
T.TABLE_NAME
INTO #VW_FRAMEWORK_TABLE
FROM INFORMATION_SCHEMA.TABLES T
SELECT TC.TABLE_SCHEMA,
TC.TABLE_NAME,
KCU.COLUMN_NAME,
TC.CONSTRAINT_TYPE,
KCU.CONSTRAINT_NAME,
KCU2.TABLE_SCHEMA TABLE_SCHEMA_FOREIGN_KEY,
KCU2.TABLE_NAME TABLE_NAME_FOREIGN_KEY,
KCU2.COLUMN_NAME COLUMN_NAME_FOREIGN_KEY
INTO #VW_FRAMEWORK_CONSTRAINT
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = KCU2.TABLE_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
SELECT CS.TABLE_SCHEMA,
CS.TABLE_NAME,
CS.COLUMN_NAME,
CAST(SUM(CASE WHEN CS.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 1
ELSE 0
END) AS BIT) IS_PRIMARY_KEY,
CAST(SUM(CASE WHEN CS.CONSTRAINT_TYPE = 'PRIMARY KEY'
THEN COLUMNPROPERTY(OBJECT_ID(CS.TABLE_SCHEMA + '.' + CS.TABLE_NAME), CS.COLUMN_NAME,
'IsIdentity')
ELSE 0
END) AS BIT) IS_IDENTITY,
MIN(CS.TABLE_SCHEMA_FOREIGN_KEY) TABLE_SCHEMA_FOREIGN_KEY,
MIN(CS.TABLE_NAME_FOREIGN_KEY) TABLE_NAME_FOREIGN_KEY,
MIN(COLUMN_NAME_FOREIGN_KEY) COLUMN_NAME_FOREIGN_KEY
INTO #VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
FROM #VW_FRAMEWORK_CONSTRAINT CS
GROUP BY CS.TABLE_SCHEMA,
CS.TABLE_NAME,
CS.COLUMN_NAME
SELECT T.TABLE_TYPE,
C.TABLE_SCHEMA,
C.TABLE_NAME,
C.COLUMN_NAME,
CASE C.DATA_TYPE
WHEN 'varchar' THEN 'string'
WHEN 'datetime' THEN 'DateTime'
WHEN 'date' THEN 'DateTime'
WHEN 'char' THEN 'string'
WHEN 'bit' THEN 'bool'
WHEN 'image' THEN 'byte[]'
WHEN 'numeric' THEN 'decimal'
ELSE C.DATA_TYPE
END + CASE C.DATA_TYPE
WHEN 'varchar' THEN ''
WHEN 'image' THEN ''
ELSE CASE C.IS_NULLABLE
WHEN 'YES' THEN '?'
ELSE ''
END
END Tipo,
-- Annotations
CASE WHEN C.DATA_TYPE = 'varchar'
AND C.IS_NULLABLE = 'NO' THEN '[Required]'
WHEN CCIU.IS_PRIMARY_KEY = 1 THEN '[Key]'
ELSE ''
END
--
+ CASE WHEN QTDE_CHAVE_PRIMARIA > 1
AND CCIU.IS_PRIMARY_KEY = 1
THEN '[Column(Order = '
+ CAST(ROW_NUMBER() OVER (ORDER BY CCIU.IS_PRIMARY_KEY DESC) AS VARCHAR(10)) + ')]'
ELSE ''
END
--
+ CASE WHEN C.DATA_TYPE = 'varchar' AND C.TAMANHO > -1 THEN '[StringLength(' + CAST(C.TAMANHO AS VARCHAR(20)) + ')]'
ELSE ''
END + CASE WHEN CCIU.IS_PRIMARY_KEY = 1
AND CCIU.IS_IDENTITY = 0 THEN '[DatabaseGenerated(DatabaseGeneratedOption.None)]'
ELSE ''
END Annotation,
--
ISNULL(CCIU.IS_PRIMARY_KEY, 0) IS_PRIMARY_KEY,
C.IS_NULLABLE IS_NULLABLE_COLUMN_INTERNA,
CCIU.TABLE_SCHEMA_FOREIGN_KEY TABLE_SCHEMA_REFERENCIA,
CCIU.TABLE_NAME_FOREIGN_KEY TABLE_NAME_REFERENCIA,
CCIU.COLUMN_NAME_FOREIGN_KEY COLUMN_NAME_REFERENCIA_EXTERNA,
CCIU.IS_PRIMARY_KEY IS_PRIMARY_KEY_EXTERNA,
CCIU.TABLE_NAME_FOREIGN_KEY
+ CASE WHEN (SELECT COUNT(1)
FROM #VW_FRAMEWORK_CONSTRAINT
WHERE TABLE_SCHEMA = C.TABLE_SCHEMA
AND TABLE_NAME = C.TABLE_NAME
AND TABLE_SCHEMA_FOREIGN_KEY = CCIU.TABLE_SCHEMA_FOREIGN_KEY
AND TABLE_NAME_FOREIGN_KEY = CCIU.TABLE_NAME_FOREIGN_KEY) > 1
OR C.TABLE_NAME = CCIU.TABLE_NAME_FOREIGN_KEY
OR CCIU.COLUMN_NAME <> CCIU.COLUMN_NAME_FOREIGN_KEY -- 2015-9-23
THEN REPLACE(CCIU.COLUMN_NAME, CCIU.COLUMN_NAME_FOREIGN_KEY, '')
ELSE ''
END NomePropriedadeReferenciaInterna,
CCIU.TABLE_NAME
+ CASE WHEN (SELECT COUNT(1)
FROM #VW_FRAMEWORK_CONSTRAINT
WHERE TABLE_SCHEMA = C.TABLE_SCHEMA
AND TABLE_NAME = C.TABLE_NAME
AND TABLE_SCHEMA_FOREIGN_KEY = CCIU.TABLE_SCHEMA_FOREIGN_KEY
AND TABLE_NAME_FOREIGN_KEY = CCIU.TABLE_NAME_FOREIGN_KEY) > 1
THEN REPLACE(CCIU.COLUMN_NAME, CCIU.COLUMN_NAME_FOREIGN_KEY, '')
ELSE ''
END + CASE WHEN C.TABLE_NAME = CCIU.TABLE_NAME_FOREIGN_KEY THEN 'Filhos'
ELSE ''
END NomePropriedadeReferenciaExterna
INTO #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES
FROM (SELECT T.TABLE_TYPE, -- Retorna apenas tabelas que possuí chave primária
T.TABLE_SCHEMA,
T.TABLE_NAME,
COUNT(1) QTDE_CHAVE_PRIMARIA
FROM #VW_FRAMEWORK_CONSTRAINT CT
JOIN #VW_FRAMEWORK_TABLE T ON T.TABLE_SCHEMA = CT.TABLE_SCHEMA
AND T.TABLE_NAME = CT.TABLE_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY T.TABLE_TYPE,
T.TABLE_SCHEMA,
T.TABLE_NAME) T
JOIN #VW_FRAMEWORK_COLUMN C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
LEFT JOIN #VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO CCIU ON C.TABLE_SCHEMA = CCIU.TABLE_SCHEMA
AND C.TABLE_NAME = CCIU.TABLE_NAME
AND C.COLUMN_NAME = CCIU.COLUMN_NAME
LEFT JOIN #VW_FRAMEWORK_COLUMN C2 ON C2.TABLE_SCHEMA = CCIU.TABLE_SCHEMA_FOREIGN_KEY
AND C2.TABLE_NAME = CCIU.TABLE_NAME_FOREIGN_KEY
AND C2.COLUMN_NAME = CCIU.COLUMN_NAME_FOREIGN_KEY
SELECT *
INTO #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS
FROM (SELECT TABLE_TYPE,
TABLE_SCHEMA,
TABLE_NAME Classe,
'public' Permissao,
Tipo,
COLUMN_NAME NomePropriedade,
Annotation,
--
'' NomePropriedadeReferenciaExterna,
--
COLUMN_NAME COLUMN_NAME_REFERENCIA_INTERNA,
IS_PRIMARY_KEY IS_PRIMARY_KEY_INTERNA,
IS_NULLABLE_COLUMN_INTERNA,
'' TABLE_NAME_REFERENCIA_EXTERNA,
'' COLUMN_NAME_REFERENCIA_EXTERNA,
'' IS_PRIMARY_KEY_EXTERNA,
'' IS_NULLABLE_COLUMN_EXTERNA,
1 Parte
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES
UNION ALL
SELECT TABLE_TYPE,
TABLE_SCHEMA,
TABLE_NAME Classe,
'public virtual' Permissao,
TABLE_NAME_REFERENCIA Tipo,
NomePropriedadeReferenciaInterna NomePropriedade,
'' Annotation,
--
NomePropriedadeReferenciaExterna,
--
COLUMN_NAME COLUMN_NAME_REFERENCIA_INTERNA,
'' IS_PRIMARY_KEY_INTERNA,
IS_NULLABLE_COLUMN_INTERNA,
TABLE_NAME_REFERENCIA TABLE_NAME_REFERENCIA_EXTERNA,
COLUMN_NAME_REFERENCIA_EXTERNA,
'' IS_PRIMARY_KEY_EXTERNA,
'' IS_NULLABLE_COLUMN_EXTERNA,
2 Parte
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES
WHERE TABLE_NAME_REFERENCIA IS NOT NULL
UNION ALL
SELECT TABLE_TYPE,
TABLE_SCHEMA_REFERENCIA,
TABLE_NAME_REFERENCIA Classe,
'public virtual' Permissao,
CASE WHEN IS_PRIMARY_KEY = 1
AND ((SELECT COUNT(1) -- Verifica se a tabela filha tem mais de uma chave primaria, se tiver o Tipo é ICollection
FROM #VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
WHERE TABLE_NAME = MEP.TABLE_NAME
AND TABLE_SCHEMA = MEP.TABLE_SCHEMA
AND IS_PRIMARY_KEY = 1) = 1) THEN TABLE_NAME
ELSE 'ICollection<' + TABLE_NAME + '>'
END Tipo,
NomePropriedadeReferenciaExterna NomePropriedade,
'' Annotation,
--
NomePropriedadeReferenciaInterna NomePropriedadeReferenciaExterna,
--
'' COLUMN_NAME_REFERENCIA_INTERNA,
'' IS_PRIMARY_KEY_INTERNA,
NULL IS_NULLABLE_COLUMN_INTERNA,
TABLE_NAME TABLE_NAME_REFERENCIA_EXTERNA,
COLUMN_NAME COLUMN_NAME_REFERENCIA_EXTERNA,
IS_PRIMARY_KEY_EXTERNA,
IS_NULLABLE_COLUMN_INTERNA IS_NULLABLE_COLUMN_EXTERNA,
3 Parte
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES MEP
WHERE TABLE_NAME_REFERENCIA IS NOT NULL) AS X
SELECT Parte,
TABLE_SCHEMA,
TipoClasse,
Classe,
'modelBuilder.Entity<' + Classe + '>().' + Mapeamento Mapeamento
INTO #VW_FRAMEWORK_MAPEAMENTO_ENTITY_MODEL_BUILDER
FROM (SELECT 1 Parte,
MEPF.TABLE_SCHEMA,
MEPF.TABLE_TYPE TipoClasse,
Classe,
CASE WHEN MEPF.Tipo = 'string'
THEN 'Property(e => e.' + MEPF.NomePropriedade COLLATE Latin1_General_CS_AS
+ ').IsUnicode(false);'
WHEN MEPF.Tipo = 'decimal'
OR MEPF.Tipo = 'decimal?'
THEN 'Property(e => e.' + MEPF.NomePropriedade COLLATE Latin1_General_CS_AS
+ ').HasPrecision(' + CAST(C.TAMANHO AS VARCHAR(10)) + ', '
+ CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ');'
END Mapeamento
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS MEPF
JOIN #VW_FRAMEWORK_COLUMN C ON C.COLUMN_NAME = MEPF.NomePropriedade
AND C.TABLE_NAME = MEPF.Classe
AND C.TABLE_SCHEMA = MEPF.TABLE_SCHEMA
WHERE Permissao = 'public'
AND MEPF.Tipo IN ('string', 'decimal', 'decimal?')
UNION ALL
SELECT 2 Parte,
MEPF.TABLE_SCHEMA,
MEPF.TABLE_TYPE TipoClasse,
MEPF.Classe,
'HasMany(e => e.' + MEPF.NomePropriedade + ').'
+ (CASE WHEN MEPF.IS_NULLABLE_COLUMN_INTERNA = 'NO'
OR MEPF.IS_NULLABLE_COLUMN_EXTERNA = 'NO' THEN 'WithRequired'
ELSE 'WithOptional'
END) + '(e => e.' + MEPF.NomePropriedadeReferenciaExterna + ').HasForeignKey(e => e.'
+ MEPF.COLUMN_NAME_REFERENCIA_EXTERNA + ').WillCascadeOnDelete(false);'
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS MEPF
WHERE MEPF.Tipo LIKE 'ICollection%'
AND ((ISNULL(MEPF.IS_NULLABLE_COLUMN_INTERNA, 'NO') = 'NO'
OR MEPF.IS_NULLABLE_COLUMN_EXTERNA = 'NO')
OR (MEPF.Classe = MEPF.TABLE_NAME_REFERENCIA_EXTERNA))
-- 1p1
UNION ALL
SELECT 3 Parte,
MEPF.TABLE_SCHEMA,
MEPF.TABLE_TYPE TipoClasse,
Classe,
'HasOptional(e => e.' + MEPF.NomePropriedade + ').WithRequired(e => e.'
+ MEPF.NomePropriedadeReferenciaExterna + ');' Mapeamento
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS MEPF
WHERE IS_PRIMARY_KEY_EXTERNA = 1
AND (SELECT COUNT(1)
FROM #VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
WHERE TABLE_NAME = MEPF.TABLE_NAME_REFERENCIA_EXTERNA
AND IS_PRIMARY_KEY = 1) = 1) AS X
END
DECLARE @Classe VARCHAR(MAX)= 'public ' + @Tabela + '() { ' + CHAR(13) + CHAR(10)
SELECT @Classe = @Classe + NomePropriedade + ' = new ' + REPLACE(Tipo, 'ICollection', 'HashSet') + '();' + CHAR(13)
+ CHAR(10)
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS
WHERE Classe = @Tabela
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND Tipo LIKE '%ICollection%'
ORDER BY Parte
SET @Classe = @Classe + '}'
SELECT @Classe = @Classe + CHAR(13) + CHAR(10) + Annotation + CASE WHEN Annotation = '' THEN ''
ELSE CHAR(13) + CHAR(10)
END + Permissao + ' ' + Tipo + ' ' + NomePropriedade
+ ' { get; set; }' + CHAR(13) + CHAR(10)
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS
WHERE Classe = @Tabela
AND TABLE_SCHEMA = @TABLE_SCHEMA
ORDER BY Parte
SELECT @Classe Classe
SELECT Mapeamento
FROM #VW_FRAMEWORK_MAPEAMENTO_ENTITY_MODEL_BUILDER
WHERE Classe = @Tabela
AND TABLE_SCHEMA = @TABLE_SCHEMA
-- Por Ycaro Afonso 2015-06-07
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment