asked on

Linq : unable to cast object

I'm getting this error:

Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[VB$AnonymousType_1`2[System.Guid,System.String]]' to type 'System.Collections.Generic.IEnumerable`1[Oop.Entities.aspnet_Roles]'.

From this query:
Public Function GetAvailableRoles(ByVal menuId2 As Integer) As IEnumerable(Of aspnet_Roles)
Try
Dim query As IEnumerable(Of aspnet_Roles) = Nothing
query = From role In oEntities.aspnet_Roles, menu In oEntities.sssMenuRole _
Select RoleId = role.RoleId, RoleName = role.RoleName, _
RoleIDMnu = menu.RoleId, MenuIDMnu = menu.menuId _
Where MenuIDMnu = menuId2 And RoleId = RoleIDMnu _
Select RoleId, RoleName

I've also tried as a datatable, with this:
As DataTable
Dim availableRoles = From role In oEntities.aspnet_Roles, menu In oEntities.sssMenuRole _
Select RoleId = role.RoleId, RoleName = role.RoleName, _
RoleIDMnu = menu.RoleId, MenuIDMnu = menu.menuId _
Where menuId = menuId And RoleId = RoleIDMnu _
Select RoleId, RoleName

with this error.
Unable to cast object of type 'System.Data.Linq.DataQuery`1[VB$AnonymousType_1`2[System.Guid,System.String]]' to type 'System.Collections.Generic.IEnumerable`1[Linq.sssMenu]'.

I will attach the queries to create the tables. the roles table is the one created by microsoft automatically when you create a set of membership tables.

USE [Oop4]
GO
 
/****** Object:  Table [dbo].[aspnet_Roles]    Script Date: 02/26/2009 10:23:22 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[aspnet_Roles](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL,
	[LoweredRoleName] [nvarchar](256) NOT NULL,
	[Description] [nvarchar](256) NULL,
 CONSTRAINT [PK__aspnet_Roles__31EC6D26] PRIMARY KEY NONCLUSTERED 
(
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[aspnet_Roles]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Ro__Appli__32E0915F] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
 
ALTER TABLE [dbo].[aspnet_Roles] CHECK CONSTRAINT [FK__aspnet_Ro__Appli__32E0915F]
GO
 
ALTER TABLE [dbo].[aspnet_Roles] ADD  CONSTRAINT [DF__aspnet_Ro__RoleI__33D4B598]  DEFAULT (newid()) FOR [RoleId]
GO
 
 
USE [Oop4]
GO
 
/****** Object:  Table [dbo].[sssMenuRole]    Script Date: 02/26/2009 10:27:01 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[sssMenuRole](
	[menuId] [int] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_sssMenuRole] PRIMARY KEY CLUSTERED 
(
	[menuId] ASC,
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Open in new window

SOLUTION

Fernando Soto

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Starr Duskk

ASKER

It errors:
"role is not declared or is not in the current scope"

Fernando Soto

Post the code as you implemented it with all variables definitions that are used as well.

PockyMaster

Your code returns a query and is not enumerated yet. Return a query.ToArray() or query.ToList()

I must admit never having done Linq in VB.NET, only in C#,

but can't you do something similar to:
return oEntities.aspnet_Roles.Where(r => r.sssMenuRole.menuID == menuId2).ToArray();
(don't know VB.NET equivalent)
I also assume that your linq model has relationships between asp_role and menu table (based on the role_id)

SOLUTION

MDKIMZEY

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Starr Duskk

ASKER

I tried that:
Dim query As New List(Of aspnet_Roles)
query = (From role In oEntities.aspnet_Roles, menu In oEntities.sssMenuRole _
Select RoleId = role.RoleId, RoleName = role.RoleName, _
RoleIDMnu = menu.RoleId, MenuIDMnu = menu.menuId _
Where MenuIDMnu = menuId2 And RoleId = RoleIDMnu _
Select role).ToList()
the "select role" errors with same error, 'either not declared or not in current scope.'
If I do it this way:
query = (From role In oEntities.aspnet_Roles, menu In oEntities.sssMenuRole _
Select RoleId = role.RoleId, RoleName = role.RoleName, _
RoleIDMnu = menu.RoleId, MenuIDMnu = menu.menuId _
Where MenuIDMnu = menuId2 And RoleId = RoleIDMnu _
Select RoleId, RoleName).ToList()
with this change:
Select RoleId, RoleName
it errors with generic list of anonymous type cannot be converted to generic list of aspnet_Roles.

sigh.

PockyMaster

what's the first select used for anyway?

    query = (From role In oEntities.aspnet_Roles, menu In oEntities.sssMenuRole _
        Where MenuIDMnu = menuId2 And RoleId = RoleIDMnu _
                                  Select role).ToList()

Open in new window

Starr Duskk

ASKER

Here is the query built-by-hand:

Dim sql As New StringBuilder()
sql.AppendLine("SELECT r.RoleId, r.RoleName ")
sql.AppendLine("FROM dbo.aspnet_Roles r ")
sql.AppendLine("WHERE r.RoleId IN ")
sql.AppendLine(" (select roleID from dbo.sssMenuRole where menuId = @MenuId) ")
sql.AppendLine("Order By r.RoleName")

PockyMaster

I'm getting confused, you're selecting roleID + roleName, but expecting to get out a complete role.
select the role as a complete object using linq, or create a new class that contains only roleID, roleName and return that if you like.
but selecting just 2 properties will not working, unless you explicitly create a new object and set these properties yourself, but I don't see the sense of doing that.

Starr Duskk

ASKER

If you refer to the previous posts, you'll see I've tried to select all the fields...
>> Select role).ToList()
>>the "select role" errors with same error, 'either not declared or not in current scope.'
But it errors.
I don't care how I do it, as long as I can get it to work.
Can you tell me how to reproduce that query in linq? with selecting *

PockyMaster

return oEntities.aspnet_Roles.ToList()

that's all

Starr Duskk

ASKER

return? can you spell it out for me please?
query = (From role In oEntities.aspnet_Roles, menu In oEntities.sssMenuRole _
Select RoleId = role.RoleId, RoleName = role.RoleName, _
RoleIDMnu = menu.RoleId, MenuIDMnu = menu.menuId _
Where MenuIDMnu = menuId2 And RoleId = RoleIDMnu _
Select role).ToList()

ASKER CERTIFIED SOLUTION

PockyMaster

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Starr Duskk

ASKER

It looks like it's working.
Thanks!
I have another question using this same query, if you can look at it:
http://www.experts-exchange.com/Programming/Languages/.NET/LINQ/Q_24204214.html
thanks!

Starr Duskk

ASKER

thanks!