Linq错误 - 在此上下文中支持原始类型或枚举类型。 [英] Linq error - nly primitive types or enumeration types are supported in this context.

问题描述

大家好，



请帮忙，我在执行linq查询时遇到此错误。非常感谢，





System.Data.Entity.dll中出现'System.NotSupportedException'类型的异常，但是未在用户代码中处理



附加信息：无法创建程序类型的常量值。在此上下文中仅支持原始类型或枚举类型。

 Dim _ctx As New rsEntities 
 Dim _dctx As new psEntities 
 
 Dim users =（From u in _dctx.Users 
 Select u）。ToArray（）
 
 Dim userRoleIn = （来自uri In _dctx.UserRoles 
 Select uri）。ToArray（）
 
 Dim progs =（from p in _dctx.Programmes 
 Select p）.ToArray（）
 
 Dim query =（来自swt In _ctx.Workflows 
加入wts in _ctx.WorkflowStatuses on wts.WTS_ID Equals swt.WTS_ID 
加入sw in _ctx.StaffWorks on sw.SW_ID Equals swt。 SW_ID 
加入cr in _ctx.Requests on cr.CR_ID Equals swt.CR_ID 
 Join sp in _ctx.Progs on sp.SP_Id Equals sw.SP_ID 
 Join sps in _ctx.ProgStatuses On sps。 SPS_ID等于sp.SPS_ID 
加入s in _c tx.Staffs On s.S_ID Equals sp.S_ID 
 Join p in progs On p.P_Id Equals sp.P_Id 
加入u in users on u.S_ID Equals s.S_ID 
加入uri In userRoleIn On uri.U_ID Equals u.U_Id 
其中uri.P_Id.HasValue.Equals（15）
其中uri.Role_Id = 13 
其中不_ctx.Responses.Any（函数） es.CR_ID = cr.CR_ID和es.S_ID = s.S_ID）
其中sps.IsActive = True和wts.Description.ToLower.Contains（no）
按顺序排序s.Surname，s .FirstName，cr.Title 
选择New With {.ProgName = p.Name，
 .Surname = s.Surname，
 .FirstName = s.FirstName，
 .Number = s.StaffNumber，
 .Title = cr.Title，
。日期= sw.StartD ate，
 .Complete = wts.Description，
 .IsActive = If（sps.IsActive = True，Yes，No），
 .HasAccess = If（uri.Role_Id = 23，是，否），
 .HasEntry = If（String.IsNullOrEmpty（u.U_Id）= True，No，Yes），
 .Email = u.U_Email ToList 
 

解决方案

因为你在 users ， userRoleIn 和 progs ，它们成为非原始的内存集合无法与数据库查询连接的类型（查询）。



您的选项：



1）删除.ToArray（） - 调用。

如果你收到错误说指定的LINQ表达式包含对与不同co相关联的查询的引用ntexts 您需要移动实体类型用户， UserRoles ，程序和工作流程 进入相同的DbContext 。



2）将这些实体/联接留出查询 query 并在内存中查询结果中执行此连接客户端。这可能会产生巨大的性能缺陷。



编辑：实施选项2看起来像这样：

 ' 遗漏与progs，users，userRoleIn  $相关的任何内容b $ b ' 并添加以下内存中查询所需的字段 
 < span class =code-comment>' （sp.P_Id，s.S_ID，u.U_Id） 
 
 < span class =code-keyword> Dim  query =（from swt  In  _ctx.Workflows 
加入wts 在 _ctx.WorkflowStatuses 开启 wts.WTS_ID等于swt.WTS_ID 
加入sw 在 _ctx.StaffWorks 开启 sw.SW_ID等于swt.SW_ID 
加入cr 在 _ctx.Requests 在 cr.CR_ID等于swt.CR_ID 
加入sp 在 _ctx.Progs 在 sp.SP_Id等于sw.SP_ID 
加入sps 在 _ctx.ProgStatuses 在 sps.SPS_ID等于sp.SPS_ID 
加入s 在 _ctx.Staffs  On  s.S_ID等于sp.S_ID 
其中不 _ctx.Responses.Any（功能（es）es.CR_ID = cr.CR_ID 和 es.S_ID = s.S_ID）
其中sps.IsActive =  True  和 wts.Description.ToLower.Contains （ 没有）
顺序s.Surname，s.FirstName，cr.Title 
 选择 新 使用 {.Surname = s.Surname，
 .FirstName = s.FirstName，
 .Number = s .StaffNumber，
。标题= cr.Title，
。日期 = sw.StartDate，
 .Complete = wts.Description， 
 .IsActive = 如果（sps.IsActive =  True ， 是， 没有），
 .P_Id = sp.P_Id，
 .S_ID = s.S_ID，
 .U_Id = u.U_Id，
}）。ToList 
 
 ' < span class =code-comment>然后使用内存中查询执行剩余联接/条件 
 ' 并重新组装并完成结果类型： 
 
  Dim  query2 =来自q  查询
加入p 在 progs  On  p.P_Id等于q.P_Id 
加入你在用户开启 u.S_ID等于q.S_ID 
加入uri 在 userRoleIn 开启 uri.U_ID等于q.U_Id 
其中uri.P_Id.HasValue.Equals（ 15 ）
其中uri.Role_Id =  13  
 选择 新 使用 {.ProgName = p.Name，
。姓氏= q.Surname ，
 .FirstName = q.FirstName，
 .Number = q.Number，
 .Title = q.Title，
。 Date  = q。日期，
 .Complete = q.Complete，
 .IsActive = q.IsActive，
。 HasAccess = 如果（uri.Role_Id =  23 ， 是， 否），
 .HasEntry = 如果（ String  .IsNullOrEmpty（u.U_Id）=  True ，  No，  是），
 .Email = u.U_Email}）。ToList 

（我试着写它以便它实际上可以工作但是由于我无法测试它，请不要依赖它 - 无论如何，我想你会理解它一般会如何工作。）

根据小型研究，我认为问题出在这里：

其中不是_ctx.Responses.Any（函数） .CR_ID = cr.CR_ID和es.S_ID = s.S_ID）

似乎你在尝试使用 NOT IN（...） 子句。在Linq查询中不受欢迎。



看看这里：实体框架 - 无法创建类型为'闭包类型'的常量值...错误 [ ^ ]了解更多详情。



我建议使用方法除外 [ ^ ]投入 NOT IN（...）。

Hi All,

Please can you help, I'm getting this error when the linq query is executed. Many thanks,


An exception of type 'System.NotSupportedException' occurred in System.Data.Entity.dll but was not handled in user code

Additional information: Unable to create a constant value of type 'Programme'. Only primitive types or enumeration types are supported in this context.

Dim _ctx As New rsEntities
       Dim _dctx As New psEntities

       Dim users = (From u In _dctx.Users
                   Select u).ToArray()

       Dim userRoleIn = (From uri In _dctx.UserRoles
                        Select uri).ToArray()

       Dim progs = (From p In _dctx.Programmes
                   Select p).ToArray()

       Dim query = (From swt In _ctx.Workflows
                   Join wts In _ctx.WorkflowStatuses On wts.WTS_ID Equals swt.WTS_ID
                   Join sw In _ctx.StaffWorks On sw.SW_ID Equals swt.SW_ID
                   Join cr In _ctx.Requests On cr.CR_ID Equals swt.CR_ID
                   Join sp In _ctx.Progs On sp.SP_Id Equals sw.SP_ID
                   Join sps In _ctx.ProgStatuses On sps.SPS_ID Equals sp.SPS_ID
                   Join s In _ctx.Staffs On s.S_ID Equals sp.S_ID
                   Join p In progs On p.P_Id Equals sp.P_Id
                   Join u In users On u.S_ID Equals s.S_ID
                   Join uri In userRoleIn On uri.U_ID Equals u.U_Id
                   Where uri.P_Id.HasValue.Equals(15)
                   Where uri.Role_Id = 13
                   Where Not _ctx.Responses.Any(Function(es) es.CR_ID = cr.CR_ID And es.S_ID = s.S_ID)
                   Where sps.IsActive = True And wts.Description.ToLower.Contains("no")
                   Order By s.Surname, s.FirstName, cr.Title
                   Select New With {.ProgName = p.Name,
                                       .Surname = s.Surname,
                                       .FirstName = s.FirstName,
                                       .Number = s.StaffNumber,
                                       .Title = cr.Title,
                                       .Date = sw.StartDate,
                                       .Complete = wts.Description,
                                       .IsActive = If(sps.IsActive = True, "Yes", "No"),
                                       .HasAccess = If(uri.Role_Id = 23, "Yes", "No"),
                                       .HasEntry = If(String.IsNullOrEmpty(u.U_Id) = True, "No", "Yes"),
                                       .Email = u.U_Email}).ToList

解决方案

Because you're calling .ToArray() on the queries for users, userRoleIn and progs, they become in-memory collections of non-primitive types which can't be joined with a database-query (query).

Your options:

1) Remove the .ToArray()-calls.
In case you then get an error saying "The specified LINQ expression contains references to queries that are associated with different contexts" you would need to move the Entity-types Users, UserRoles, Programmes and Workflows into the same DbContext.

2) Leave these entities/joins out of the query "query" and perform this join client-side on the in-memory query results. Which could potentially have a huge performance drawback.

edit: Implementing option 2 would look something like this:

' leaving out anything that's related to progs, users, userRoleIn
' and adding fields that are required for the following in-memory query
' (sp.P_Id, s.S_ID, u.U_Id)

        Dim query = (From swt In _ctx.Workflows
                    Join wts In _ctx.WorkflowStatuses On wts.WTS_ID Equals swt.WTS_ID
                    Join sw In _ctx.StaffWorks On sw.SW_ID Equals swt.SW_ID
                    Join cr In _ctx.Requests On cr.CR_ID Equals swt.CR_ID
                    Join sp In _ctx.Progs On sp.SP_Id Equals sw.SP_ID
                    Join sps In _ctx.ProgStatuses On sps.SPS_ID Equals sp.SPS_ID
                    Join s In _ctx.Staffs On s.S_ID Equals sp.S_ID
                    Where Not _ctx.Responses.Any(Function(es) es.CR_ID = cr.CR_ID And es.S_ID = s.S_ID)
                    Where sps.IsActive = True And wts.Description.ToLower.Contains("no")
                    Order By s.Surname, s.FirstName, cr.Title
                    Select New With {   .Surname = s.Surname,
                                        .FirstName = s.FirstName,
                                        .Number = s.StaffNumber,
                                        .Title = cr.Title,
                                        .Date = sw.StartDate,
                                        .Complete = wts.Description,
                                        .IsActive = If(sps.IsActive = True, "Yes", "No"),
                                        .P_Id = sp.P_Id,
                                        .S_ID = s.S_ID,
                                        .U_Id = u.U_Id,
                                    }).ToList

' and then performing the left-out joins/conditions with an in-memory query
' and re-assembling and completing the result-type:

Dim query2 = From q in query
             Join p In progs On p.P_Id Equals q.P_Id
             Join u In users On u.S_ID Equals q.S_ID
             Join uri In userRoleIn On uri.U_ID Equals q.U_Id
             Where uri.P_Id.HasValue.Equals(15)
             Where uri.Role_Id = 13
             Select New With {.ProgName = p.Name,
                              .Surname = q.Surname,
                              .FirstName = q.FirstName,
                              .Number = q.Number,
                              .Title = q.Title,
                              .Date = q.Date,
                              .Complete = q.Complete,
                              .IsActive = q.IsActive,
                              .HasAccess = If(uri.Role_Id = 23, "Yes", "No"),
                              .HasEntry = If(String.IsNullOrEmpty(u.U_Id) = True, "No", "Yes"),
                              .Email = u.U_Email}).ToList

(I tried to write it so it will actually work but since I can't test it, please don't rely on it - in any case, I think you'll understand how it would work in general.)

As per small research, i think the problem is here:

Where Not _ctx.Responses.Any(Function(es) es.CR_ID = cr.CR_ID And es.S_ID = s.S_ID)

Seems you're trying to use NOT IN(...) clause. It's not appreciated within Linq query.

Have a look here: Entity Framework - "Unable to create a constant value of type 'Closure type'…" error[^] for more details.

I'd suggest to use Except method[^] insted of NOT IN(...).

这篇关于Linq错误 - 在此上下文中支持原始类型或枚举类型。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！