none
LINQ count query RRS feed

  • General discussion

  • Hi there,

    I am asked to write a LINQ query to display total number of students supervised by each faculty in the order of faculty name. 

    Here is what I've got so far: 

    from s in Students
    join f in Faculties on s.FID equals f.FID into g
    orderby g.Count() descending, f.FLAST descending
    select new 
    {
        Id = s.FID,
        Name = f.FLast,
        Total = g.Count()
    }

    This is to be written as a C# statement. As you can see there are joins there between tables Students and Faculties.

    Thank you

    Thursday, May 22, 2014 11:45 AM

All replies

  • Just reverse the operands to the join.  Instead of (Students join Faculties), do (Faculties join Students).

    Here's a complete example.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    
    class Student
    {
        public int FID;
    }
    
    class Faculty
    {
        public int FID;
        public string FLast;
    }
    
    class Program
    {
        static void Main( string[] args )
        {
            List<Student> Students = new List<Student>() { 
                new Student() { FID = 101 },
                new Student() { FID = 101 },
                new Student() { FID = 102 },
                new Student() { FID = 102 },
                new Student() { FID = 102 },
                new Student() { FID = 103 },
                new Student() { FID = 103 },
                new Student() { FID = 103 }
            };
            List<Faculty> Faculties = new List<Faculty>() { 
                new Faculty() { FID = 101, FLast = "Math" },
                new Faculty() { FID = 102, FLast = "Arts" },
                new Faculty() { FID = 103, FLast = "Science" }
            };
    
            var query = from f in Faculties
                    join s in Students on f.FID equals s.FID into g
                    orderby g.Count() descending, f.FLast descending
                    select new
                    {
                        Id = f.FID,
                        Name = f.FLast,
                        Total = g.Count()
                    };
    
            Console.WriteLine( "Id\tName\tTotal" );
            foreach( var item in query ) {
                Console.WriteLine( "{0}\t{1}\t{2}", item.Id, item.Name, item.Total );
            }
        }
    }
    

    You can sort this out in your head by thinking like this: if the result is basically a list of faculties, then start the join with faculties; and if you need more information from the students table to get the information for your faculty list, then join with the students list to get your results.

    I found it weird to order by FLast descending (to put the faculty names with equal student counts in reverse alphabetical order), but maybe that's just me -- I did what you did in your example.

    Thursday, May 22, 2014 12:46 PM
  • Moving to more appropriate forum.

    Esther Fan | Visual Studio | If a post answers your question, please mark it as the answer. Thanks!

    Friday, May 23, 2014 5:15 PM