CODEWITHSUNDEEP
sql-servert-sql
Saulius
Saulius

Reputation: 2006

Linq to sql returns "Subquery returned more than 1 value" though it should not

I am retrieving data with linq from sproc and get an exception "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.". Weird thing is that it runs OK from SQL server Management studio. The sproc is this:

BEGIN
DECLARE @LoginRights Table(RowNo int, TabID int, MenuID int, ControlID int)
INSERT INTO @LoginRights SELECT row_number() Over (order by ControlID), TabID, MenuID, ControlID FROM dbo.func_Action_LoginRoles(@LoginID) --WHERE TabID=@TabID AND ControlID is not null
DECLARE @RightsCount int=@@RowCount, @iRow int =1,@ControlID int,@MenuID int;

DECLARE @Menu Table(MenuID int)
INSERT INTO @Menu(MenuID)
SELECT MenuID FROM (
SELECT m.ID MenuID, t.ID TabID FROM dbo.tblAction_Menu m JOIN dbo.tblAction_MenuGroup mg on m.GroupID=mg.ID JOIN tblAction_Tabs t on t.ID=mg.TabID
WHERE t.ID IN(SELECT TabID FROM @LoginRights WHERE MenuID is null) or
m.ID IN(SELECT MenuID FROM @LoginRights WHERE MenuID is not null)
) m Group by m.MenuID
DECLARE @ControlsInMenu Table(MenuID int, ControlID int)
WHILE @iRow<=@RightsCount BEGIN
 SELECT @ControlID=ControlID, @MenuID=MenuID FROM @LoginRights WHERE RowNo=@iRow
 IF @MenuID is null BEGIN
  INSERT INTO @ControlsInMenu(MenuID, ControlID)
  SELECT MenuID, @ControlID FROM @Menu
 END ELSE BEGIN
  INSERT INTO @ControlsInMenu(MenuID, ControlID)
  SELECT @MenuID, @ControlID
 END
SET @iRow=@iRow+1 END
SELECT MenuID, ControlID FROM @ControlsInMenu
END

The function:
ALTER FUNCTION [dbo].[func_Action_LoginRoles]
(@LoginID int)
RETURNS @LoginsRoles TABLE(ID int, Name nvarchar(50), TabID int, MenuID int, ControlID int)
AS
BEGIN
INSERT INTO @LoginsRoles (ID, Name, TabID, MenuID, ControlID)
SELECT lr.ID, lr.Name, TabID, MenuID, ControlID FROM tblLogins_Roles lr
    JOIN tblLogins_RolesInGroup rig ON lr.ID=rig.RolesID
    JOIN tblLogins_Roles_Groups lrg ON lrg.ID=rig.Roles_GroupID
    JOIN tblLogins l ON l.Roles_GroupID=lrg.ID WHERE l.ID=@LoginID
RETURN
END

Data retrieval method is this (C# / LINQ-to-SQL):

var ControlsInMenu = from c in dc.proc_Action_ControlsInMenu(LoginData.LoginID, TabID)
                     select new
                     {
                        c.MenuID,
                        c.ControlID
                     };

Upvotes: 1

Views: 979

Answers (3)

Saulius
Saulius

Reputation: 2006

I have found out. Error "Subquery returned more than 1 value.." occurred not in the procedure where it been shown in the db.designer. It actually occurred in previous data consumption method. Famous KISS principle in action :-)

Upvotes: 0

DForck42
DForck42

Reputation: 20357

check to see if you get any duplicates from dbo.func_Action_LoginRoles(@LoginID)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Ah. Your query is returning multiple result sets (one per time through the loop), and LINQ to SQL is presumably unable to process this.

If you want LINQ to SQL to process this, I'd suggest you swap the following two lines:

SELECT MenuID, ControlID FROM @ControlsInMenu
END

Upvotes: 1

Related Questions