static void Main(string[] args) { DataContext LinqDataContext = new DataContext(); #region Partie 1 #region Exo 1.1 //var qResult = LinqDataContext.Students.Select(s => new //{ // nom = string.Format("{0} {1}", s.First_Name, s.Last_Name), // ddn = s.BirthDate, // login = s.Login, // yrResult = s.Year_Result //}); //foreach (var s in qResult) //{ // Console.WriteLine(s.nom + " | " + s.ddn + " | " + s.login + " | " + s.yrResult); //} #endregion #region Exo 1.2 //var qResult = LinqDataContext.Students.Select(s => new //{ // nom = string.Format("{0} {1}", s.First_Name, s.Last_Name), // id = s.Student_ID, // ddn = s.BirthDate //}); //foreach (var s in qResult) //{ // Console.WriteLine(string.Format("{0} | {1} | {2}", s.nom, s.id, s.ddn)); //} #endregion #region Exo 1.3 //var qResult = LinqDataContext.Students.Select(s => new //{ // info = string.Format("{0} | {1} | {2} | {3} | {4} | {5} | {6} | {7}", // s.Student_ID, // s.First_Name, // s.Last_Name, // s.Login, // s.BirthDate, // s.Course_ID, // s.Section_ID, // s.Year_Result // ) //}); //foreach (var s in qResult) //{ // Console.WriteLine(s.info); //} #endregion #endregion #region Partie 2 #region Exo 2.1 //var qResult = LinqDataContext.Students.Where(s => s.BirthDate.Year < 1955) // .Select(s => new // { // nom = string.Format("{0} {1}", s.First_Name, s.Last_Name), // yrResult = s.Year_Result, // status = studentStatus(s.Year_Result) // }); //foreach (var s in qResult) //{ // Console.WriteLine(string.Format("{0} | {1} | {2}", s.nom, s.yrResult, s.status)); //} #endregion #region Exo 2.2 //var qResult = LinqDataContext.Students.Where(s => s.BirthDate.Year >= 1955 && s.BirthDate.Year <= 1965) // .Select(s => new // { // nom = string.Format("{0} {1}", s.First_Name, s.Last_Name), // yrResult = s.Year_Result, // cat = studentCat(s.Year_Result) // }); //foreach (var s in qResult) //{ // Console.WriteLine(string.Format("{0} | {1} | {2} | {3}", s.nom, s.yrResult, s.cat)); //} #endregion #region Exo 2.3 //var qResult = LinqDataContext.Students.Where(s => checkLastLetter(s.Last_Name, 'r')) // .Select(s => new // { // nom = s.Last_Name, // section = s.Section_ID // }); //foreach (var s in qResult) //{ // Console.WriteLine(s.nom + " | " + s.section); //} #endregion #region Exo 2.4 //var qResult = LinqDataContext.Students.Where(s => s.Year_Result <= 3) // .Select(s => new // { // nom = string.Format("{0} {1}", s.First_Name, s.Last_Name), // result = s.Year_Result // }) // .OrderByDescending(s => s.result); //foreach (var s in qResult) //{ // Console.WriteLine(s.nom + " | " + s.result); //} #endregion #region Exo 2.5 //var qResult = LinqDataContext.Students // .Where(s => s.Section_ID == 1110) // .Select(s => new // { // nom = string.Format("{0} {1}", s.Last_Name, s.First_Name), // yrResult = s.Year_Result // }) // .OrderBy(s => s.nom); //foreach (var s in qResult) // Console.WriteLine(s.nom + " | " + s.yrResult); #endregion #region Exo 2.6 //var qResult = LinqDataContext.Students // .Where(s => (s.Section_ID == 1010 || s.Section_ID == 1020) && (s.Year_Result < 12 || s.Year_Result > 18)) // .Select(s => new // { // nom = s.Last_Name, // section = s.Section_ID, // yrResult = s.Year_Result // }) // .OrderBy(s => s.yrResult); //foreach (var s in qResult) // Console.WriteLine(string.Format("{0} | {1} | {2}", s.nom, s.section, s.yrResult)); #endregion #region Exo 2.7 //var qResult = LinqDataContext.Students // .Where(s => (s.Section_ID >= 1300 && s.Section_ID < 1400) && resultOn100(s.Year_Result) <= 60) // .Select(s => new // { // nom = s.Last_Name, // section = s.Section_ID, // result_100 = resultOn100(s.Year_Result) // }) // .OrderByDescending(s => s.result_100); //foreach (var s in qResult) // Console.WriteLine(string.Format("{0} | {1} | {2}", s.nom, s.section, s.result_100)); #endregion #endregion #region Partie 3 #region Exo 3.1 //int qResult = (int)(LinqDataContext.Students // .Select(s => s.Year_Result).ToList().Average()); //Console.WriteLine(qResult); #endregion #region Exo 3.2 //int qResult = LinqDataContext.Students.Select(s => s.Year_Result).Max(); //Console.WriteLine(qResult); #endregion #region Exo 3.3 //Console.WriteLine(LinqDataContext.Students.Select(s => s.Year_Result).Sum()); #endregion #region Exo 3.4 //Console.WriteLine(LinqDataContext.Students.Select(s => s.Year_Result).Min()); #endregion #region Exo 3.5 //Console.WriteLine(LinqDataContext.Students.Where(s => s.Year_Result % 2 == 1).Count()); #endregion #endregion #region Partie 4 #region Exo 4.1 //var qResult = LinqDataContext.Students.GroupBy(s => s.Section_ID); //foreach (var section in qResult) //{ // Console.WriteLine("Résultat maximum pour la section {0} : {1}", section.Key, section.Select(s => s.Year_Result).Max()); //} #endregion #region Exo 4.2 //var qResult = LinqDataContext.Students.Where(s => s.Section_ID >= 1000 && s.Section_ID < 1100).GroupBy(s => s.Section_ID); //foreach (var section in qResult) //{ // Console.WriteLine(string.Format("Moyenne pour la section {0} : {1}", section.Key, Math.Round(section.Select(s => s.Year_Result).Average(), 2))); //} #endregion #region Exo 4.3 //var qResult = LinqDataContext.Students // .Where(s => s.BirthDate.Year >= 1970 && s.BirthDate.Year <= 1985) // .GroupBy(s => s.BirthDate.MonthName_FR()); //foreach (var students in qResult) // Console.WriteLine(string.Format("{0} : {1}", Math.Round(students.Select(s => s.Year_Result).Average(), 2), students.Key)); #endregion #region Exo 4.4 //var qResult = LinqDataContext.Students.GroupBy(s => s.Section_ID) // .Where(g => g.Count() > 3) // .Select(g => Math.Round(g.Average(s => s.Year_Result), 2)); //foreach (double avg in qResult) // Console.WriteLine(avg); //// Ou encore mieux... //var qResultAlt = LinqDataContext.Students.GroupBy(s => s.Section_ID).Where(g => g.Count() > 3); //foreach (var section in qResultAlt) // Console.WriteLine(string.Format("{0} : {1}", section.Key, Math.Round(section.Select(s => s.Year_Result).Average(), 2))); #endregion #region Exo 4.5 //var qResult = LinqDataContext.Courses // .Join(LinqDataContext.Professors, // c => c.Professor_ID, // p => p.Professor_ID, // (c, p) => new // { // cours = c.Course_Name, // prof = p.Professor_Name, // section = p.Section_ID // }); //foreach (var cp in qResult) // Console.WriteLine(string.Format("{0} | {1} | {2}", cp.cours, cp.prof, cp.section)); #endregion #region Exo 4.6 //var qResult = LinqDataContext.Sections // .Join(LinqDataContext.Students, // scn => scn.Delegate_ID, // std => std.Student_ID, // (scn, std) => new // { // secId = scn.Section_ID, // secName = scn.Section_Name, // delName = std.First_Name + " " + std.Last_Name // }).OrderByDescending(scn => scn.secId); //foreach (var sd in qResult) // Console.WriteLine(string.Format("{0} | {1} | {2}", sd.secId, sd.secName, sd.delName)); #endregion #region Exo 4.7 //var qResult = LinqDataContext.Sections // .GroupJoin(LinqDataContext.Professors, // s => s.Section_ID, // p => p.Section_ID, // (s, p) => new // { // secId = s.Section_ID, // secName = s.Section_Name, // prof = p // }); //foreach (var section in qResult) //{ // Console.WriteLine("{0} - {1}", section.secId, section.secName); // if (section.prof.Count() > 0) // { // foreach (Professor p in section.prof) // Console.WriteLine(p.Professor_Name); // } // else // Console.WriteLine("Aucun"); // Console.WriteLine(); //} #endregion #region Exo 4.8 //var qResult = LinqDataContext.Sections // .GroupJoin(LinqDataContext.Professors, // s => s.Section_ID, // p => p.Section_ID, // (s, p) => new // { // secId = s.Section_ID, // secName = s.Section_Name, // prof = p // }).Where(g => g.prof.Count() > 0); //foreach (var section in qResult) //{ // Console.WriteLine("{0} - {1}", section.secId, section.secName); // foreach (Professor p in section.prof) // Console.WriteLine(p.Professor_Name); // Console.WriteLine(); //} #endregion #region Exo 4.9 //var qResult = from s in LinqDataContext.Students // from g in LinqDataContext.Grades // where (s.Year_Result >= g.Lower_Bound && s.Year_Result <= g.Upper_Bound) && s.Year_Result >= 12 // orderby (g.GradeName) // select new // { // name = s.First_Name + " " + s.Last_Name, // result = s.Year_Result, // grade = g.GradeName // }; //foreach (var sg in qResult) // Console.WriteLine("{0} | {1} | {2}", sg.name, sg.result, sg.grade); #endregion #region Exo 4.10 var qResult = from s in LinqDataContext.Sections from p in LinqDataContext.Professors join c in LinqDataContext.Courses on p.Professor_ID equals c.Professor_ID into gcp from cp in gcp.DefaultIfEmpty() select new { prof = p.Professor_Name, section = s.Section_Name, cours = (cp == null) ? null : cp.Course_Name, creds = (cp == null) ? null : (float?)cp.Course_Ects }; var qFinalResult = from row in qResult orderby row.creds descending select new { row.prof, row.section, row.cours, row.creds }; foreach (var psc in qFinalResult) Console.WriteLine("{0} | {1} | {2} |{3}", psc.prof, psc.section, psc.cours, psc.creds); #endregion #endregion Console.ReadKey(); }
static void Main(string[] args) { DataContext dc = new DataContext(); #region EXERCICE 2.1 //var query = from c in dc.Students // select new { nom = c.Last_Name, dateNaissance = c.BirthDate, login = c.Login, resultat = c.Year_Result }; //var query = dc.Students.Select(st => new { nom = st.Last_Name, dateNaissance = st.BirthDate, login = st.Login, resultat = st.Year_Result }); //foreach (var st in query) //{ // Console.WriteLine("{0} - {1} - {2} - {3}", st.nom, st.dateNaissance, st.login, st.resultat); //} #endregion #region EXERCICE 2.2 //var query = dc.Students.Select(st => new //{ // nomComplet = string.Format("{0} {1}", st.First_Name, st.Last_Name), // id = st.Student_ID, // dateNaissance = st.BirthDate //}); //foreach (var st in query) //{ // Console.WriteLine("{0} {1} {2}", st.id, st.nomComplet, st.dateNaissance); //} #endregion #region EXERCICE 2.3 //var query = dc.Students.Select(st => new //{ // info = string.Join(" | ", st.Student_ID, st.First_Name, st.Last_Name, st.Login, st.BirthDate.ToShortDateString(), st.Year_Result) //}); //foreach (var st in query) //{ // Console.WriteLine(st.info); //} #endregion #region EXERCICE 3.1 //var query = dc.Students // .Where(st => st.BirthDate.Year < 1955) // .Select(st => new // { // nom = st.Last_Name, // year_result = st.Year_Result, // statut = (st.Year_Result >= 12) ? "OK" : "KO" // }); //foreach (var st in query) //{ // Console.WriteLine("{0} {1} {2}", st.nom, st.statut, st.year_result); //} #endregion #region EXERCICE 3.2 //var query = dc.Students // .Where(st => st.BirthDate.Year >= 1955 && st.BirthDate.Year <= 1965) // .Select(st => new // { // nom = st.Last_Name, // year_result = st.Year_Result, // categorie = (st.Year_Result < 10) ? "Inférieure" : // ((st.Year_Result == 10) ? "Neutre" : "Supérieure") // }); //foreach (var st in query) //{ // Console.WriteLine("{0} {1} {2}", st.nom, st.year_result, st.categorie); //} #endregion #region EXERCICE 3.3 //var query = dc.Students // .Where(st => st.Last_Name.Substring(st.Last_Name.Length-1, 1) == "r") // .Select(st => new // { // last_name = st.Last_Name, // section_id = st.Section_ID // }); //foreach (var st in query) //{ // Console.WriteLine("{0} {1}", st.last_name, st.section_id); //} #endregion #region EXERCICE 3.4 //var query = dc.Students // .Where(st => st.Year_Result <= 3) // .Select(st => new // { // st.Last_Name, // st.Year_Result // }).OrderByDescending(st => st.Year_Result); //foreach (var st in query) //{ // Console.WriteLine("{0} {1}", st.Last_Name, st.Year_Result); //} #endregion #region EXERCICE 3.5 //var query = dc.Students. // Where(st => st.Section_ID == 1110) // .Select(st => new // { // nomComplet = string.Format("{0} {1}", st.Last_Name, st.First_Name), // st.Year_Result // }) // .OrderBy(st => st.nomComplet); //foreach (var st in query) //{ // Console.WriteLine("{0} {1}", st.nomComplet, st.Year_Result); //} #endregion #region EXERCICE 3.6 //var query = dc.Students // .Where(st => st.Year_Result > 18 || st.Year_Result < 12) // .Where(st => st.Section_ID == 1010 || st.Section_ID == 1020) // .Select(st => new // { // st.Last_Name, // st.Section_ID, // st.Year_Result // }) // .OrderBy(st => st.Section_ID); //foreach (var st in query) //{ // Console.WriteLine("{0} {1} {2}", st.Last_Name, st.Section_ID, st.Year_Result); //} #endregion #region EXERCICE 3.7 //var query = dc.Students // .Where(st => st.Year_Result * 5 <= 60 && st.Section_ID / 100 == 13) // .Select(st => new // { // st.Last_Name, // st.Section_ID, // result_100 = st.Year_Result * 5 // }) // .OrderByDescending(st => st.result_100); //foreach (var st in query) //{ // Console.WriteLine("{0} {1} {2}", st.Last_Name, st.Section_ID, st.result_100); //} #endregion #region EXERCICE 4.1 //Console.WriteLine(dc.Students.Average(st => st.Year_Result)); #endregion #region EXERCICE 4.2 //Console.WriteLine(dc.Students.Max(st => st.Year_Result)); #endregion #region EXERCICE 4.3 //Console.WriteLine(dc.Students.Sum(st => st.Year_Result)); #endregion #region EXERCICE 4.4 //Console.WriteLine(dc.Students.Min(st => st.Year_Result)); #endregion #region EXERCICE 4.5 //Console.WriteLine(dc.Students.Where(st => st.Year_Result % 2 != 0).Count()); #endregion #region EXERCICE 5.1 //IEnumerable<IGrouping<int, Student>> query = dc.Students // .GroupBy(st => st.Section_ID); //foreach (IGrouping<int, Student> group in query) //{ // Console.WriteLine("Section : {0} - Résultat max : {1}", group.Key, group.Max(st => st.Year_Result)); //} #endregion #region EXERCICE 5.2 #endregion #region Console.ReadLine() Console.ReadLine(); #endregion }