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

Can you add the relevant parts of your Students and Subjects models? — Will Jenkins
– Will Jenkins, Commented Nov 21, 2014 at 13:01
We need to see your model definitions. EF is telling you that Subjects is not queryable. — Michael Edenfield
– Michael Edenfield, Commented Nov 21, 2014 at 13:11

StuartLC · Accepted Answer · 2014-11-21 13:27:03Z

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();

Mateusz Rogulski · Accepted Answer · 2014-11-21 13:24:58Z

0

Have you tried this:

var count = context.Students.Select(x=>new { Name = x.Student.FirstName, 
    NoOfSubject = x.Subjects.SubjectId.Count}).ToList();

edited Nov 21, 2014 at 13:24

Mateusz Rogulski

7,4638 gold badges47 silver badges62 bronze badges

answered Nov 21, 2014 at 13:11

Saket Kumar

4,8816 gold badges38 silver badges61 bronze badges

Collectives™ on Stack Overflow

Unable to get Count in lambda expression - NotSupportedException

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related