Linq exception when join a list on a query result

2019-08-14 04:34发布

问题:

I have a list:

List<MyClass> lstClass1;

Where MyClass has 2 simple string property:

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

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

MyTable
   column MainKey
   column AlternativeKey

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:

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

What i'm doing wrong?

回答1:

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 Options, you could consider:

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;

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

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 ...


回答2:

Niels, option 1 is a good suggest, but i must filter like this:

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

var property1List = (from prp in lstClass1 select prp.property1).Distinct();
var property2List = (from prp in lstClass1 select prp.property2).Distinct();

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;

var selMain = from main in source 
                     where main.AlternativeKey == null &&
                     (property1List.Contains(main.MainKey.Substring(0, 4)) ||
                     property2List.Contains(main.MainKey.Substring(0, 4)))
                     select main;

source = alt.Union(main);

Thank you!