实体LINQ语句内的Join集合 [英] Join collection inside an Entity LINQ statement

问题描述

此问题与LINQ-to-entity有关.

This problem related to LINQ-to-entity.

我发布了一个类似的问题，但它没有答案就令人困惑，因此，我提供了一个示例，并大声呼救.

I posted a similar question but it got confusing without an answer, so I am providing an example and a new shout for help.

我有一个"Colors"类，其中包含一个ObservableCollection，它具有两个成员，分别为Index和Name，并且填充如下:

I have a class "Colors" containing an ObservableCollection which has two members, Index and Name and populated like :

0-红色
1-蓝色
2-绿色

0 - Red
1 - Blue
2 - Green

，我有一个数据库表，其中包含我最喜欢的颜色的整数列表.我想基于数据库中存储的索引值返回一个查询，查询中同时包含我最喜欢的颜色的整数值和匹配名称(由observablecollection返回).

and I have a database table containing a list of integers of my favorite colors. I would like to return a query with both the integer value of my favorite color and also the matching name (returned by the observablecollection) based on the index value stored in the database.

单独使用此语句可以正常工作，并返回我的颜色名称:-

This statement in isolation works fine and returns my color name :-

string ColorName = Colors.Names.Where(x => x.Index == 1).FirstOrDefault().Name;

但是当包含在LINQ-to-entity查询中时:-

but when included within the LINQ-to-entity query :-

var query = from c in context.FavoriteColor
select (new Item
    {
        Id = c.Id,
      ColorName = Colors.Names.Where(x => x.Index == c.ColorIndex).FirstOrDefault().Name
    });

我收到此错误:

无法创建类型为'blah blah'的常量值.只有原始的 在此类型中支持类型(例如Int32，String和Guid) 上下文.

Unable to create a constant value of type 'blah blah'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

我知道该对象可能是在LINQ语句中返回的，但是我认为通过在末尾指定.Name表示法(它是一个字符串成员)，它将使用该表示法并将其分配给"ColorName"，可惜不是.

I understand that maybe the object is being returned within the LINQ statement but I thought by specifying the .Name notation on the end, which is a string member, it would use that and assign it to "ColorName", alas not.

推荐答案

您正在尝试在数据库查询中使用CLR对象. LINQ-to-Entities表示您无法做到这一点.查询完成后获取颜色名称.

You are trying to use a CLR object in a database query. LINQ-to-Entities is saying you can't do that. Get the color name after the query has completed.

var dbItems = context.FavoriteColor.Select(c => new {
    c.Id,
    c.ColorIndex
).ToList();

var items = dbItems.Select(item => new Item {
    Id = item.Id,
    ColorName = Colors.Names.Where(x => x.Index == item.ColorIndex).First().Name
})

这里的想法是对ToList的调用会命中数据库，并在CLR对象中返回结果.然后可以像使用其他任何对象一样使用该对象.

The idea here is that the call to ToList hits the database and returns the results in a CLR object. That object can then be used like any other object.

这篇关于实体LINQ语句内的Join集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！