在查询结果上加入列表时的Linq异常 [英] Linq exception when join a list on a query result

问题描述

我有一个列表:

List<MyClass> lstClass1;

MyClass具有2个简单的字符串属性:

Where MyClass has 2 simple string property:

class MyClass
{
   public string property1 { get; set; }
   public string property2 { get; set; }
}

我在数据库MyTable上有一个要查询的表，其中有两个字符串类型列:

And I have a table on DB, MyTable, to query, where there are two string type columns:

MyTable
   column MainKey
   column AlternativeKey

对于我的脚本，我必须选择数据库和列表的联接，并遵循以下规则:如果AlternativeKey存在，则AlternativeKey中的前4个字符等于MyClass.property1或MyClass.Property2时，选择行，否则选择如果MainKey中的前4个字符等于MyClass.property1或MyClass.Property2，则行.这是我的实现:

For my script, i must select a join of DB and the list, with the following rule: if AlternativeKey exist, select the row if the first 4 characters in AlternativeKey equal to MyClass.property1 or MyClass.Property2, else select the row if the first 4 characters in MainKey equal to MyClass.property1 or MyClass.Property2. This is my implementation:

IQueryable<MyTable> source = getMyTable();
List<MyClass> lstClass1 = getListClass();

IQueryable<MyClass> qMyClassList = lstClass1.AsQueryable<MyClass>();

IQueryable<MyTable> selAlternative = from alt in source
                                     join cl1 in qMyClassList on
                                     alt.AlternativeKey.Substring(0, 4)
                                     equals cl1.property1
                                     join cl2 in qMyClassList on
                                     alt.AlternativeKey.Substring(0, 4)
                                     equals cl2.property2
                                     where alt.AlternativeKey != null && alt.AlternativeKey.Length >= 4
                                     select alt;

IQueryable<MyTable> selMain = from main in source
                                     join cl1 in qMyClassList on
                                     main.MainKey.Substring(0, 4)
                                     equals cl1.property1
                                     join cl2 in qMyClassList on
                                     main.MainKey.Substring(0, 4)
                                     equals cl2.property2
                                     where main.AlternativeKey == null && main.MainKey.Length >= 4
                                     select main;

source = alt.Union(main);

在执行中，当我在结果元素上循环时，此查询引发此异常:

In execution, this query raise this exception when i loop on the result element:

无法创建类型为'MyTable + MyClass'的常量值.仅有的 在此上下文中支持基本类型或枚举类型.

Unable to create a constant value of type 'MyTable+MyClass'. Only primitive types or enumeration types are supported in this context.

我做错了什么?

推荐答案

您正在将内存集合:qMyClassList加入到IQueryable数据源.例外是因为IQueryable LINQ提供程序无法将join转换为相关查询.

You're joining a memory collection: qMyClassList to an IQueryable datasource. The exception is because the IQueryable LINQ Provider isn't able to translate the join into the relevant query.

2个选项，您可以考虑:

2 Options, you could consider:

选项1:您可以尝试在where中使用Contains而不是join.您的LINQ查询提供程序可能能够将您的查询解释为WHERE .. IN ('val1','val2'...).所以对您来说，看起来像这样:

Option 1: You could try using Contains in the where instead of join. Your LINQ Query provider may be able to interpret your query as WHERE .. IN ('val1','val2'...). So for you this would look something like this:

var selAlternative = from alt in source 
                     where alt.AlternativeKey != null &&
                           property1List.Contains(alt.AlternativeKey.Substring(0, 4)) &&
                           property2List.Contains(alt.AlternativeKey.Substring(0, 4)) &&
                     select alt;

请记住，Contains仅适用于原始类型.在您的情况下，这似乎是string，所以很好.

Remember that Contains will only work with primitive types. In your case this seems to be a string, so it's fine.

选项2 ，如果要保持连接不变，只需.ToList()您的source实例.如果您的源很大，这通常不是一个好主意，因为您将整个数据集加载到内存中，并且联接已应用到内存中.):

Option 2, if you want to keep your join as is, simply .ToList() your source instance. This usually isn't a good idea if your source is large, since you load the entire dataset into memory and the join is applied in memory.):

var selAlternative = from alt in source.ToList()
                     join cl1 in qMyClassList on ...

这篇关于在查询结果上加入列表时的Linq异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！