Why is this LINQ join statement failing to execute?

c# entity-framework join linq linq-to-entities

Question

This is my 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();

Who or what is causing 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] Below is the code forWeightedItem :

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

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

Can you identify my mistakes? Both the _ctx and the code are fully compiled. Items include appropriate values, as do itemScores.

1
10
11/12/2012 8:13:27 PM

Accepted Answer

Yes, it would successfully compile; the issue is that it cannot convert it to SQL. The entity framework must determine what to do with "local" data when it has to construct a SQL query when you reference them. In essence, it is incapable of handling a join operation between an in-memory collection and a database table.

One strategy that might work is to employContains instead. I'm not sure ifLinkedList<T> will be effective for this, although I thinkList<T> @ least in LINQ to SQL, does

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);

That involves performing an unneeded join in the in-memory query. 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]));
21
12/30/2009 7:55:41 PM

Popular Answer

An in-memory list and a queryable object cannot be joined. You must do the following action:

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);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow