为什么LINQ join语句不会工作？ [英] Why won't this LINQ join statement work?

问题描述

我有这个LINQ查询：

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

哪个生成此错误：

无法创建类型为System.Collections.Generic.IEnumerable1的常量值。

只有原始类型（如Int32，String和Guid）是

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.

以下是 WeightedItem ：

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

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

你能看到我做错了什么吗？代码完美编译，_ctx.Items和itemScores都包含正确的值。

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

推荐答案

是的，它会编译好 - 问题是不能将其翻译成SQL。当引用本地值时，实体框架必须解决在需要创建SQL查询时要做的事情。它基本上不能应付在内存集合和数据库表之间的连接。

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.

可能可能会有一件事情将使用替换包含。我不知道 LinkedList< T> 是否适用于此，但我相信 List< T> 至少在LINQ to SQL中：

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

这篇关于为什么LINQ join语句不会工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！