CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresentity-framework-6complextype
Ben-Coden
Ben-Coden

Reputation: 126

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

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

Upvotes: 0

Views: 2542

Answers (2)

Milad
Milad

Reputation: 117

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.

Upvotes: 1

Hasan Mahmood
Hasan Mahmood

Reputation: 978

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]

Upvotes: 0

Related Questions