Introduction à LINQ To SQL par l'exemple

L'article qui suit a pour but la manipulation des données en base avec LINQ To SQL.

Nous allons présenter une série de diverses opérations CRUD (Create Read Update Delete)
appliquées sur notre base de données ECOLE.

1. Opérations de lecture :

Dans cette partie, on répondra à chaque question de la manière suivante, dans l'ordre :

• Requête SQL
• "Expression query" de LINQ
• Opérateurs LINQ (méthodes d'extention) de la classe Queryable, et expressions lambda : "Lambda query"

Q1. Obtenir la liste des noms, prénoms et date de naissance de tous les élèves.

SELECT nom, prenom, date_naissance  
FROM ELEVES

var listEleves = from el in dc.ELEVEs

                 select new { el.NOM, el.PRENOM, el.DATE_NAISSANCE };

var listEleves = dc.ELEVEs.Select(el => new { el.NOM, el.PRENOM, el.DATE_NAISSANCE });

Q2. Obtenir tous les renseignements sur toutes les activités.

SELECT * FROM ACTIVITES

var listActivites = from a in dc.ACTIVITEs

                    select a;

var listActivites = dc.ACTIVITEs.Select(a => a);

Q3. Obtenir la liste des spécialités des professeurs.

SELECT
DISTINCT specialite
FROM PROFESSEURS
WHERE specialite IS NOT NULL

var listSpec = from p in dc.PROFESSEURs

               where p.SPECIALITE != null

               select new { p.SPECIALITE };

var listSpec = dc.PROFESSEURs

               .Where(p => p.SPECIALITE != null)

               .Select(p => p.SPECIALITE)

               .Distinct();

Q4. Obtenir le nom et le prénom des élèves pesant moins de 45 kilos et inscrits en 1ére année ou des élèves inscrits en 2ème année.

SELECT nom, prenom
FROM ELEVES
WHERE (poids < 45 AND annee =" 1) OR annee = 2

var listEleves = from el in dc.ELEVEs

                 where ((el.POIDS < 45 && el.ANNEE == 1) || el.ANNEE == 2)

                 select new { el.NOM, el.PRENOM };

var listEleves = dc.ELEVEs

                 .Where(el => ((el.POIDS < 45 && el.ANNEE == 1) || el.ANNEE == 2))

                 .Select(el => new { el.NOM, el.PRENOM } );

Q5. Obtenir le nom des professeurs dont la spécialité est 'poésie' ou 'sql'.

SELECT nom
FROM PROFESSEURS
WHERE specialite IN ('poésie', 'sql')

var listProfesseurs = from p in dc.PROFESSEURs

                      where (p.SPECIALITE.Equals("poésie") || p.SPECIALITE.Equals("sql"))

                      select p.NOM;

var listProfesseurs = dc.PROFESSEURs

                      .Where(p => (p.SPECIALITE.Equals("poésie")

                                    || p.SPECIALITE.Equals("sql")))

                      .Select(p => p.NOM);

Q6. Obtenir le nom des élèves dont le nom commence par 'L'.

SELECT nom
FROM ELEVES
WHERE nom LIKE'L%'

var listEleves = from el in dc.ELEVEs

                 where el.NOM.StartsWith("L")

                 select el.NOM;

var listEleves = dc.ELEVEs.Where(el => el.NOM.StartsWith("L")).Select(el => el.NOM);

Q7. Obtenir pour chaque professeur, son nom et sa spécialité.

SELECT nom, ISNULL(specialite, '****') AS specialite
FROM professeurs

var listSpec = from p in dc.PROFESSEURs

               select new { specialite = (p.SPECIALITE == null)?"****":p.SPECIALITE };

var listSpec = dc.PROFESSEURs.Select(p => (p.SPECIALITE == null)?"****":p.SPECIALITE);

Q8. Obtenir le nom et prenom d'eleves et leur résultats par cours.

SELECT nom, ISNULL(specialite, '****') AS specialite
FROM professeurs

var listResults = from el in dc.ELEVEs

                  join r in dc.RESULTATs on el.NUM_ELEVE equals r.NUM_ELEVE

                  join c in dc.COURs on r.NUM_COURS equals c.NUM_COURS

                  select new { el.NOM, el.PRENOM, nomCour = c.NOM, r.POINTS };

var listResults = dc.ELEVEs.Join(dc.RESULTATs, el => el.NUM_ELEVE, r => r.NUM_ELEVE,

                                (el, r) => new { el.NOM, el.PRENOM, r.POINTS, r.NUM_COURS })

                           .Join(dc.COURs, tmpVar => tmpVar.NUM_COURS, c => c.NUM_COURS,

                                (tmpVar, c) => new { tmpVar.NOM, tmpVar.PRENOM, nom_cours = c.NOM, tmpVar.POINTS });

Q9. Obtenir le nombre de professeurs par specialité. Ordonnez par ordre alphabétique des spécialités.

SELECT specialite, nb_professeurs = count(*)
FROM PROFESSEURS
GROUP BY specialite

var listResult = from p in dc.PROFESSEURs

                 where p.SPECIALITE != null

                 group p by p.SPECIALITE into g

                 orderby g.Key ascending

                 select new { g.Key, nb_professeurs = g.Count() };

var listResult = dc.PROFESSEURs

                 .Where(p => p.SPECIALITE != null)

                 .GroupBy(p => p.SPECIALITE)

                 .OrderBy(g => g.Key)

                 .Select( g => new { g.Key, nb_professeurs = g.Count() });

2. Opérations de création, mise à jour et de suppression :

Q1. Créer et ajouter un nouveau professeur .NET nommé "Antonio", arrivé aujourd'hui. Son salaire de base et actuel sera de 2500000.

// création du nouveau professeur

PROFESSEUR newProf = new PROFESSEUR { NUM_PROF = 9,

                                      NOM = "Antonio",

                                      SPECIALITE = ".NET",

                                      DATE_ENTREE = DateTime.Now,

                                      SALAIRE_BASE = 2500000,

                                      SALAIRE_ACTUEL = 2500000 };

dc.PROFESSEURs.InsertOnSubmit(newProf); // ajout et prise en compte dans le DataContext

dc.SubmitChanges(); // sauvegarde effective en base de donnée

Q2. Depuis son arrivée, Antonio a eu une augmentation de 10% par rapport à son salaire actuel. Mettre à jour ces informations.

try

{

    var prof = dc.PROFESSEURs.Single(p => p.NOM == "Antonio" && p.SPECIALITE == ".NET");

    prof.SALAIRE_ACTUEL += prof.SALAIRE_ACTUEL * 0.1;

    dc.SubmitChanges();

}

catch(Exception ex){

    // Exception : Pas de professeurs .NET appelé Antonio !

}

Q3. Antonio enseigne la programmation. Ajoutez cette information dans la table CHARGE.

// récupère le professeur Antonio

var leProf = dc.PROFESSEURs.Single(p => p.NOM == "Antonio");

// récupère le cours de programmation

var leCours = dc.COURs.Single(c => c.NOM == "Programmation");

// crée une nouvealle charge

CHARGE uneCharge = new CHARGE { PROFESSEUR = leProf, COUR = leCours };

// Ajoute et sauvegarde en base la nouvelle charge

dc.CHARGEs.InsertOnSubmit(uneCharge);

dc.SubmitChanges();

Q4. Notre bon vieil ami Antonio n'enseigne plus et part finir ses jours à la retraite au soleil... Supprimer toutes les informations existantes sur Antonio.

/* Suppression des informations d'Antonio */

// récupère le professeur Antonio

var prof = dc.PROFESSEURs.Single(p => p.NOM.Equals("Antonio"));

// Prise en compte de la suppression dans le DataContext

dc.PROFESSEURs.DeleteOnSubmit(prof);

// Récupère la liste des charges d'Antonio

var listeCharges = dc.CHARGEs

                   .Where(c => c.PROFESSEUR.Equals(prof))

                   .Select(c => c);

// Prise en compte de la suppression des charges dans le DataContext

dc.CHARGEs.DeleteAllOnSubmit(listeCharges);

// Validation et Suppression effective en base de donnée :

dc.SubmitChanges();