SET FMTONLY OFF Descontinuado E substituído por procedures de sistemas que não resolvem o problema no uso com EF6

Question

Olá,

Em desenvolvimento de um sistema corporativo, me deparei com um problema que sinceramente não deveria existir.

Estou envolvido num projeto que utiliza Entity Framework 6, com acesso a stored procedures num banco de dados Sql Server 2012.

Por questões inerentes ao EF6, em algumas situações, não consigo obter as estruturas ditas Complex Types de retorno das procedures, e pesquisando na internet, descobri o recurso SET FMTONLY OFF, que permite ao Entity Framework enxergar as estruturas complexas retornadas, permitindo que eu acesse essas estruturas via código.

Mas, ao mesmo tempo, no site da Microsoft que explica o recurso, existe um comentário que informa que esse recurso foi descontinuado, e que em lugar dele, foram criadas 4 novas procedures de sistemas, conforme texto copiado do site :

<token space="preserve" style="box-sizing:border-box;">Observação</token>

Não use este recurso. <sentence data-original-title="" data-source="This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)." data-source-localized="0" style="box-sizing:border-box;" title="">Esse recurso foi substituído pelo sp_describe_first_result_set (Transact-SQL),sp_describe_undeclared_parameters (Transact-SQL), DM exec_describe_first_result_set (Transact-SQL), e DM exec_describe_first_result_set_for_object (Transact-SQL).</sentence>

Pois bem, muitas das procedures usadas pelo sistema que estou envolvido, utilizam internamente tabelas temporárias, e se observarmos na descrição de erros dessas procedures, veremos que todas não aceitam procedures que usem tabelas temporárias.

Portanto, qual caminho seguir ? A Microsoft descontinua e não indica o uso desse recurso em produção, e informa que agora devemos passar a usar essas procedures, mas todas não atendem a uma necessidade que o recurso anterior atendia com 100% de sucesso.

Portanto, venho aos senhores fazer as seguintes perguntas:

1 - Não posso mesmo mais usar o recurso SET FMTONLY OFF no meu sistema ?

2 - Cheguei a implementar o comando nas procedure, no banco SQL Server 2012, e funcionou. Mesmo assim, não posso mais usar ou posso usar, mas com resalvas ?

3 - O que vai acontecer se eu mantiver o recurso e o banco sofrer updates ?

4 - Como usar um recurso que permita o EF6 enxergar os Complex Types das procedures, nos casos onde temos procedures que retornam resultados sobre tabelas temporárias ou mesmo que manipulam tabelas temporárias internamente ?

Para ilustrar o problema, encontrei esse site https://connect.microsoft.com/SQLServer/feedback/details/782038/set-fmtonly-deprecated-in-sql-2012-without-full-replacement , que menciona que o problema já havia sido detectado e aberto um pedido de correção, mas que a Microsoft fechou o chamado e não corrigiu o bug, alegando não era prioridade.

Embora o site seja antigo, pelas minhas pesquisas, não tenho achado nada que diga o contrário.

Ou seja, um recurso que funciona é descontinuado, os recursos substitudos não resolvem o problema, e a empresa vem alegar que não é prioridade fazer esse ajuste.... e como ficam os milhões de usuários que usam a plataforma MS (Asp.Net + EF + SQL Server + Procedures) ???

Atenciosamente,

---

Olivertech

Answer 1

Maclau,

Sim você pode usar mantendo o nível de compatibilidade do seu banco de dados na versão 110 e até que o SQL Server 2012 mantenha em uso e suportado pela Microsoft.

Mas não seria o caso de ao invés de usar temp table, implementar estas tabelas como auxiliares em seu ambiente?

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 2

Olá Junior,

Estou tendo enorme dificuldade em fazer o EF6 se entender com as procedures do sistema que estamos desenvolvendo.

A empresa tem um enorme legado e algumas procedures são extensas e tem muita lógica interna.

Abaixo, listo uma das procedures que não consigo obter a lista de retorno. O resultado desta procedure do exemplo, pode ser um único número (no formato de varchar) ou uma lista de números (todos como varchar), conforme pode ser visto na imagem em anexo.

Só que se tendo acessar a procedure pelo EF, me retorna uma lista de um único elemento com NULL.

Abaixo a procedure -  reparar que tem um trecho logo no inicio (com parte comentada) que tentei fazer alguns artifícios para tentar fazer reconhecer o retorno da procedure, conseguindo identificar o Complex Type. Em todos os casos, eu não consigo obter o retorno. Eu consigo reconhecer o Complex Type, mas a procedure não retorna o que deveria.

ALTER PROCEDURE [dbo].[usp_NSC_DistribuiNumerosDaSorteOtimizadoPorCarga]
----------------------------------------------------------------------------------------------
-- P a r a m e t r o s
----------------------------------------------------------------------------------------------
    @DtCompetencia VARCHAR(6) = NULL,
    @QtdNumerosDaSorte INT = NULL,
    @TamanhoSerie INT = 100000,
    @SerieId INT = NULL
AS
    BEGIN

        SET NOCOUNT ON;

		---------------------------------------------------------------------------
		-- Declarando + Dummy Query para permitir o EF obter o Complex Data Type
		---------------------------------------------------------------------------
		--IF (1=0) 
		--BEGIN
			SET FMTONLY OFF;
		--	DECLARE @TabelaResultado TABLE (
		--		Numero VARCHAR(5) NOT NULL
		--	);
		--	SELECT Numero FROM @TabelaResultado WHERE (1=0)
		--END

----------------------------------------------------------------------------------------------
--	D e c l a r a ç ã o    d e    V a r i á v e i s
----------------------------------------------------------------------------------------------
		IF OBJECT_ID('tempdb..#valoresCarga') is not null
			DROP TABLE #valoresCarga
		IF OBJECT_ID('tempdb..#valoresCargaPorNumero') is not null
			DROP TABLE #valoresCargaPorNumero

		CREATE TABLE #valoresCarga (
			[Id] INT IDENTITY(1,1),
			[NumeroProcesso] VARCHAR(20),
			[SerieId] INT,
			[Numero] INT,
			[Carga] NUMERIC(19, 10),
			[Multiplo] NUMERIC(19, 10),
			[Subtotal] NUMERIC(19, 10)
		)
		CREATE NONCLUSTERED INDEX ind_vc_1 ON #valoresCarga (Numero)
		CREATE NONCLUSTERED INDEX ind_vc_2 ON #valoresCarga (SerieId,Numero)

		CREATE TABLE #valoresCargaPorNumero (
			Id INT IDENTITY(1,1),
			Numero INT,
			Carga NUMERIC(16, 10)
		)
		CREATE NONCLUSTERED INDEX ind_vcn_1 ON #valoresCargaPorNumero (Numero)
		
		DECLARE @TabelaResultado TABLE (
			Numero VARCHAR(5) NOT NULL
		)
		
		DECLARE @qtdResultado INT

----------------------------------------------------------------------------------------------
--	L ó g i c a     P r i n c i p a l
----------------------------------------------------------------------------------------------

        BEGIN TRY

	-- Busca o valor da competencia aberta 
            IF (@DtCompetencia is null)
				SELECT TOP 1 @DtCompetencia = DT_COMPETENCIA
				FROM RECEITAS (NOLOCK)
				WHERE Fechado = 0

	-- Preenche as tabelas temporárias com os dados 
			INSERT INTO #valoresCarga
			SELECT pr.NR_PROCESSO, ser.Id as SERIE_ID, num.NUMERO, left(nrval.Vlr_Original,8) as Carga, left(msor.Multiplo,8) as Multiplo,
				 left((nrval.Vlr_Original * msor.Multiplo), charindex('.', (nrval.Vlr_Original * msor.Multiplo)) - 1) as Subtotal
			FROM SERIES ser
			INNER JOIN NRVALIDOS nrval WITH (NOLOCK) on ser.Id = nrval.SER_ID and ser.DT_COMPETENCIA = @DtCompetencia
			INNER JOIN SERIE_DISTRIBUICOES sed WITH (NOLOCK) on ser.SED_ID = sed.id
			INNER JOIN PLANOS pla WITH (NOLOCK) on sed.PLANO_ID = pla.Id
			INNER JOIN CfgPlano cfpla with (NOLOCK) on pla.id = cfpla.IdPlano and cfpla.ESerieFechada =0
			INNER JOIN PROCESSOS pr WITH (NOLOCK) on pla.PROC_ID = pr.Id 
			INNER JOIN PROCESSOS_MULTIPLOS msor WITH (NOLOCK) on pr.Id = msor.PROC_ID
			INNER JOIN HISTORICO_PROCESSOS hpro WITH (NOLOCK) on pr.Id = hpro.PROC_ID and hpro.SorteioProprio = 'N'
			RIGHT JOIN NUMEROS num on num.NUMERO = nrval.NUMERO
			WHERE	/*(pr.NR_PROCESSO IS NULL
					 OR pr.NR_PROCESSO = ISNULL(@NumeroProcesso, pr.NR_PROCESSO))
			AND		*/num.NUMERO < @TamanhoSerie

			INSERT INTO #valoresCargaPorNumero
			SELECT a.Numero, sum(isnull(b.Subtotal, 0)) as Carga
			FROM #valoresCarga a
			LEFT JOIN #valoresCarga b on a.id = b.id
			GROUP BY a.Numero
			
	-- Obtém '@QtdNumerosDaSorte' números da sorte aleatórios com menor carga para uma série determinada ou não por parâmetro
			IF(@SerieId IS NOT NULL)
			BEGIN			
				INSERT INTO @TabelaResultado
				SELECT TOP (ISNULL(@QtdNumerosDaSorte, 1)) num.Numero
				FROM #valoresCarga vc
				JOIN #valoresCargaPorNumero vcn on vc.Numero = vcn.Numero
				RIGHT JOIN NUMEROS num on num.Numero = vcn.Numero
				WHERE num.NUMERO < @TamanhoSerie
				AND num.NUMERO NOT IN (SELECT	Numero
									   FROM		#valoresCarga
									   WHERE	SerieId = ISNULL(@SerieID, vc.SerieId))
				GROUP BY num.Numero, vcn.Carga
				ORDER BY vcn.Carga asc, newid()
			END
			ELSE
			BEGIN
				INSERT INTO @TabelaResultado
				SELECT TOP (ISNULL(@QtdNumerosDaSorte, 1)) cn.Numero
				FROM #valoresCargaPorNumero cn
				ORDER BY cn.Carga asc, newid()
			END
			
			SELECT @qtdResultado = COUNT (*)
			FROM @TabelaResultado

	-- Exibe o resultado da procedure
			IF (@qtdResultado = ISNULL(@QtdNumerosDaSorte, 1))
				SELECT RIGHT(CASE 
								WHEN @TamanhoSerie = 1000 THEN '000'
								WHEN @TamanhoSerie = 10000 THEN '0000'
								WHEN @TamanhoSerie = 100000 THEN '00000'
							 END + CONVERT(VARCHAR(5), Numero), 5) AS Numero
				FROM @TabelaResultado
			ELSE
				SELECT -1

        END TRY	

--------------------------------------------------------------------------------------------
-- T r a t a m e n t o     d e     E r r o 	
--------------------------------------------------------------------------------------------

        BEGIN CATCH

            SELECT
					--ERROR_PROCEDURE() AS ErrorProcedure,
                    --ERROR_LINE() AS ErrorLine,
                    --ERROR_NUMBER() AS ErrorNumber,
                    ERROR_MESSAGE() AS ErrorMessage
                    --ERROR_SEVERITY() AS ErrorSeverity,
                    --ERROR_STATE() AS ErrorState;

        END CATCH

    END

Abaixo, o meu código usado pra chamar a procedure :

        public List<string> DistribuiNumerosDaSorteOtimizadoPorCarga(NSC_DistribuiNumerosDaSorteOtimizadoPorCargaDTO dto)
        {
            try
            {
                List<string> result = new List<string>();

                List<NumerosDaSorte_ResultDTO> procResult = _context.Database.SqlQuery<NumerosDaSorte_ResultDTO>("usp_NSC_DistribuiNumerosDaSorteOtimizadoPorCarga " +
                                                                     "@DtCompetencia, " +
                                                                     "@QtdNumerosDaSorte, " +
                                                                     "@TamanhoSerie, " +
                                                                     "@SerieId",
                                                    new SqlParameter("DtCompetencia", (object)dto.DtCompetencia ?? DBNull.Value),
                                                    new SqlParameter("QtdNumerosDaSorte", (object)dto.QtdNumerosDaSorte ?? DBNull.Value),
                                                    new SqlParameter("TamanhoSerie", (object)dto.TamanhoSerie ?? DBNull.Value),
                                                    new SqlParameter("SerieId", (object)dto.SerieId ?? DBNull.Value)).ToList();

                foreach (NumerosDaSorte_ResultDTO row in procResult)
                {
                    result.Add(row.Numero);
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

E abaixo, o que retorna no EF:

Finalmente aqui, a procedure funcionando no banco:

Esse problema ocorre em algumas procedures, onde ou não consigo reconhecer o Complex Type de retorno, ou não consigo retornar os valores que retorno se rodar a procedure pelo banco.

Preciso da sua ajuda, para entender onde está o problema e o que posso fazer pra contornar essa situação... já estou há dias parado nisso, sem evoluir o projeto e meu prazo está acabando pra ficar tentando resolver isso, sem sucesso... já pesquisei de tudo e já fiz diversas mudanças, mas não posso mexer na lógica da procedure, até porque nem fui eu quem fez, logo, o que posso é fazer pequenos ajustes, mas não alterar a lógica da mesma profundamente.

Abraços.

---

Olivertech

Answer 3

Maclau,

Ok, vamos tentar ajudar, mas nem sempre conseguimos chegar na solução final!!!

O complex type que você se referi esta retornando qual conjunto de dados?

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 4

Olá amigo,

Então, no VS 2013, ele retorna um array de uma posição, com null, conforme imagem que enviei do VS.

Não retorna nenhum dado.

Qto ao Complex Type, o mesmo não foi criado automaticamente quando importei a procedure. Eu tive de criar manual, indo na edição da procedure, solicitando que ele mapeie o retorno e criando o Complex Type.

Qdo uso os recursos abaixo habilitado na procedure, consigo reconhecer o retorno e inicialmente está correto.

		IF (1=0) 
		BEGIN
			SET FMTONLY OFF;
		-	DECLARE @TabelaResultado TABLE (
				Numero VARCHAR(5) NOT NULL
			);
			SELECT Numero FROM @TabelaResultado WHERE (1=0)
		END

Aí o retorno que espero é um elemento string ou um array de strings. A procedure retorna numeros, mas é feito um cast nesses numeros para varchar, para que eles sejam concatenados com zeros a esquerda.

Bem. A procedure quando executada manualmente dentro do SQL Server, funciona 100%, mas se tento acessar a mesma pelo mapeamento, não consigo nada... sempre vem esse array de 1 posição com NULL.

Minha dúvida é se isso se deve a algum recurso usado na procedure em anexo, que possa estar causando esse problema, ou se isso é uma deficiência do EF.

Já fiz diversas pesquisas e sempre encontro as mesmas sugestões, de fazer como fiz acima, ou seja, criar uma especie de contract, forçando o EF a reconhecer o retorno ou mesmo usar o comando que hoje é considerado descontinuado (SET FMTONLY OFF).

Não encontro explicações mais detalhes por exemplo, explicado como devo proceder com o desenvolvimento das procedures, se espero mapea-las no EF, pois a princípio, o que se entende é que o EF6 não tem problemas com o mapeamento de procedures e que tudo é simples de usar, mas não me parece uma verdade, pois tenho tido enormes dificuldades, quando as procedures deixam de ser triviais, e passam a ser mais complexas, com uso de tables, condições, try catch etc.

É nessa linha que quero entender, se existem comando que não devem ser usados dentro das procedures, que causem impactos no mapping, ou se infelizmente estamos nos deparando com uma deficiência do EF6, para procedures mais complexas.

Abraços.

---

Olivertech

Answer 5

Maclau,

Você necessita realmente utilizar um array? Parece-me que o problema esta justamente na dificuldade do SQL Server 2012 identificar e manipular esta estrutura.

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]