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

complextype entity-framework-6 sql-server stored-procedures tsql

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
1
0
3/21/2019 8:20:13 PM

Popular Answer

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.

0
3/21/2019 9:04:37 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow