Sin Sin - 2 months ago 14
Vb.net Question

The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities. MVC User Profile Advice

My scenario is that I want to list all the user logins and their roles on a sort of admin page, where only admin users will be able to access

In my UserProfile model, there is no field for Role as I am using the SimpleMembershipProvider, so I've chosen to tackle this with a ViewModel that looks as follows:

Imports WebMatrix.WebData

Public Class vmUserProfile

Public Property UserId As Integer
Public Property Username As String
Public Property Role As String


End Class


Thereafter, in my controller where I am creating my list of this ViewModel, the code looks as follows:

Public Function GetListOfUsers() As ActionResult
Dim users = From e In db.UserProfile
Select New vmUserProfile With {.UserId = e.UserId, _
.Username = e.UserName, _
.Role = Roles.GetRolesForUser(e.UserName)(0) _
}

Return PartialView("Partial/_UserList", users.ToList.OrderBy(Function(s) s.Username))
End Function


The part that I'm having an issue with is
.Role = Roles.GetRolesForUser(e.UserName)(0)
which I'm quite aware LINQ to Entities is not happy with because of the array.

I've looked at other examples and seen that others have solved this problem by putting the relevant array index into a separate variable but for the life of me I cannot see how to apply that to my current dilemma.

Ben Ben
Answer

I'm pretty rubbish with VB, and am used to C#. I also tend to use the lambda syntax for linq rather than the query syntax.

But I'll give it a go!

I've seen this error my self a few times, and I'm pretty sure it crops up when you call a method within a linq to entities statement that it can't translate to SQL. In your case Roles.GetRolesForUser(e.UserName)(0) can't be translated to SQL.

I'm afraid that I can't write VB or linq in query syntax, but here is my solution in C# and lambda syntax. This is what you have now:

var users = 
    db.UserProfile
        .Select(e => new vmUserProfile()
        {
            UserId = e.UserID,
            Username = e.Username,
            Role = Roles.GetRolesForUser(e.UserName)(0)
        })

If you change it to this it should work:

var users = 
    db.UserProfile
        .Select(e => mew
        {
            e.UserID,
            e.Username
        })
        .ToList()
        .Select(e => new vmUserProfile()
        {
            UserId = e.UserID,
            Username = e.Username,
            Role = Roles.GetRolesForUser(e.UserName)(0)
        })

The key difference here is that I am pulling a list of anonymous objects containing the UserID and Username into memory by calling ToList() before selecting the values into a new vmUserProfile and calling `Roles.GetRolesForUser(e.UserName)(0)

EDIT

Thought I'd have a stab at a VB version!

Dim users = 
    From u In (
        From e In db.UserProfile 
        Select New With 
        { 
            e.UserId, 
            e.UserName 
        }
    ).ToList
    Select New vmUserProfile With 
    { 
        .UserId = e.UserId, 
        .Username = e.UserName, 
        .Role = Roles.GetRolesForUser(e.UserName)(0) 
    }
Comments