Linq to Sql NotSupportedException "Explicit object construction in query is invalid" when selecting a new type of another database table
I am trying to get a different datatype from a Linq query as a result after grouping and averaging the values from the grouped results.
For example:
I have a table called classes with these columns:
long StudentID
long ExamID
int Grade
And another table called examAverage with a similar structure:
long ExamID
int Grade
Now I want to select all rows in the class table and group them using ExamID. Then compare the scores and save the score for each exam in the examAverage table.
In request, I am not like this
var values = (from grades in db.Grades
group grades by grades.ExamID into avgGrade
select new examAverage
{
ExamID = avgGrade.Key,
Grade= (int)avgGrade.Average(x => x.Grade)
});
Unfortunately it always throws a System.NotSupportedException, telling me that they are not allowed to create a new instance of the Examity of the middle Entitytype. However, when I change the database to a list of classes and store the result in a list of type examAverage, it works fine.
Now for my question. Is it possible to do something like this and how? If there is no other way to do something like this?
source to share
Probably your problem is that "examAverage" is also a Linq object: it maps to a table or view.
Linq to Objects doesn't check for this, so why don't you get an exception when asking for a list of objects. Linq to SQL DOES, in part because it wants to protect you from co-creating multiple objects containing data containing data that does not reflect your actual state of the database.
So what you can do depends on what you want:
If you want to get a list of custom objects built in a database query, use a different type, for example. MyExamAverage - Possibly defined in another assembly.
If you really want to create database records (select ... in), you can use the anonymous type:
var values = (from grades in db.Grades
group grades by grades.ExamID into avgGrade
select new
{
ExamID = avgGrade.Key,
Grade= (int)avgGrade.Average(x => x.Grade)
});
foreach (var value in values)
{
var grade = new examAverage{
ExamID = value.ExamId,
Grade = value.Grade
};
// save grade here
}
If you MUST return a list from a method, you can:
private IEnumerable<examAverage> GetGrades()
{
var values = (from grades in db.Grades
group grades by grades.ExamID into avgGrade
select new
{
ExamID = avgGrade.Key,
Grade= (int)avgGrade.Average(x => x.Grade)
});
foreach (var value in values) yield return new examAverage{
ExamID = value.ExamId,
Grade = value.Grade
};
}
source to share