Linq Query that returns IQueryable

Question

I have a method called

     public static IQueryable GetUsers()

uses Linq query.

{SELECT [t0].[ApplicationUserId], ([t0].[LastName] + @p0) + [t0].[FirstName] AS [UserName], [t1].[SecurityRoleName], [t2].[UserStatus]
    FROM [dbo].[ApplicationUsers] AS [t0]
    INNER JOIN [dbo].[SecurityRoles] AS [t1] ON [t0].[SecurityRoleId] = [t1].[SecurityRoleID]
    INNER JOIN [dbo].[UserStatusLookups] AS [t2] ON [t0].[UserStatusId] = [t2].[UserStatusLookupID]
    ORDER BY [t0].[LastName]
}

that returns all the users. I cannot the change this method or class.

I need a user belonging to a specific security role. From just IQueryable would I be able to get a specific user. so that [t1].[SecurityRoleID] is replaced with 15.

Answer 1

This should do it:

var Role15Users = GetUsers().Where(t => t.SecurityRoldID == 15);

Answer 2

You can use a where on your full list

var users = MyClass.GetUsers();
var userInRole = users.Where(u=>u.SecurityRoleId == 15);

Answer 3

It is also quite possible that there's no SecurityRoleId property in the class, rather an object relation. In this case you would have to write something like

var roleUsers = dataSource.GetUsers().Where(
    u => u.SecurityRole.Id == 15
    );

It would return all users with role #15.
However you sure must know that magic number are bad.

If you want to find a single user with role called, for instance, Admin, make sure SecurityRole is correctly mapped (depends on your class structure which you haven't posted) and do something like

var admin = (from user in dataSource.GetUsers()
            where user.SecurityRole.Name == "Administrator"
            select user).Single(); // will throw exception if none or multiple are found


var admin = (from user in dataSource.GetUsers()
            where user.SecurityRole.Name == "Administrator"
            select user).SingleOrDefault(); // will return null if none, throw exception if multiple are found

You can also rewrite these queries in method call fashion:

var admin = dataSource.GetUsers()
    .Where(u => u.SecurityRole.Name == "Administrator")
    .Single(); // will throw exception if none or multiple are found

var admin = dataSource.GetUsers()
    .Where(u => u.SecurityRole.Name == "Administrator")
    .SingleOrDefault(); // will return null if none, throw exception if multiple are found

Answer 4

You can't use Where because it's only defined on IQueryable<T>, the strongly-typed version. You need to cast your IQueryable from GetUsers() to an appropriate IQueryable<T>:

var users = GetUsers().Cast<ModelType>();

ModelType should be whatever type the model objects from GetUsers are; it should have SecurityRoleID, ApplicationUserId, or whatever properties you need.

Then you can use the LINQ extension methods:

var user = users.Where(u => u.SecurityRoleID == 15);

or maybe:

var user = users.FirstOrDefault(u => u.SecurityRoleID == 15);

Answer 5

Do you have

using System.Linq

in your includes?

Where<TSource> Method (IQueryable<TSource>, Expression<Func<TSource, Boolean>>)

is an extension method to the IQueryable type found in the System.Linq namespace.