I have below entities and database context class,
public class Grade
{
public int Id { get; set; }
public string GradeName { get; set; }
public virtual ICollection<Student> Students { get; set; } = new HashSet<Student>();
}
public class Student
{
public int Id { get; set; }
public string StudentName { get; set; }
}
public class SchoolContext : DbContext
{
public DbSet<Grade> Grades { get; set; }
public DbSet<Student> Students { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\MSSQLLocalDB;Database=SchoolDB;Trusted_Connection=True;");
}
}
Getting below error while executing below code, whats need to be done here. Thanks!
I'm looking for solution without adding Grade property in Student class
System.InvalidOperationException: 'The Include property lambda expression 'x => {from Student c in x.Students where ([c].Id == __studentId_0) select [c]}' is invalid. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.'
static void Main(string[] args)
{
var studentId = 1;
var context = new SchoolContext();
var data = context.Grades.Include(x => x.Students.Where(c => c.Id == studentId)).SingleOrDefault();
}
I'm looking for solution without adding Grade property in Student class
So you want to be able to get the Grade
for a Student
, but don't want to add Grade
navigation property to Student
and let the EF Core handle naturally that for you? In other words, losing one of the biggest ORM benefits and start seeking for SQL-ish solutions for a simple requests which ORM can handle with a simple property accessor?
There are ways to do what you want, but I would suggest first asking yourself if you really need that.
Anyway, one possible solution is to use the collection navigation property with Any
as filter:
var studentGrade = context.Grades
.FirstOrDefault(grade => grade.Students.Any(student => student.Id == studentId));
Another is to use the LINQ equivalent of the SQL query:
var studentGrade = (
from grade in context.Grades
from student in grade.Students
where student.Id == studentId
select grade).FirstOrDefault();
You cannot include navigation property by condition. Include can only contains lambda to navigation property without any conditions. To solve your problem you should add navigation property Grade to class Student and select from Students including Grade.
public class Student
{
public int Id { get; set; }
public string StudentName { get; set; }
public Grade StudentGrade { get; set; }
}
…
var data = context.Students.Include(x => x.Grade).Where(c => c.Id == studentId).SingleOrDefault();