sql-server entity-framework-4 temp-tables

sql server - EF4-El procedimiento almacenado seleccionado no devuelve columnas



sql-server entity-framework-4 (13)

Tengo una consulta en un procedimiento almacenado que llama a algunos servidores vinculados con algún SQL dinámico. Entiendo que a EF no le gusta eso, así que enumeré específicamente todas las columnas que serían devueltas. Sin embargo, todavía no me gusta eso. ¿Qué estoy haciendo mal aquí? Solo quiero que EF pueda detectar las columnas devueltas del procedimiento almacenado para poder crear las clases que necesito.

Por favor, consulte el siguiente código que compone las últimas líneas de mi procedimiento almacenado:

SELECT #TempMain.ID, #TempMain.Class_Data, #TempMain.Web_Store_Class1, #TempMain.Web_Store_Class2, #TempMain.Web_Store_Status, #TempMain.Cur_1pc_Cat51_Price, #TempMain.Cur_1pc_Cat52_Price, #TempMain.Cur_1pc_Cat61_Price, #TempMain.Cur_1pc_Cat62_Price, #TempMain.Cur_1pc_Cat63_Price, #TempMain.Flat_Length, #TempMain.Flat_Width, #TempMain.Item_Height, #TempMain.Item_Weight, #TempMain.Um, #TempMain.Lead_Time_Code, #TempMain.Wp_Image_Nme, #TempMain.Wp_Mod_Dte, #TempMain.Catalog_Price_Chg_Dt, #TempMain.Description, #TempMain.Supersede_Ctl, #TempMain.Supersede_Pn, TempDesc.Cust_Desc, TempMfgr.Mfgr_Item_Nbr, TempMfgr.Mfgr_Name, TempMfgr.Vendor_ID FROM #TempMain LEFT JOIN TempDesc ON #TempMain.ID = TempDesc.ID LEFT JOIN TempMfgr ON #TempMain.ID = TempMfgr.ID

Además de lo que dijo @tmanthley, asegúrese de que su procedimiento almacenado realmente funciona al ejecutarlo primero en SSMS. Importé algunos procedimientos almacenados y olvidé un par de funciones escalares dependientes, lo que hizo que EF determinara que el procedimiento no arrojó columnas. Parece un error que debería haber detectado antes, pero EF no te da un mensaje de error en ese caso.

Ambas soluciones: 1- Definir manualmente el tipo complejo devuelto (supongo que debería funcionar) 2- Utilizar un truco y solo para agregar el procedimiento almacenado puesto al inicio SET FMTONLY OFF.

¡No trabajé conmigo en algún procedimiento sin embargo funcionó con otro!

mi procedimiento termina con esta línea:

SELECT machineId, production [AProduction] , (select production FROM #ShiftBFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [BProduction] , (select production FROM #ShiftCFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [CProduction] FROM #ShiftAFinalProd ORDER BY machineId

Gracias

Como algunos otros han notado, asegúrese de que el procedimiento realmente se ejecute. En particular, en mi caso, estaba ejecutando el procedimiento felizmente sin error en SQL Server Management Studio olvidando por completo que estaba conectado con derechos de administrador. Tan pronto como intenté ejecutar el procedimiento utilizando el usuario principal de mi aplicación, encontré que había una tabla en la consulta a la que ese usuario no tenía permiso de acceso.

EF no admite la importación de procedimientos almacenados que generan un conjunto de resultados desde:

  • Consultas dinámicas
  • Tablas temporales

La razón es que para importar el procedimiento EF debe ejecutarlo . Tal operación puede ser peligrosa porque puede provocar algunos cambios en la base de datos. Debido a eso EF usa un comando SQL especial antes de ejecutar el procedimiento almacenado:

SET FMTONLY ON

Al ejecutar este comando, el procedimiento almacenado devolverá solo "metadatos" sobre las columnas en su conjunto de resultados y no ejecutará su lógica. Pero debido a que la lógica no se ejecutó, no existe una tabla temporal (o consulta dinámica incorporada), por lo que los metadatos no contienen nada.

Tiene dos opciones (excepto la que requiere volver a escribir su procedimiento almacenado para no utilizar estas características):

  • Definir manualmente el tipo complejo devuelto (supongo que debería funcionar)
  • Use un truco y solo para agregar el procedimiento almacenado al inicio SET FMTONLY OFF . Esto permitirá que el resto del código de su SP se ejecute de manera normal. ¡Solo asegúrese de que su SP no modifique ningún dato porque estas modificaciones se ejecutarán durante la importación! Después de la importación exitosa, elimina ese truco.

En el marco de la Entidad, al obtener información de la columna, el sql ejecuta el procedimiento con la aprobación de valores nulos en el parámetro. Así que manejé el caso nulo de manera diferente al crear una tabla temporal con todas las columnas requeridas y devolver todas las columnas sin valor cuando se pasa null al procedimiento.

En mi procedimiento hubo consulta dinámica, algo así como

declare @category_id int set @category_id = (SELECT CATEGORY_ID FROM CORE_USER where USER_ID = @USER_ID) declare @tableName varchar(15) declare @sql VARCHAR(max) declare @USER_IDT varchar(100) declare @SESSION_IDT varchar(10) IF (@category_id = 3) set @tableName = ''STUD_STUDENT'' else if(@category_id = 4) set @tableName = ''STUD_GUARDIAN'' if isnull(@tableName,'''')<>'''' begin set @sql = ''SELECT [USER_ID], [FIRST_NAME], SCHOOL_NAME, SOCIETY_NAME, SCHOOL_ID, SESSION_ID, [START_DATE], [END_DATE] from @tableName .... EXECUTE (@sql) END ELSE BEGIN SELECT * from #UserPrfTemp END

No recibí la información de la columna en mi caso después de usar el truco establecido FMTONLY OFF.

Esta es la tabla temporal que creé para obtener los datos en blanco. Ahora estoy obteniendo la información de la columna

Create table #UserPrfTemp ( [USER_ID] bigint, [FIRST_NAME] nvarchar(60), SCHOOL_NAME nvarchar(60), SOCIETY_NAME nvarchar(200) ..... }

En mi caso, SET NOCOUNT ON; en la parte superior del procedimiento se corrigió el problema. Es una mejor práctica de todos modos.

En mi caso, SET FMTONLY OFF no funcionó. El método que seguí es, tomé una copia de seguridad del procedimiento almacenado original y lo reemplacé con solo el nombre de la columna, como la consulta siguiente.

Select Convert(max,'''') as Id,Convert(max,'''') as Name

Después de este cambio, cree una nueva función de importación, tipo complejo en el marco de la entidad. Una vez que se crea la función de importación y el tipo complejo, reemplace la consulta anterior con su procedimiento almacenado original.

Entity Framework intentará obtener las columnas ejecutando su procedimiento almacenado, pasando NULL para cada argumento.

  1. Asegúrese de que el procedimiento almacenado devuelva algo en todas las circunstancias. Tenga en cuenta que puede haber sido más inteligente para Entity Framework ejecutar el proceso almacenado con valores predeterminados para los argumentos, en oposición a NULLs.

  2. ER hace lo siguiente para obtener los metadatos de la tabla:

    SET FMTONLY ENCENDIDO

  3. Esto romperá su procedimiento almacenado en diversas circunstancias, en particular, si utiliza una tabla temporal.

  4. Entonces para obtener un resultado como tipo complejo; por favor intente agregando

    SET FMTONLY OFF;

Esto funcionó para mí, espero que también funcione para ti.

Referido de https://social.msdn.microsoft.com/Forums/en-US/e7f598a2-6827-4b27-a09d-aefe733b48e6/entity-model-add-function-import-stored-procedure-returns-no-columns?forum=adodotnetentityframework

Lo que agregaría es:

Que la importación también falla si los procedimientos almacenados tienen parámetros y no devuelve ningún resultado para los valores de parámetros predeterminados.

Mi procedimiento almacenado tenía 2 parámetros de flotación y no devolvería nada cuando ambos parámetros son 0.

Entonces, para agregar este procedimiento almacenado al modelo de entidad, establezco el valor de estos parámetros en el procedimiento almacenado para garantizar que devuelva algunas filas, sin importar cuáles sean los parámetros en realidad.

Luego, después de agregar este procedimiento almacenado al modelo de entidad, deshizo los cambios.

Nota al margen interesante: Tuve el mismo problema que primero resolví usando Variables de tabla, en lugar de Tablas temporales (solo para la importación). Eso no fue particularmente intuitivo para mí, y me echó de menos cuando observé mis dos SProcs: uno que usa tablas Temp y otro con Variables de Tabla.

(SET FMTONLY OFF nunca funcionó para mí, así que solo cambié mis SProcs temporalmente para obtener la información de la columna, en lugar de molestarme con el hack en el lado EF solo como un FYI.)

Mi mejor opción fue simplemente crear manualmente el tipo complejo y asignarle la función de importación. Funcionó muy bien, y la única diferencia terminó siendo que FactoryMethod adicional para crear las propiedades se incluyó en el Diseñador.

O puede crear un tipo de tabla definido por el usuario y devolverlo.

CREATE TYPE T1 AS TABLE ( ID bigint NOT NULL ,Field1 varchar(max) COLLATE Latin1_General_CI_AI NOT NULL ,Field2 bit NOT NULL ,Field3 varchar(500) NOT NULL ); GO

Luego en el procedimiento:

DECLARE @tempTable dbo.T1 INSERT @tempTable (ID, Field1, Field2, Field3) SELECT ..... .... SELECT * FROM @tempTable

Ahora EF debería ser capaz de reconocer el tipo de columnas devueltas.

SET FMTONLY OFF

funcionó para mí para uno de los procedimientos pero falló para otro procedimiento. Los siguientes pasos me ayudan a resolver mi problema

  1. Dentro de un procedimiento almacenado, he creado una tabla temporal con el mismo tipo de columna e inserté todos los datos devueltos por la consulta dinámica a la tabla temporal. y seleccionó los datos de la tabla temporal.

    Create table #temp ( -- columns with same types as dynamic query ) EXEC sp_executeSQL @sql insert into #temp Select * from #temp drop table #temp

  2. Se eliminó el tipo complejo existente, la función de importación y la instancia de procedimiento almacenado para el viejo procedimiento almacenado y el modelo de entidad actualizado para el nuevo procedimiento actual.

  3. Edite la Función importada en la entidad modal para el tipo complejo deseado, obtendrá toda la información de columna allí que no obtiene para el procedimiento almacenado anterior.

  4. Una vez que haya terminado con la creación del tipo, puede eliminar la tabla temporal del procedimiento almacenado y luego actualizar Entity Framework.