CODEWITHSUNDEEP
linq-to-sqlc#-4.0
Pradeep
Pradeep

Reputation: 271

Unable to create a constant value of type 'T'

I have a table called Subjects,

I have an another Table called Allocations, which stores the Allocations of the Subjects

I have a Datagridview, which is populated with Subject Allocations from the Allocations Table

Now i need to get the Subjects that are not in the Datagridview

To do this

  1. I Get All Subjects from the ObjectContext
  2. Now i get all the Subjects that are alloted from the Datagridview (It Returns me an InMemory Collection)
  3. Now i use the LINQ.EXCEPT method to filter the results, but it is throwing me the Following Exception,

"Unable To Create Constant Value of Type "ObjectContext.Subjects" Only primitive types ('such as Int32, String, and Guid') are supported in this context."

Below is my Code

    public static IOrderedQueryable<Subject> GetSubjects()
    {
        return OBJECTCONTEXT.Subjects.OrderBy(s => s.Name);
    }

    private IQueryable<Subject> GetAllocatedSubjectsFromGrid()
    {
          return (from DataGridViewRow setRow in dgv.Rows          
                    where !setRow.IsNewRow
                    select setRow.DataBoundItem).Cast<Allocation>()  //I know the Problem lies somewhere in this Function
                    .Select(alloc =>alloc.Subject).AsQueryable();
    }

    private void RUN()
    {
        IQueryable<Subject> AllSubjects = GetSubjects(); //Gets

        IQueryable<Subject> SubjectsToExclude = GetAllocatedSubjectsFromGrid();

        IQueryable<Subject> ExcludedSubjects = AllSubjects.Except(SubjectsToExclude.AsEnumerable());   
        //Throwing Me "Unable to create a constant value of type 'OBJECTCONTEXT.Subject'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."
    }

As a result of googling i found that it happens because LINQ can't compare between InMemory collection(Records from DGV) and Objectcontext(FromDB)

Upvotes: 2

Views: 884

Answers (2)

Dave Cousineau
Dave Cousineau

Reputation: 13158

I got around this by comparing keys in a Where clause rather than using Except.

So instead of:

var SubjectsToExclude = GetAllocatedSubjectsFromGrid();
var ExcludedSubjects = AllSubjects.Except(SubjectsToExclude.AsEnumerable());

Something more like:

var subjectsToExcludeKeys =
   GetAllocatedSubjectsFromGrid()
   .Select(subject => subject.ID);

var excludedSubjects =
   AllSubjects
   .Where(subject => !subjectsToExcludeKeys.Contains(subject.ID));

(I'm guessing what your entity's key looks like though.)

This allows you to keep everything in Entity Framework, rather than pulling everything into memory.

Upvotes: 0

Pleun
Pleun

Reputation: 8920

A little short of time, have not tested it. But I guess you can try to get it all in memory. So instead of using 

  IQueryable<Subject> AllSubjects = GetSubjects(); //Gets

You do

List<Subject> AllSubjects = GetSubjects().ToList(); //

List<Subject> SubjectsToExclude = GetAllocatedSubjectsFromGrid().ToList();    

List<Subject> ExcludedSubjects = AllSubjects.Except(SubjectsToExclude);

Upvotes: 2

Related Questions