Join Error | Unable to create a constant value of type only primitive

Question

Very new with LINQ here.

I have the following data in my table (TableA):

ID  Name      SubmissionNo
1   Jim       A-1
2   Andy      A-2
3   Rick      A-2
4   Mary      A-3
5   Zim       A-4
6   Loren     A-1

I then need to create a query which will allow me to get from that table, those records which have duplicate submission numbers.

Here's my solution so far (Context is the database context):

var duplicates = (from tbl in Context.TableA.AsNoTracking()
                  group tbl by tbl.SubmissionNo into grp
                  select new { count = grp.Count(), submissionNo = grp.Key})
                 .Where(x => x.count > 1)
                 .OrderBy(y => y.submissionNo).ToList();

The variable duplicates then contains the record:

count  submissionNo
2      A-1
2      A-2

I then write the main query which will allow me to get all the records from TableA which has duplicate submissionNo

var myList = (from tbl in Context.TableA.AsNoTracking()
              join dup in duplicates on tbl.SubmissionNo equals dup.submissionNo
              select new 
              { 
                ID = tbl.ID, 
                Name = tbl.Name, 
                SubmissionNo = tbl.SubmissionNo 
              })
              .ToList();

I am then getting an error for the myList query with

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

I think there must be a better way to do this as from the TableA above, I practically want the following results:

ID  Name      SubmissionNo
1   Jim       A-1
2   Andy      A-2
3   Rick      A-2
6   Loren     A-1

Possible duplicate of https://stackoverflow.com/questions/18929483/unable-to-create-a-constant-value-of-type-only-primitive-types-or-enumeration-ty — Zysce, Aug 28 '18 at 19:16
Hi @Zysce - I did checked the link provided but it does not do the same thing I wanted. — Chett, Aug 28 '18 at 19:17
use duplicates as a list of submission numbers only and then in your last Linq query instead of join try using where list contains — Piyush Khanna, Aug 28 '18 at 19:23
Do you also get this error if you don't do the ToList in your Duplicates, so if you let Duplicates be of type IQueryable, instead of a List? — Harald Coppoolse, Aug 28 '18 at 19:59

Gert Arnold · Accepted Answer · 2018-08-30T15:01:16.967

Your first query, slightly modified, has all information you need:

var myList  = from tbl in Context.TableA.AsNoTracking()
              group tbl by tbl.SubmissionNo into grp
              where grp.Count() > 1
              from item in grp
              select new
              { 
                  count = grp.Count(),
                  submissionNo = grp.Key,
                  item.Name,
              );

The pattern group into grp - from item in grp is a commonly used query pattern to group items and then flatten the group again, while keeping in touch with the group data (like Count() and Key).

Now you don't need the join anymore and the exception doesn't occur. By the way, the exception tells you that EF can only handle joins with collections of primitive types (int etc.), because it has to translate the whole expression into SQL. There's simply no translation for rich objects like TableA.

By the way, the query can be improved by removing the repeated Count():

var myList  = from tbl in Context.TableA.AsNoTracking()
              group tbl by tbl.SubmissionNo into grp
              let count = grp.Count()
              where count > 1
              from item in grp
              select new
              { 
                  count = count,
                  submissionNo = grp.Key,
                  item.Name,
              );

This will generate a more efficient SQL statement containing one COUNT instead of two.

score 1 · Answer 2 · answered Aug 28 '18 at 19:27

Since Entity Framework does not support joining in-memory collections of objects with database collections, a common workaround for this is to filter using Contains.

First, you need to get the IDs to filter on:

var duplicates = (from tbl in Context.TableA.AsNoTracking()
                  group tbl by tbl.SubmissionNo into grp
                  select new { count = grp.Count(), submissionNo = grp.Key})
                 .Where(x => x.count > 1)
                 .OrderBy(y => y.submissionNo)
                 .ToList();
var duplicateIds = duplicates.Select(x => x.submissionNo).ToList();

And then change your query to perform a WHERE...IN instead of a JOIN:

var myList = (from tbl in Context.TableA.AsNoTracking()
              where duplicateIDs.Contains(tbl.SubmissionNo)
              select new 
              { 
                ID = tbl.ID, 
                Name = tbl.Name, 
                SubmissionNo = tbl.SubmissionNo 
              })
              .ToList();

Join Error | Unable to create a constant value of type only primitive

2 Answers2