13
How does MSLinqToSqlGenerator determine the return type of a stored procedure?
source link: https://www.codesd.com/item/how-does-mslinqtosqlgenerator-determine-the-return-type-of-a-stored-procedure.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
How does MSLinqToSqlGenerator determine the return type of a stored procedure?
advertisements
I have a stored procudre (SP) as follows:
CREATE PROCEDURE [dbo].[UDSPSelectMissing]
-- paramters omitted
AS
BEGIN
-- Code Omitted
SELECT
c.MemberID,
c.FirstName,
c.MiddleName,
c.LastName,
c.Suffix,
c.PhoneHome,
c.PhoneCell,
c.Email
FROM [dbo].[Members] c
WHERE c.MemberID IN (SELECT MemberID FROM #PeopleCameMissing)
ORDER BY c.LastName,c.FirstName
END
When I added the SP to the SP pane of "dbml" designer, I was expecting MSLinqToSqlGenerator to create "ISingleResult" as return value type. To my surprise, the return value type is "int". Clearly, the generator is using certain criteria to determine the return value type. I have other similar SPs that returns "ISingleResult" type. Does anybody know what would cause the generator to behave this way? By the way, somebody on this site suggested recreating the SP, removing the reference from "dbml", restarting Visual Studio (VS) and adding to it to "dbml" again. I went through all that to no avail. My suspicion lies with the temporary table "#PeopleCameMissing". Any comments or suggestions are most welcome! Thanks, Cullen
I mentioned in my original post that I suspected the temp table. The temp table was created by "SELECT INTO #PeopleCameMissing" command therefore its definition is dynamic so when the stored procedure is added to "dbml" designer, the generator has no clue about the table's definition. What if I let the table definition known to the generator, maybe I'd be able to get the desired return type. Based on this conjecture, I ran a test. I declared a table as follows:
DECLARE @PeopleCameMissing TABLE(
MemberID int,
FirstName nchar(20),
MiddleName nchar(20),
LastName nchar(20),
Suffix nchar(10),
PhoneHome nchar(20),
PhoneCell nchar(20),
Email nchar(80)
);
And changed the original statement as follows:
INSERT INTO @PeopleCameMissing (
MemberID,
FirstName,
MiddleName,
LastName,
Suffix,
PhoneHome,
PhoneCell,
Email)
SELECT
c.MemberID,
c.FirstName,
c.MiddleName,
c.LastName,
c.Suffix,
c.PhoneHome,
c.PhoneCell,
c.Email
FROM [dbo].[Members] c
WHERE c.MemberID IN (SELECT a.MemberID FROM #PeopleCameMissing a)
ORDER BY c.LastName,c.FirstName;
DROP TABLE #PeopleCameMissing;
SELECT *
FROM @PeopleCameMissing
ORDER BY LastName,FirstName;
And then I added the stored procedure to "dbml" designer again. This time the generator created the desired return type: "ISingleResult". It seems to prove my conjecture.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK