Unable to get Count in lambda expression - NotSupportedException

Question

I am trying to get the number of subjects each student have using the below query:

var selectedSubject = context.Students
                             .Include(d => d.Subjects)
                             .Select(dr => new
                                           {
                                               Name = dr.Student.FirstName,
                                               NoOfSubject = dr.Subjects.Count
                                           })
                             .ToList();

But I am getting an exception

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: The specified type member 'Subjects' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

Answer 1

You won't be able to access non entity member Properties on the IList interface (like Count) until the query is materialized.

Either materialize early:

var selectedSubject = context.Students
                         .Include(d => d.Subjects)
                         // .Where() goes here, 
                         .ToList()
                         .Select(dr => new
                                       {
                                           Name = dr.Student.FirstName,
                                           NoOfSubject = dr.Subjects.Count
                                       })
                         .ToList();

Or, make use of the Count() method, which will be mappable into Sql:

var selectedSubject = context.Students
                         .Include(d => d.Subjects)
                         .Select(dr => new
                                       {
                                           Name = dr.Student.FirstName,
                                           NoOfSubject = dr.Subjects.Count()
                                       })
                         .ToList();