为什么这个 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.IEnumerable`1"的常量值.
在此上下文中仅支持原始类型(例如 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.

可能 的一件事是使用 Contains 代替.我不知道 LinkedList 是否适用于此，但我相信 List 至少在 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屋！