“无法创建类型为..的常量值。仅支持基本类型。”在EF查询中？ [英] "Unable to create a constant value of type .. Only primitive types are supported ..'' in EF query?

问题描述

我已经解决了这个问题，但是如果有人可以解释为什么会发生这种情况，以及如何为无法解决我的大数据集设计该方法，我将不胜感激。

I have a work around for this issue, however I would appreciate it if someone could explain why this is happening and how I would design this for large datasets where my work around would not be viable.

完整错误为：
无法创建类型为'THPT_Razor.Models.WinType'的常量值。在这种情况下，仅支持基本类型（例如Int32，String和Guid）。

The full error is: Unable to create a constant value of type 'THPT_Razor.Models.WinType'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

，而我正在使用EF v4.0。

and I am using EF v4.0.

注释行是有问题的代码，变通方法是 For循环

The commented lines are the offending code and the work around is the "For loop"

谢谢。

List<WinType> _atype = db.WinTypes.Where(wt => wt.IsWin == false).ToList();
List<WinType> _wtype = db.WinTypes.Where(wt => wt.IsWin == true).ToList();
string test = _wtype.Where(wt => wt.Value ==0).Select(wt => wt.Description).SingleOrDefault();
List<WinCheckDetails> wcd = db.Wins.Include("UserProfiles").Where(w => w.venueLogId == logid).Select(w => new WinCheckDetails
{
    //awarddesc = w.atypeid.HasValue ? _atype.Where( wt=> wt.Value == w.atypeid).Select(wt => wt.Description).SingleOrDefault():string.Empty,
    //windesc = _wtype.Where(wt => wt.Value == w.typeid).Select(wt => wt.Description).Single(),
    atypeid = w.atypeid,
    typeid = w.typeid,
    WinId = w.WinId,
    other = w.other,
    posterid = w.posterid,
    confirmed = w.confirmed,
    posttime = w.posttime,
    game = w.game,
    playerid = w.UserProfile.PlayerID,
    firstname = w.UserProfile.FirstName,
    lastname = w.UserProfile.LastName,
    fullname = w.UserProfile.FirstName + " " + w.UserProfile.LastName
}).OrderBy(o => o.game).ToList();
foreach (WinCheckDetails wc in wcd)
{
    wc.awarddesc = _atype.Where(wt => wt.Value == wc.atypeid).Select(wt => wt.Description).SingleOrDefault();
    wc.windesc = _wtype.Where(wt => wt.Value == wc.typeid).Select(wt => wt.Description).SingleOrDefault();
}

推荐答案

_atype 和 _wtype 是 WinType 在内存中的列表，因为您正在将 ToList（）应用于查询。对于数据库查询，它们是恒定值的集合，因为要在数据库中执行查询，必须将它们作为它们在内存中的值传输到数据库服务器。 EF不支持将此类常量或值的集合从内存转移到数据库中，除非它们是原始类型的值（例如， int ）。这就是为什么会出现异常的原因。

_atype and _wtype are lists of WinType in memory because you are applying ToList() to the queries. With respect to database queries they are collections of constant values because to perform the query in the database they have to be transmitted to the database server as the values they are in memory. EF doesn't support to transfer such constant values or collections of values from memory to the database unless they are values of primitive types (int for example). That's the reason why you get an exception.

您尝试使用 _atype 和 _wtype 作为 IQueryable 而不是列表：

Did you try to use _atype and _wtype as IQueryable instead of lists:

IQueryable<WinType> _atype = db.WinTypes.Where(wt => !wt.IsWin);
IQueryable<WinType> _wtype = db.WinTypes.Where(wt => wt.IsWin);

List<WinCheckDetails> wcd = db.Wins
    .Where(w => w.venueLogId == logid)
    .Select(w => new WinCheckDetails
    {
        awarddesc = w.atypeid.HasValue
            ? _atype.Where(wt=> wt.Value == w.atypeid)
                    .Select(wt => wt.Description).FirstOrDefault()
            : string.Empty,
        windesc = _wtype.Where(wt => wt.Value == w.typeid)
                        .Select(wt => wt.Description).FirstOrDefault(),

        // ... (unchanged)
    }).OrderBy(o => o.game).ToList();

我删除了 Include ，因为它将使用 Select 执行投影时，无论如何都会被忽略。我也用 FirstOrDefault 替换了 SingleOrDefault 和 Single 投影中不支持（并且 First 都不支持），仅支持 FirstOrDefault 。

I have removed the Include because it will be ignored anyway when you perform a projection with Select. Also I have replaced SingleOrDefault and Single by FirstOrDefault because both are not supported in a projection (and First neither), only FirstOrDefault is supported.

我不确定这是否行得通。但这应该可以消除您的例外情况（但也许您还会得到另一个...）。

I am not sure if that will work. But it should remove your exception (but maybe you'll get another one...).

这篇关于“无法创建类型为..的常量值。仅支持基本类型。”在EF查询中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！