Home Linq to sql returns "Subquery returned more than 1 value" though it should not
Reply: 3

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

Saulius
1#
Saulius Published in 2010-11-08 06:53:21Z

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
                     };
Damien_The_Unbeliever
2#
Damien_The_Unbeliever Reply to 2010-11-08 09:02:23Z

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
DForck42
3#
DForck42 Reply to 2010-11-08 16:06:51Z

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

Saulius
4#
Saulius Reply to 2010-11-09 06:12:20Z

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 :-)

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.344799 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO