using System;
using System.Collections.Generic;
using System.Linq;
namespace Console__LINQ_QueriesOnListCollection
{
class Student
{
public string Name { get; set; }
public int Age { get; set; }
public string Gender { get; set; }
public string City { get; set; }
public int Scores { get; set; }
public List<Student> GetStudents()
{
List<Student> _students = new List<Student>()
{
new Student() { Name = "Ajeet", Age = 27, Gender = "Male", City = "Agra", Scores = 50},
new Student() { Name = "Mohan", Age = 23, Gender = "Male", City = "Delhi", Scores = 80 },
new Student() { Name = "Rina", Age = 24, Gender = "Female", City = "Delhi", Scores = 70},
new Student() { Name = "Mira", Age = 33, Gender = "Female", City = "Agra", Scores = 60 },
new Student() { Name = "Sita", Age = 33, Gender = "Female", City = "Agra", Scores = 50 },
new Student() { Name = "Hari", Age = 27, Gender = "Male", City = "Delhi", Scores = 40 },
new Student() { Name = "Ranjeet", Age = 37, Gender = "Male", City = "Goa", Scores = 50},
new Student() { Name = "Ravi", Age = 23, Gender = "Male", City = "Delhi", Scores = 30 },
new Student() { Name = "Roshan", Age = 24, Gender = "Male", City = "Delhi", Scores = 50},
new Student() { Name = "Bhavna", Age = 33, Gender = "Female", City = "Goa", Scores = 55 },
new Student() { Name = "Mina", Age = 28, Gender = "Female", City = "Goa", Scores = 45 },
new Student() { Name = "Ajay", Age = 27, Gender = "Male", City = "Delhi", Scores = 70 },
};
return _students;
}
}
class Program
{
static void Main(string[] args)
{
Student s = new Student();
List<Student> students = s.GetStudents();
var query1 = students.Where(s => s.City == "Delhi" && s.Gender == "Male");
Console.WriteLine("\nResult of query1\n");
foreach (var item in query1)
{
Console.WriteLine(item.Name + " " + item.Age);
}
var query2 = students.Where(s => s.City == "Delhi" && s.Gender == "Male").FirstOrDefault();
Console.WriteLine("\nResult of query2\n");
Console.WriteLine(query2.Name+ " "+ query2.Age);
//find all names that starts with letter R
Console.WriteLine("\nResult of query3\n");
var query3 = students.Where(s => s.Name[0] == 'R').ToList();
foreach (var item in query3)
{
Console.WriteLine(item.Name);
}
//find all names that starts with letter R, SQL syntax
Console.WriteLine("\nResult of query4\n");
var query4 = from student in students
where student.Name.StartsWith('R')
select student;
foreach (var item in query4)
{
Console.WriteLine(item.Name);
}
//find average age of students
Console.WriteLine(students.Average(s => s.Age).ToString());
//find sum of all scores
Console.WriteLine(students.Sum(s=>s.Scores).ToString());
//find average age of male students, first filter
Console.WriteLine(students.Where(s =>s.Gender == "Male").Average(s=>s.Age));
//display records of students who live in Delhi or Agra and are female
var query6 = students.Where(s => (s.City == "Delhi" || s.City == "Agra") && s.Gender=="Female");
Console.WriteLine("\nResult of query6\n");
foreach (var std in query6)
{
Console.WriteLine("{0}, {1}, {2}, {3} ", std.Name, std.Age, std.Gender, std.City);
}
var query7 = students.Where(s => (s.City == "Delhi" || s.City == "Agra") && s.Gender == "Female").Average(s => s.Age);
Console.WriteLine("\nResult of query7 : Where() Average()\n");
Console.WriteLine("Average Age of female students living in Agra or Delhi: " + query7);
//find total number of distinct cities
Console.WriteLine("Distinct cities #:"+students.GroupBy(s => s.City).Count());
//sort the students cities in ascending order
Console.WriteLine("\nResult of query8: OrderBy\n");
var query8 = students.OrderBy(s => s.City);
foreach (var std in query8)
{
Console.WriteLine("{0} {1} ", std.City, std.Name);
}
//sort city and then name
Console.WriteLine("\nResult of query9: OrderBy ThenBy\n");
var query9 = students.OrderBy(s => s.City).ThenBy(s => s.Name);
foreach (var q9 in query9)
{
Console.WriteLine("{0} {1} ",q9.City, q9.Name);
}
var queryCity = students.GroupBy(s => s.City);
/* The datatype of queryCity is IEnumerable<IGrouping<string, Student>> Place the cursor at queryCity to get this fact. Here, string is all about the data type of the city column used in grouping. Remember that in LINQ datatype is strictly type */
Console.WriteLine("\nGROUP STUDENTS BY CITY");
foreach (var gcity in queryCity)
{
Console.WriteLine(gcity.Key +" Group:->");
foreach (var stt in gcity)
{
Console.WriteLine("{0} {1} {2} {3} {4}",stt.Name, stt.Age, stt.Gender,stt.City,stt.Scores);
}
}
Console.WriteLine("\nGROUP STUDENTS BY CITY AND GENDER");
// Note the + sign used to concatenate multilevel grouping based on Gender and City
var qGenderCity = students.GroupBy(s => s.Gender+ s.City);
foreach (var gcityGender in qGenderCity)
{
Console.WriteLine(gcityGender.Key);
foreach (var stt in gcityGender)
{
Console.WriteLine("{0} {1} {2} {3} {4}", stt.Name, stt.Age, stt.Gender, stt.City, stt.Scores);
}
Console.WriteLine();
}
Console.WriteLine("NESTED QUERY:");
var queryNested = from student in students
group student by student.City
into newStd
from grps in newStd
group grps by grps.Gender;
foreach (var gcg in queryNested)
{
Console.WriteLine(gcg.Key);
foreach (var g in gcg)
{
Console.WriteLine("{0} {1} {2} {3}", g.City, g.Gender, g.Name,g.Age);
}
}
Console.WriteLine("MAXIMUM AVERAGE SCORE OF EACH GROUP/CITY");
var qGrpAvg = from sdn in students
group sdn by sdn.City into gsCity
select new
{
HighestAvg = Math.Round(((from scoregcity in gsCity
select scoregcity.Scores).Average()),2)
};
foreach (var item in qGrpAvg)
{
Console.WriteLine(item);
}
var qmax = (from q in qGrpAvg
select q.HighestAvg).Max();
Console.WriteLine("Maximum average score: {0}",qmax);
Console.ReadKey();
}
}
}
OUTPUT
Result of query1
Mohan 23
Hari 27
Ravi 23
Roshan 24
Ajay 27
Result of query2
Mohan 23
Result of query3
Rina
Ranjeet
Ravi
Roshan
Result of query4
Rina
Ranjeet
Ravi
Roshan
28.25
650
26.857142857142858
Result of query6
Rina, 24, Female, Delhi
Mira, 33, Female, Agra
Sita, 33, Female, Agra
Result of query7 : Where() Average()
Average Age of female students living in Agra or Delhi: 30
Distinct cities #:3
Result of query8: OrderBy
Agra Ajeet
Agra Mira
Agra Sita
Delhi Mohan
Delhi Rina
Delhi Hari
Delhi Ravi
Delhi Roshan
Delhi Ajay
Goa Ranjeet
Goa Bhavna
Goa Mina
Result of query9: OrderBy ThenBy
Agra Ajeet
Agra Mira
Agra Sita
Delhi Ajay
Delhi Hari
Delhi Mohan
Delhi Ravi
Delhi Rina
Delhi Roshan
Goa Bhavna
Goa Mina
Goa Ranjeet
GROUP STUDENTS BY CITY
Agra Group:->
Ajeet 27 Male Agra 50
Mira 33 Female Agra 60
Sita 33 Female Agra 50
Delhi Group:->
Mohan 23 Male Delhi 80
Rina 24 Female Delhi 70
Hari 27 Male Delhi 40
Ravi 23 Male Delhi 30
Roshan 24 Male Delhi 50
Ajay 27 Male Delhi 70
Goa Group:->
Ranjeet 37 Male Goa 50
Bhavna 33 Female Goa 55
Mina 28 Female Goa 45
GROUP STUDENTS BY CITY AND GENDER
MaleAgra
Ajeet 27 Male Agra 50
MaleDelhi
Mohan 23 Male Delhi 80
Hari 27 Male Delhi 40
Ravi 23 Male Delhi 30
Roshan 24 Male Delhi 50
Ajay 27 Male Delhi 70
FemaleDelhi
Rina 24 Female Delhi 70
FemaleAgra
Mira 33 Female Agra 60
Sita 33 Female Agra 50
MaleGoa
Ranjeet 37 Male Goa 50
FemaleGoa
Bhavna 33 Female Goa 55
Mina 28 Female Goa 45
NESTED QUERY:
Male
Agra Male Ajeet 27
Delhi Male Mohan 23
Delhi Male Hari 27
Delhi Male Ravi 23
Delhi Male Roshan 24
Delhi Male Ajay 27
Goa Male Ranjeet 37
Female
Agra Female Mira 33
Agra Female Sita 33
Delhi Female Rina 24
Goa Female Bhavna 33
Goa Female Mina 28
MAXIMUM AVERAGE SCORE OF EACH GROUP/CITY
{ HighestAvg = 53.33 }
{ HighestAvg = 56.67 }
{ HighestAvg = 50 }
Maximum average score: 56.67
No comments:
Post a Comment