EF can’t infer return schema from Stored Procedure selecting from a #temp table

by 
CREATE PROCEDURE [MySPROC]
AS 
BEGIN

--supplying a data contract
IF 1 = 2 BEGIN
    SELECT
        cast(null as bigint)  as MyPrimaryKey,
        cast(null as int)    as OtherColumn
    WHERE
        1 = 2  
END

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) 
    SELECT SomePrimaryKey, SomeColumn 
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn 
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

Supplying a faux data contract for the result set is the easiest, cleanest and fastest way to take care of the issue. This same problem exists in data source controls in SSIS too. .NET will read the result set from the unreachable “contract” section of the query and supply the metadata for the complex type. No performance impact and no need to comment out the SQL that does the actual work.

More Related Contents:

  1. Entity Framework: One Database, Multiple DbContexts. Is this a bad idea? [closed]
  2. No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’
  3. Entity Framework and SQL Server View
  4. How to mock the limitations of EntityFramework’s implementation of IQueryable
  5. EntityFramework code-first custom connection string and migrations
  6. Debug code-first Entity Framework migration codes
  7. Unique keys in Entity Framework 4
  8. Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths
  9. Entity Framework: Re-finding objects recently added to context
  10. ObjectContext.Refresh()?
  11. How do I map a char property using the Entity Framework 4.1 “code only” fluent API?
  12. combination of two field must be unique in Entity Framework code first approach. how it would?
  13. EntityFramework Core automatic migrations
  14. How to create index in Entity Framework 6.2 with fluent configuration
  15. Using Entity Framework with an SQL Compact Private Installation
  16. Mocking or faking DbEntityEntry or creating a new DbEntityEntry
  17. Map two different entities to the same table?
  18. EF Code First foreign key without navigation property
  19. Nullable property to entity field, Entity Framework through Code First
  20. LINQ to Entities for subtracting 2 dates
  21. Entity Framework Code First Using Guid as Identity with another Identity Column
  22. AutomaticMigrationsEnabled false or true?
  23. ADO.NET Entity Framework tutorials [closed]
  24. Entity Framework Code First Many to Many Setup For Existing Tables
  25. Compose queries across Entity Data Models
  26. What is the best way to compare two entity framework entities?
  27. Changing Inherited Types in Entity Framework
  28. EF5 How to get list of navigation properties for a domain object
  29. Why is my Entity Framework Code First proxy collection null and why can’t I set it?
  30. Why does the entity framework need an ICollection for lazy loading?

Leave a Comment