Linq exception when join a list on a query result

Question

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?

Try not using the equality operator ("==") in your query. – Mitulát báti Jun 26 '15 at 11:50 — Mitulát báti, Jun 26 '15 at 11:50

Niels Filter · Accepted Answer · 2015-06-26T12:27:32.317

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

If that list of `MyClass` is already in memory I suspect the removal of `.AsQueryable()` cleans this right up. (edit: well, _and_ not declaring it as queryable) — clarkitect, Jun 26 '15 at 12:59

score 0 · Answer 2 · answered Jun 26 '15 at 14:45

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!

Note that two `Contains` is [not the same as one join](http://stackoverflow.com/a/26201371/861716)! — Gert Arnold, Jun 26 '15 at 17:48

Linq exception when join a list on a query result

2 Answers2

Linked