Programmation .Net - Indépendance financière

Requêtes sur les données relationnelles avec Linq/Entity Framework en C#

Une chose intéressante que vous pouvez accomplir avec Entity Framework 6 est de faire des requêtes sur des objets liés par une relation sans avoir besoin d’écrire une seul ligne de code SQL! Le Entity Framework nous rend la tâche facile et intuitive en s’occupant de traduire notre code Linq directement en SQL et nous permet d’accéder aux enfants d’un parent et vice versa sans Jointures. C’est ce que nous allons regarder dans ce post. Si toutefois vous voulez en savoir plus sur les jointures elle-mêmes avec Linq, vous devrez lire cet article.

Dans ce tutoriel nous regarderons deux scénarios soit:

  1. comment accéder aux objets enfants à partir de leur parent;
  2. comment accéder au parent à partir d’un de ses enfants.

1. Mise en place de la base de données

Commençons par la base de données, assurez-vous d’abord d’avoir les deux logiciels suivants d’installés sur votre ordinateur :

Note: pour ce qui est de SQL Server, vous pouvez simplement télécharger la version express. De plus, voici un vidéo YouTube vous expliquant comment effectuer l’installation des deux outils. Pas besoin de suivre la partie de mise en place du réseau, seulement la partie installation suffira.

Lancez SQL Management Studio et cliquez sur File -> New -> Query With Current Connection.

Copiez et collez ensuite le code SQL suivant puis exécutez-le en cliquant sur le bouton Execute dans la barre d’outils du haut. Cette commande créera une base de données qui contient deux tables Stores (magasins) et SoldItems (articles vendus) que nous utiliserons durant ce tutoriel.

CREATE DATABASE [Corporation]
GO
USE [Corporation]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SoldItems](
	[SoldItemID] [int] IDENTITY(1,1) NOT NULL,
	[StoreID] [int] NOT NULL,
	[Number] [int] NOT NULL,
	[Description] [varchar](150) NOT NULL,
	[Price] [money] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_SoldItems] PRIMARY KEY CLUSTERED 
(
	[SoldItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stores](
	[StoreID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Street] [varchar](50) NOT NULL,
	[StreetNumber] [varchar](10) NOT NULL,
	[City] [varchar](50) NOT NULL,
	[Country] [varchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED 
(
	[StoreID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SoldItems] ON 

INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (15, 23, 97843278, N'BlackBerry Priv', 499.9900, 60)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (16, 23, 32999201, N'Motorola RAZR', 1200.0000, 10)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (18, 23, 93002010, N'BlackBerry Leap', 120.0000, 15)
SET IDENTITY_INSERT [dbo].[SoldItems] OFF
SET IDENTITY_INSERT [dbo].[Stores] ON 

INSERT [dbo].[Stores] ([StoreID], [Name], [Street], [StreetNumber], [City], [Country], [IsActive]) VALUES (22, N'Test Store EF', N'front st.', N'78-1', N'Toronto', N'Canada', 1)
INSERT [dbo].[Stores] ([StoreID], [Name], [Street], [StreetNumber], [City], [Country], [IsActive]) VALUES (23, N'Store With Items EF', N'Back st.', N'77-2', N'Toronto', N'Canada', 1)
SET IDENTITY_INSERT [dbo].[Stores] OFF
ALTER TABLE [dbo].[SoldItems]  WITH CHECK ADD  CONSTRAINT [FK_SoldItems_Stores] FOREIGN KEY([StoreID])
REFERENCES [dbo].[Stores] ([StoreID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SoldItems] CHECK CONSTRAINT [FK_SoldItems_Stores]
GO

2. Mise en place d’un projet dans Visual Studio

Lancez Visual Studio Community ou téléchargez puis installez-le si vous ne l’avez pas déjà sur votre ordinateur. Ensuite, créez un nouveau projet de type .Net Framework Console.

Vous pouvez aussi créer une structure de fichiers semblable à celle montrée ci-dessous (DataAccess->DataObjects) pour mieux organiser les namespaces (espaces de noms) dans votre application.

Entity Framework Folder Structure.

Puis, cliquez droit sur votre projet et Add -> New Item puis allez chercher EF 6.x DbContext Generator.

Entity Framework 6.x Create DbContext.

Synchroniser le Entity Framework avec la base de données

Pour s’assurer que la base de données physique et le Entity Framework sont synchronisés, il est primordial de le mettre à jour. L’objectif est que lorsque nous voudrons accéder aux données, Linq To Entities saura comme la base de données est formée et nous donnera les bonnes indications de complétion. Mettez à jour le DbContext que vous venez de créer en double-cliquant sur le fichier .edmx. Vous verrez alors l’écran suivant apparaître.

Entity Framework 6 DbContext Screen

Cliquez droit n’importe où dans la section vide pour ouvrir le menu. Puis, cliquez sur Update Model from Database.

Mise à jour du modèle de données Entity Framework.

Sélectionnez les deux tables de la base de données comme dans l’image ci-dessous. Ce sont ces tables qui seront intégrées au Entity Framework, donc que nous pourrons voir en utilisant l’objet DbContext créé. En gros l’objet porte le nom que vous avez indiqué plus haut à la création du fichier .tt suivit du mot Entities. Donc comme nous avons nommé le fichier Corporation, l’objet s’appellera CorporationEntities et servira à accéder la base de données au travers du Entity Framework.

Entity Framework Select Tables to add.

Pour l’étape suivante, cliquez sur Finish et vous verrez clairement la relation 1 vers * (plusieurs) sur le diagramme. Très utile puisque maintenant nous savons que notre base de données correspond à notre objet DbContext (CorporationEntities). Nous sommes maintenant prêts à accéder aux données!

Newly added tables to the Entity Framework DbContext.

3. Accès aux SoldItems (articles vendus) à partir d’un Store (magasin)

La première façon d’accéder aux données que nous allons regarder est de partir de la table Store et d’aller chercher ses enfants dans la table SoldItems. Logiquement un magasin peut avoir plusieurs articles vendus. Comme j’ai mentionné plus haut c’est effectivement le principe de la relation 1 vers * (plusieurs) que nous avons entre les deux tables.

Program.cs

Voici ce que mon fichier Program.cs à l’air pour le moment, j’y ai ajouté des commentaires pour expliquer chacune des étapes.

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

using CorporationRelationships.DataAccess.DataObjects;

namespace CorporationRelationships
{
    class Program
    {
        static void Main(string[] args)
        {
            // Déclaration d'un nouvel objet de type CorporationEntities pour accéder à la base de données via le Entity Framework.
            CorporationEntities corporationEntities = new CorporationEntities();

            // Selection d'un seul (avec Single()) store ayant l'identifiant 23 dans la table Stores.
            Store selectedStore = corporationEntities.Stores.Single(store => store.StoreID == 23);

            // On peut selectionner les SoldItems enfants du store sans même avoir à faire une jointure. On le converti ensuite en List avec ToList().
            List<SoldItem> soldItems = selectedStore.SoldItems.ToList();

            // Écriture des SoldItems récupérés dans la console avec une boucle foreach.
            foreach (SoldItem soldItem in soldItems)
                Console.WriteLine($"SoldItem associated to Store 23 - SoldItemID: {soldItem.SoldItemID}, Description: {soldItem.Description}.");

            Console.ReadKey();
        }
    }
}

Note: assurez-vous que les namespaces (using) sont bons si vous copiez et collez mon code. C’est très possible que vous ayez à les ajuster si le nom de votre projet est différent ou que vous n’avez pas utilisé la structure de fichiers montrée plus haut.

Remarquez dans le code que la première étape est de créer l’objet CorporationEntities. Il servira à accéder la représentation des tables “en mémoire” par le Entity Framework. De plus, remarquez que les tables sont accessibles à partir de la variable corporationEntities en appelant leur nom comme : corporationEntities.Stores.LaMethodeQueVousVoulezAppeler().

En passant, il serait possible d’accéder à des procédures stockées de la même façon, toutefois nous n’aurions pas à passer par une table, seulement comme ceci : corporationEntities.myStoredProcedureName(firstParameter, secondParameter). Attention, si vous avez vraiment beaucoup de procédures stockées dans votre base de données, ça peut rapidement devenir un enfer et la mise à jour de votre DbContext prendra un temps fou.

Finalement une autre chose intéressante est que l’action de récupérer les données de la base de données n’est pas toujours effectuée tout de suite. En effet, on appellera Lazy Load le fait que rien n’est physiquement chargé en mémoire avant de le demander explicitement. Par exemple, dans le cas de selectedStore.SoldItems.ToList();, les données de la table SoldItems ne sont pas en mémoire avant que nous demandions explicitement de les mettre dans une variable à l’aide de ToList() dans ce cas. Vous remarquerez qu’aucune jointure explicite en Linq n’a été utilisé pour accéder aux objets enfants, contrairement à ce que nous aurions dû faire en SQL. Beaucoup plus facile comme ça!

4. Accéder au Store (magasin) parent à partir d’un de ses enfants

Faisons maintenant l’inverse, nous voulons accéder au Store à partir d’un SoldItem donc d’un enfant pour en récupérer l’information encore une fois sans avoir besoin d’effectuer une jointure.

Program.cs

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

using CorporationRelationships.DataAccess.DataObjects;

namespace CorporationRelationships
{
    class Program
    {
        static void Main(string[] args)
        {
            // Déclaration d'un nouvel objet de type CorporationEntities pour accéder à la base de données via le Entity Framework.
            CorporationEntities corporationEntities = new CorporationEntities();

            // Sélection d'un seul (avec Single()) store ayant l'identifiant 23 dans la table Stores.
            Store selectedStore = corporationEntities.Stores.Single(store => store.StoreID == 23);

            // On peut sélectionner les SoldItems enfants du store sans même avoir à faire une jointure. On le convertit ensuite en List avec ToList().
            List<SoldItem> soldItems = selectedStore.SoldItems.ToList();

            // Écriture des SoldItems récupérés dans la console avec une boucle foreach.
            foreach (SoldItem soldItem in soldItems)
                Console.WriteLine($"SoldItem associated to Store 23 - SoldItemID: {soldItem.SoldItemID}, Description: {soldItem.Description}.");

            // On fait l'inverse donc on récupère le Store (un seul avec Single()) lié au SoldItem ayant l'identifiant 18. Remarquez qu'on selection le solditem.Store plutôt que simplement le solditem.
            Store associatedStore = (from solditem in corporationEntities.SoldItems
                                     where solditem.SoldItemID == 18
                                     select solditem.Store).Single();

            // Écriture de deux propriétés du Store dans la console.
            Console.WriteLine($"Store associated to SoldItem 18: {associatedStore.StoreID}, {associatedStore.Name}.");

            Console.ReadKey();
        }
    }
}

Pour cette partie j’ai ajouté une requête Linq pour récupérer le Store associé au SoldItem portant l’identifiant numéro 18. Cet identifiant est autogénéré par la base de données et unique selon sa structure. Par la suite j’utilise Single() qui s’assurera qu’un seul résultat est retourné par cette requête. Si ce n’est pas le cas, il lancera une exception.

L’explication est que nous savons qu’un seul élément Store peut être associé à un SoldItem à cause du type de relation utilisé soit un article ne peut avoir été vendu que par un seul magasin. C# ne le sait pas nécessairement c’est donc pourquoi nous devons lui indiquer explicitement en utilisant Single().

Il est important de retenir que la requête elle-même retournera un objet IQueryable qui ne sera pas exécuté, encore une fois c’est le Single() qui retournera un élément physiquement présent en mémoire (la variable associatedStore dans ce cas). Ça forcera l’exécution au niveau de la base de données.

Je vous suggère de mettre des points d’arrêts pour vraiment comprendre l’exécution pas-à-pas.

Ça rend notre vie vraiment plus facile!

Le but de ce tutoriel était d’illustrer comment facile et intuitif Linq To Entities peut être même lorsque l’on veut accéder à des données ayant des jointures simples. En effet, il génére tout le code requis pour nous tout en nous permettant garder la plus grande part de notre attention sur un seul langage de programmation (C#) plutôt que sur deux (C# et SQL). Ça économise du temps de déboguage avec un seul IDE (Visual Studio) et réduit la complexité du code.

Il faut tout de même se rappeler que ce n’est qu’une petite partie de tout le pouvoir de Linq To Entities et du Entity Framework. Par exemple vous verrez qu’il est possible de faire encore bien plus lorsque nous ajouterons réellement les jointures!

Bon, si vous étiez uniquement intéressé par la partie C#/Linq alors vous pouvez arrêter maintenant. Par contre si vous êtes curieux, la partie suivante montre comment voir le code SQL généré par les requêtes Linq que nous avons écrites.

Inspection du code SQL généré

Pour la partie plus avancée, regardons les requêtes qui ont été générées par Linq To Entities pour les deux opérations. Pour accomplir cette tâche, il sera nécessaire de revamper notre code un peu. Il faut séparer la requête de l’action pour pouvoir appeler la méthode ToString() sur la requête seulement. Je vais d’abord créer une variable de type IQueryable puis appeler sa méthode ToString() qui me donnera la requête SQL comme telle sous format d’une chaîne de caractères.

// Création de la requête et stockage dans une variable (associateStoreQuery), prête pour être exécutée.
IQueryable<Store> associatedStoreQuery = (from solditem in corporationEntities.SoldItems
                                                     where solditem.SoldItemID == 18
                                                     select solditem.Store);

// Exécution de la requête contenue dans associateStoreQuery.
Store associatedStore = associatedStoreQuery.Single();

Console.WriteLine($"Store associated to SoldItem 18: {associatedStore.StoreID}, {associatedStore.Name}.");

// La méthode ToString() appliquée sur l'objet associateStoreQuery nous retourne la requête qui sera executée générée par Linq To Entities.
string sqlCommandText = associatedStoreQuery.ToString();
Console.WriteLine(sqlCommandText);

Voici maintenant le code complet pour montrer les deux requêtes SQL dans la console.

Program.cs

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

using CorporationRelationships.DataAccess.DataObjects;

namespace CorporationRelationships
{
    class Program
    {
        static void Main(string[] args)
        {
            // Déclaration d'un nouvel objet de type CorporationEntities pour accéder à la base de données via le Entity Framework.
            CorporationEntities corporationEntities = new CorporationEntities();

            // Création de la requête Linq pour sélectionner les éléments SoldItems. Remarquez que store.SoldItems est en fait une ICollection<SoldItem>.
            IQueryable<ICollection<SoldItem>> soldItemsSelectionQuery = from store in corporationEntities.Stores
                                                                        where store.StoreID == 23
                                                                        select store.SoldItems;

            // Exécution de la requête et je convertis la ICollection en List.
            List<SoldItem> soldItems = soldItemsSelectionQuery.Single()
                                                              .ToList();

            // Écriture des SoldItems récupérés dans la console avec une boucle foreach.
            foreach (SoldItem soldItem in soldItems)
                Console.WriteLine($"SoldItem associated to Store 23 - SoldItemID: {soldItem.SoldItemID}, Description: {soldItem.Description}.");

            // Création de la requête et stockage dans une variable (associateStoreQuery), prête pour être exécutée.
            IQueryable<Store> associatedStoreQuery = (from solditem in corporationEntities.SoldItems
                                                      where solditem.SoldItemID == 18
                                                      select solditem.Store);

            // Execution de la requête contenue dans associateStoreQuery.
            Store associatedStore = associatedStoreQuery.Single();

            Console.WriteLine($"Store associated to SoldItem 18: {associatedStore.StoreID}, {associatedStore.Name}.");

            // La méthode ToString() appliquée sur l'objet associateStoreQuery nous retourne la requête qui sera exécutée générée par Linq To Entities.
            Console.WriteLine("--First Query Text--");
            string sqlCommandTextFirstQuery = soldItemsSelectionQuery.ToString();
            Console.WriteLine(sqlCommandTextFirstQuery);

            Console.WriteLine("--Second Query Text--");
            string sqlCommandTextSecondQuery = associatedStoreQuery.ToString();
            Console.WriteLine(sqlCommandTextSecondQuery);

            Console.ReadKey();
        }
    }
}

Output

Linq To Entities With Query Displayed in Console.

Comme vous pouvez le constater, Linq To Entities utilise lui-même des jointures en arrière-plan. Toutefois vous n’avez pas à vous soucier de les faire vous-même, vous n’avez qu’à appeler par exemple la propriété SoldItems de la variable associateStore pour avoir accès à ses enfants. En terminant, si vous voulez plus de contrôle sur quel type de jointure sera utilisée par Linq To Entities, alors vous devrez utiliser le mot-clé join que nous verrons dans un prochain article!

Bon code!

Vous avez trouvé un bogue dans mon code? C’est possible, veuillez m’envoyer un message en utilisant mon formulaire de contact pour que je puisse remédier à la situation aussi rapidement que possible!

YouTube

Next article Utiliser le Join avec LINQ en C#
Previous article Investir dans les fonds négociés en bourse avec un faible taux d'économies

Related posts

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Comment

Your data will be safe! Your e-mail address will not be published. Also other data will not be shared with third person. Required fields marked as *