Why is this linq expression not working?

Question

I'm using LINQ to Entities.

I have a table called Student; it has ID and Name as it's columns. ID is a primary key.

I'd like to be able select the name of the Student and get the amount of Students with the same Name.

So for example I'd have this as my table data.

ID  Name  
1   Bob
2   Will
3   Bob

After performing the query I'd return a List of Student objects looking like this.

Name    Quantity
Bob     2
Will    1

I guess it is kind of similar to how the Tags page of stackoverflow works; It has the name and the quantity.

Anyways, I created a partial class called Student.cs in which I added a Quantity property like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MySite.Models
{
    public partial class Student
    {
        private int _quantity;

        public int Quantity
        {
            get { return _quantity; }
            set { _quantity = value; }
        }
    }
}

I came up with this but I'm getting an error..

    public IQueryable<Student> FindStudentsDistinctWithQuantity()
    {
        /*SELECT Name, COUNT(Name) AS Quantity
        FROM Student
        GROUP BY Name*/

        var students= (from s in db.Students
                    group s by s.Name into g
                    select new {Name = g.Key, Quantity = g.Count()});            

        return students;
    }

The error i'm getting says something like Can't convert from type Anonymous to the Student list. Does it have something to do with it not recognizing the quantity field I added in the partial class?

Thanks!

Answer 1

The problem is that you are not returning students - you are trying to return an anonymous type from your function. This is not allowed.

Create a class to represent your result and use new MyClass { ... } instead of new { ... } in your query, and change the method to return IQueryable<MyClass> instead of IQueryable<Student>.

You could for example make a class called StudentNameAndResults.

class StudentNameAndResults
{
    public string Name { get; set; }
    public int Quantity { get; set; }
}

Alternatively, you could just return the result as a Dictionary or an IEnumarable of IGrouping. For example:

public IDictionary<string, int> FindStudentsDistinctWithQuantity()
{
    Database db = new Database();
    var students= (from s in db.Students
                group s by s.Name into g
                select new {Name = g.Key, Quantity = g.Count()});

    return students.ToDictionary(s => s.Name, s => s.Quantity);
}

Also, the property you created is using the verbose syntax from pre-C# 3.0 days. Now you can use auto-implemented properties if you don't need any special logic:

public int Quantity { get; set; }

Answer 2

Change your Student type to look like this:

public partial class Student
{
    public Int32 Quantity { get; set; }
    public String Name { get; set; }
}

And your query to look like this:

var students = from s in db.Students
               group s by s.Name into g
               select new Student { 
                   Name = g.Key, 
                   Quantity = g.Count() };

Your method returns an IQueryable<Student> but you are currently returning an IQueryable<T> of a projected anonymous type.

You need to refactor your Student type to have a Name property of type String and then project new instances of your Student type from the expression so that the return type of your expression will match the return type of your method.

Answer 3

 var students= (from s in db.Students
                    group s by s.Name into g
                    select new {Name = g.Key, Quantity = g.Count()}); 

This is an anonymous type, not IQueryable<Student>. Either you need to return System.Object, or you need to return IQueryable<Student> as follows...

return from s in db.Students
       group s by s.Name into g
      select new Student{Name = g.Key, Quantity = g.Count()};

Where Student defines the properties used in the initializtion.

Answer 4

The select new keywords are causing the form of the data to change, which means the LINQ query will not return an IQueryable<Student>, but rather an anonymous type containing the "Name" and "Quantity" properties. If you change it to return a concrete type rather than an anonymous one you will be able to retrieve the data in the form you want.

public class StudentGrouping {
    public string Name { get; set; }
    public int Quantity { get; set; }
}

public IQueryable<StudentGrouping> FindStudentsDistinctWithQuantity()
{
    /*SELECT Name, COUNT(Name) AS Quantity
    FROM Student
    GROUP BY Name*/

    var students= (from s in db.Students
                group s by s.Name into g
                select new StudentGrouping {
                   Name = g.Key, 
                   Quantity = g.Count()
                }).AsQueryable();            

    return students;
}

Answer 5

Your function returns Student

public IQueryable<Student> FindStudentsDistinctWithQuantity(){ ... }

But your Linq query returns a new type that contains a Name and an Int (count)

               >>> select new {Name = g.Key, Quantity = g.Count()});            

y-try select new Student{Name = g.Key, Quantity = g.Count()}

Answer 6

You are doing a projection in your linq query. If you'd hover the cursor over var students inside vs you'll see its a collection of an anonymous type.

If you want to return an IQueryabley<Student> you need to do:

 var students= from s in db.Students
                    group s by s.Name into g
                    select s.Key; 

There is no way outside methods can know about the anonymous type you have created in your previous example, so you won't be able to return a typed collection.

With the method I suggested you will still be able to do a projection on the return value of your method later on, since IQueryable is composable until the first enumeration:

var students = FindStudentsDistinctWithQuantity();
var namesAndQunatity = from s in students select new {s.Name, s.Quantity};

Answer 7

The method's return value ties the "students" collection to IQueryable<Student> but... the Linq expression is creating an IQueryable<some anonymous type>, and there is no conversion between the two.

You may get a baby step further by modifying your select part to be:

select new Student() {....}

Hopefully this helps,

Tyler