In ASP.NET MVC 3, how do I use LINQ to retrieve only the entity objects I want?

Question

I have a Controller that currently looks like this:

public ActionResult Index()
{
    var onlineUsers = Membership.GetAllUsers().Cast<MembershipUser>().Where(u => u.IsOnline);
    var onlinePlayers = from p in _db.Players
                        join u in onlineUsers on p.userId equals (Guid)u.ProviderUserKey
                        select p;

    return View(onlinePlayers);
}

but when I try to run this, my View throws an exception at:

@using BuySell.Models;
@model IEnumerable<BuySell.Models.Player>
@{
    ViewBag.Title = "Index";
}
...
@foreach (var item in Model) { // EXCEPTION HAPPENS HERE
    ...

with the error:

Unable to create a constant value of type 'System.Web.Security.MembershipUser'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

What's going on? All I want to do is enumerate through every "Player" entity that corresponds to a user who is currently logged on.

Answer 1

You cannot mix providers in that fashion. While the C# compiler doesn't know that it's invalid, the runtime has no idea how to (and, indeed, can't) translate something that mixes LINQ-to-SQL entities and in-memory objects into a SQL query. The only way to make that actually work would be to use _db.Players.AsEnumerable(), but that would cause the entire Player table to be brought back and filtered in memory, which is bad.

Instead, you'll have to do something like this:

var onlineKeys = Membership.GetAllusers().Cast<MembershipUser>()
                .Where(u => u.IsOnline)
                .Select(u => (Guid)u.ProviderUserKey)
                .ToList();

var onlinePlayers = from p in _db.Players
                    where onlineKeys.Contains(p.userId)

This will flip the Contains around and do something like where userId in (...).