No Complex Type created from new stored procedure, EF database first

Question

I can not figure out why EF6 will not create a complex type for my stored procedure. I am using the database-first approach.

I created a new stored procedure in SSMS 2017. Open my EDMX, right clicked "Update Model From Database," check my stored procedure and finish.

In my Model Browser, I see my stored procedure, but no complex type was created. Then I found this post and tried creating a complex type from "Edit Function Import", but when I click 'Get Column Information' it tells me "The Selected stored procedure or function returns no columns."

When I run my stored procedure in SSMS, it returns the data I want.

Here is my stored procedure

SELECT DISTINCT
    r.ROId, ru.VIN, ru.Year, ru.Make, ru.Model, ru.ModelNo, ru.PlateNo, ru.Note 
INTO
    #ROUnits
FROM  
    tbl_ROUnits ru
INNER JOIN 
    tbl_RepairOrder r ON ru.ROUnitId = r.UnitId
INNER JOIN 
    tbl_ServiceItems s ON r.roid = s.roId
INNER JOIN 
    tbl_TechServiceItems tsi ON tsi.ServiceItemId = s.ItemId
WHERE 
    tsi.EmployeeId = @EmployeeID 
    AND s.IsActive = 1 
    AND tsi.IsCompleted = 0

SELECT DISTINCT
    r.ROId, c.CustomerId, c.CompanyId, c.CompanyName, 
    c.FirstName, c.MiddleName, c.LastName, c.Suffix, 
    c.Phone1, c.Phone2, c.Email
INTO 
    #Customer
FROM
    tbl_Customer c
JOIN
    tbl_RepairOrder r ON r.CustomerId = c.CustomerId
JOIN
    tbl_ServiceItems si ON si.ROId = r.roid
JOIN
    tbl_TechServiceItems tsi ON tsi.ServiceItemId = si.ItemId
WHERE 
    tsi.EmployeeId = @EmployeeID 
    AND r.IsActive = 1 
    AND c.IsActive = 1 
    AND tsi.IsCompleted = 0

SELECT DISTINCT
    r.ROId, r.RONo,
    s.ItemId, s.Repair, s.Problem, s.Solution, 
    s.CalculatedHrs, s.ActualStartDateTime, s.IsCompleted, s.CompletedDate,
    s.ActualEndDateTime, s.StatusId, s.RepairNo,
    rs.ROStatusId, rs.Name, rs.IsActive, rs.Description
INTO
    #ServiceItem
FROM
    tbl_ServiceItems s
INNER JOIN 
    tbl_RepairOrder r ON s.ROId = r.ROId
INNER JOIN 
    tbl_TechServiceItems tsi ON tsi.ServiceItemId = s.ItemId
LEFT JOIN 
    tbl_ROStatus rs ON rs.ROStatusId = s.StatusId
WHERE 
    tsi.EmployeeId = @EmployeeID
    AND s.IsActive = 1  
    AND tsi.IsActive = 1 
    AND tsi.IsCompleted = 0 
    AND s.IsCompleted = 0 
    AND r.ROStatus  <> 'V'

SELECT DISTINCT
    c.CustomerId, c.CompanyId, c.CompanyName,
    c.FirstName, c.MiddleName, c.LastName, c.Suffix, 
    c.Phone1, c.Phone2, c.Email,
    si.ROId, si.RONo, si.ItemId, si.Repair, si.Problem, 
    si.Solution, si.CalculatedHrs, si.ActualStartDateTime, si.IsCompleted,
    si.CompletedDate, si.ActualEndDateTime, si.StatusId, si.RepairNo,
    si.ROStatusId, si.Name, si.IsActive, si.Description 
INTO
    #CustomerService 
FROM
    #ServiceItem si  
LEFT JOIN
    #Customer c ON si.ROId = c.ROId

SELECT 
    ROStatusId, name, Description 
INTO
    #RoStatus
FROM
    tbl_ROStatus

SELECT
    cs.ROId, cs.RepairNo, cs.RONo,
    (cs.FirstName + ' ' + cs.LastName) AS CustomerName,
    cs.Name, cs.Repair,
    ru.Make, ru.Model, cs.ItemId, cs.IsCompleted, ros.Name AS 'Status'
FROM
    #CustomerService cs 
LEFT JOIN
    #ROUnits ru ON ru.ROId = cs.ROId
LEFT JOIN
    #RoStatus ros ON ros.ROStatusId =cs.StatusId
ORDER BY 
    RONo

DROP TABLE #ROUnits, #Customer, #ServiceItem, #CustomerService, #RoStatus

set all your parameters as NULL or 0 or some default value, try to run your stored procedure with only name `EXEC YourSPName` and see if you can see your column structure? if not fix that first and it will be fixed. — Hasan Mahmood, Mar 21 '19 at 19:21
EF can't discover the output columns because of the SELECTs to temp tables. Just build a return entity type yourself in the EDMX designer. — Gert Arnold, Mar 21 '19 at 21:38

Milad · Answer 1 · 2019-03-21T21:04:37.520

1

Use SET FMTONLY ON before your queries start and refresh entity model again.
In Complex queries with many selects in store procedures this problem happen for me too when working with entity framework.

edited Mar 21 '19 at 21:04

answered Mar 21 '19 at 20:59

Milad

117
1
8

score 0 · Answer 2 · answered Mar 21 '19 at 19:24

0

set all your parameters as NULL or 0 or some default value, try to run your stored procedure with only name EXEC YourSPName and see if you can see your column structure? if not fix that first and it will be fixed

SAMPLE CODE:

CREATE PROCEDURE [dbo].[CustOrdersDetail] 
@OrderID INT = 0
AS
SELECT * FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
GO

EXEC [dbo].[CustOrdersDetail]

answered Mar 21 '19 at 19:24

Hasan Mahmood

978
7
10

1

OK, I made that change, but still nothing. I can execute my sproc with no parameters and in SSMS it gives me the column headers, but in the 'Edit Function Import' I am still getting 'The Selected stored procedure or function returns no columns.' I even tried deleting the sproc from the EDMX and reading it but still no complex type. – Ben-Coden Mar 21 '19 at 20:09

No Complex Type created from new stored procedure, EF database first

2 Answers2