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

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

user2385
1#
user2385 Published in June 23, 2018, 7:54 am

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
                     };
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO