Why won't this LINQ join statement work?

Question

I have this LINQ-query:

    // types...
    LinkedList<WeightedItem> itemScores = new LinkedList<WeightedItem>();

    var result = from i in _ctx.Items
                 join s in itemScores on i.Id equals s._id
                 orderby s._score descending
                 select new ItemSearchResult(i, s._score);

    // this fails:
    return result.ToList();

Which is generating this error:

Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

[EDIT] Here's the code of WeightedItem:

public class WeightedItem
{
    public int _id;
    public decimal? _score;

    public WeightedItem(int id, decimal? score)
    {
        _id = id;
        _score = score;
    }
}

Can you see what I've done wrong? The code compiles perfectly and both the _ctx.Items and itemScores contains proper values.

Answer 1

Yes, it would compile fine - the problem is that it can't translate it into SQL. When you reference "local" values, the entity framework has to work out what to do with them when it needs to create a SQL query. It basically can't cope with doing a join between an in-memory collection and a database table.

One thing which might work would be to use Contains instead. I don't know whether LinkedList<T> will work for this, but I believe List<T> does, at least in LINQ to SQL:

List<int> requiredScoreIds = itemScores.Select(x => x._id).ToList();

var tmp = (from i in _ctx.Items
           where requiredScoreIds.Contains(i.Id)
           orderby s._score descending
           select i).AsEnumerable();

// Now do the join in memory to get the score
var result = from i in tmp
             join s in itemScores on i.Id equals s._id
             select new ItemSearchResult(i, s._score);

Now that's doing a join in the in-memory query, which is somewhat unnecessary. You could instead use a dictionary:

List<int> requiredScoreIds = itemScores.Select(x => x._id).ToList();

var tmp = (from i in _ctx.Items
           where requiredScoreIds.Contains(i.Id)
           orderby s._score descending
           select i).AsEnumerable();

// Create a map from score ID to actual score
Dictionary<int, decimal?> map = itemScores.ToDictionary(x => x._id,
                                                        x => x._score);

var result = tmp.Select(i => new ItemSearchResult(i, map[i.Id]));

Answer 2

You can't join between an in memory list and a queriable object. You need to do something like this:

var criteria = itemScores.Select(x => x._id).ToList();
var result_tag = (from i in _ctx.Items
                 where criteria.Contains(i.ID)
                 select i).ToList();
var result = from i in result_tag
             join s in itemScores on i.ID equals s._id
             orderby s._score descending
             select new ItemSearchResult(i, s._score);