Cette page s'intéresse particulièrement au LINQ to SQL (LTS) qui interagit avec une système de gestion de bases de données SQL Server. Les exemples utilisent la base de données AdventureWorks¹⁾.

Pour créer une simple application utilisant LINQ to SQL, voici les étapes :

1. Créer une application (console, Windows Form, WPF ou ASP.NET) avec Visual Studio 2008.
2. Afficher l'Explorateur de serveurs et ajouter une connexion à la base de données (ie: AdventureWorks).
3. Ajouter un nouvel élément au projet : Classes LINQ to SQL. Donner un nom approprié (ie : AdventureWorks.dbml).
4. Eclater la liste des tables de la base de données et glisser-déposer une table dans le fichier DBML (ie : Department dans AdventureWorks.dbml).
   • Pour les exemples avec AdventureWorks, insérer également les tables Employee, Contact et EmployeeDepartmentHistory.
   • Idéalement toutes les clés étrangères ont été créés dans la BD, ce qui crée automatiquement les associations. Sinon, il est possible de créer les associations dans le designer DBML, mais il peut y avoir des problèmes d'intégrité, de requêtes, etc.
5. Ajouter, par glisser-déposer, les procédures stockées (sproc) nécessaires au panneau de méthodes du designer DBML.
   1. Ajouter les sprocs directement sur l'entité si elle est spécifique à celle-ci.

Source : LINQ: Query Comprehension Syntax

Les exemples de code suivants ont besoin de la déclaration de db pour bien fonctionner. Il s'agit en fait de créer l'objet DataContext que nous utiliseront. Le DataContext est l'interface entre l'application et la base de données.

AdventureWorksDataContext db = new AdventureWorksDataContext();

Autre exemple qu'il est possible d'utiliser, mais non testé :

AdventureWorksDataContext db = new AdventureWorksDataContext (@"Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks");

Aussi, il est préférable d'entourer les opérations LINQ avec un try catch.

try
{
    // code LINQ...
}
catch (Exception ex)
{
    throw ex;
}

Les requêtes pour retrouver les données sont simples. Il peut y avoir des questions de performances et d'optimisation, mais il n'en sera pas question ici.

var p =  (from em in db.Employees
          join edh in db.EmployeeDepartmentHistories
          on em.HireDate equals edh.StartDate
          where em.EmployeeID == edh.EmployeeID
          select new
          {
              em.EmployeeID,
              Date = edh.StartDate
          }).ToList();

Retrouver des enregistrements en précisant les Ids dans un tableau (OrgIds) :

List<Personnel> query = 
    (from p in this.Database.Personnels 
    where OrgIds.Contains(p.OrgID) select p).ToList();

results = (from e in Employee
           orderby e.Id descending
           select e).Take(5).ToList();

public Product GetProduct(int productID)
{
    AdventureWorksDBDataContext db = new AdventureWorksDBDataContext();
    Product product = db.Products.Single(p => p.ProductID == productID);
    return product;
} 

La valeur null ne peut pas être assignée à un membre de type System.Int32, car il s'agit d'un type valeur non nullable.

Quand on crée un objet anonyme lors de la sélection LINQ-to-SQL, les int sont non nullables. Voici un exemple qui permet de les rendre nullable.

var employe = from e in EMPLOYEs
              join fe in DOCUMENTs on uc.FORMULAIRE_EMBAUCHE_PK equals fe.DOCUMENT_PK into tempFE
	      from tfe in tempFE.DefaultIfEmpty()
              select new {
                  Nom = e.NOM,
                  Prenom = e.PRENOM,
                  FormulaireNom = tfe.NOM,
                  FormulaireId = (int?) tfe.DOCUMENT_ID
              };

Si on veut spécifier une valeur possiblement null dans une clause where, il faut utiliser object.Equals(). Comme ça, si la valeur est nulle, la requête SQL contiendra WHERE NOM_COLONNE IS NULL, au lieu de WHERE NOM_COLONNE = NULL.

public static List<Employe> GetEmploye(int? personneId)
{
    using (DataContext db = new DataContext())
    {
        var employes = from e in db.EMPLOYES
                       where object.Equals(e.PersonneId, personneId)
                       select e;
    }
    // etc
}

L'expression LINQ query.Any(x ⇒ predicate) est équivalente en SQL à :

EXISTS(
    SELECT *
    FROM query
    WHERE predicate
    )

Par exemple, cette requête :

from c in db.Customers
where db.Employees.Any(e => e.City == c.City)
select c;

var subquery = from product in MySandbox.Products
where product.Supplier.City.StartsWith("M")
select product.CategoryID;
 
var query = from category in MySandbox.Categories
where
  category.CategoryName.Contains("y")
  && subquery.Contains(category.CategoryID)
select category;

L'extension du cadriciel LINQ a été construit pour utiliser le principe du lazy loading. Il peut être nécessaire de changer ce comportement avec DataLoadOptions. Dans l'exemple suivant, les informations sur le contact de l'employé seront également chargés. Il faut faire attention aux relations de un à plusieurs (1:n) entre les entités qui peuvent apporter des problèmes de performances.

System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();
options.LoadWith<Employee>(emp => emp.Contact);
db.LoadOptions = options;
Employee x = db.Employees.Single(r => r.EmployeeID == 1);
Debug.Print(x.Contact.FirstName);

System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions();
options.AssociateWith<Employee>(c => c.EmployeeDepartmentHistories.Where
                                (r=>r.StartDate > new DateTime(2000,10,31)));
db.LoadOptions = options;
foreach (Employee emp in db.Employees)
{
    foreach (EmployeeDepartmentHistory edh in emp.EmployeeDepartmentHistories)
    { }
}

Le code suivant montre comment créer une requête LINQ un peu plus complexe lors de l'événement Selecting d'un GridView. On voit la concaténation de deux colonnes et deux jointures.

protected void GridView1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    CustomDataContext db = new CustomDataContext();
    var requestList = from t1 in db.NomTable1
                      join t2 in db.NomTable2 on t1.IdNomTable2 equals t2.IdNomTable2
                      join t3 in db.NomTable3 on t1.IdNomTable3 equals t3.IdNomTable3
                      select new {
                            Id = t1.IdNomTable1,
                            t2.Field1,
                            t3.Field1,
                            Nom = t1.Prenom + " " + t1.Nom,
                            Description = t2.Description,
                      };
    e.Result = requestList;
}

Note : Il est préférable de mettre AutoGenerateColumns à false puisque la requête est personnalisée.

var query = db.Companies.Where(c => c.CompanyName == companyName)
              .Join(db.Persons, c => c.CompanyId, p => p.CompanyId, (p, c) => p)
              .Select(p => new
              {
                  Id = p.PersonId,
                  Name = string.Format("{0} {1}", p.FirstName, p.LastName)
              });

Source

var query = from o in db.Orders
    from p in db.Products
    join d in db.OrderDetails 
        on new {o.OrderID, p.ProductID} equals new {d.OrderID,
        d.ProductID} into details
        from d in details
        select new {o.OrderID, p.ProductID, d.UnitPrice};

→ Exemple avec la base de données Northwind.

join ea in EmployesAdresse on em.IdEmploye equals ea.IdEmploye into tempAdresse
from EmployeAdresse in tempAdresse.DefaultIfEmpty()

var query = from s in context.ShoppingMalls
            join h in context.Houses
            on
            new { s.CouncilCode, s.PostCode }
            equals
            new { h.CouncilCode, h.PostCode }
            select s;

• Source

var cities = 	from c in Customers
		group c.ContactName by new {c.City, c.ContactTitle} into g
		where g.Count() > 1
		select g;

var count = from r in result 
	    group r by r.CODE into g
	    orderby g.Count() descending
	    select new {
	        code = g.Key,
		count = g.Count()
	    };
count.Dump();

Pour regrouper dans une même colonne les données non-groupées par group by, par exemple :

On voit que la colonne IdUsers est une combinaison des Id utilisateurs du tableau de gauche.

var result = 
  collection.GroupBy(x => new { x.IdPlace, x.IdInternship })
    .Select(x => new {
                         x.Key.IdPlace,
                         x.Key.IdInternship,
                         IdUsers = String.Join(", ", x.Select(c => c.IdUser.ToString()).ToArray())
                     });

S'il y a une colonne null dans la requête LINQ, par une jointure (LEFT JOIN) qui peut possiblement ne pas avoir d'enregistrement correspondant, on y verra le message suivant :

La valeur null ne peut pas être assignée à un membre de type System.<T>, car il s'agit d'un type valeur non nullable.

Il faut alors caster par un type nullable.

select new 
{
    Id = (int?) tRE.ID,
    Date = (DateTime?) tRE.DATE
}   

Dans certains exemples trouvés sur Internet et dans des livres de références, c'est la méthode Add() qui est utilisée pour ajouter un enregistrement. Cette méthode a été remplacée par InsertOnSubmit().

Department dept = new Department();
dept.Name = txtDepartmentName.Text;
dept.GroupName = txtGroupName.Text;
dept.ModifiedDate = DateTime.Now;
db.Department.InsertOnSubmit(dept);
db.SubmitChanges();
MessageBox.Show("Department added");

Employee emp = db.Employee.Single(r => r.Contact.LastName == txtLastName.Text);
emp.VacationHours = short.Parse(txtVacationHours.Text);
db.SubmitChanges();
MessageBox.Show("Mise à jour réussie.");

Pour régler les conflits potentiels :

try {
    db.SubmitChanges();
}
catch (ChangeConflictException) {
    db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);  // Résoudre le conflit
}

Tout comme l'insertion, la méthode de suppression Remove() a été remplacée par DeleteOnSubmit().

Department dept = db.Department.Single(d => d.Name == txtDepartmentName.Text);
db.Department.DeleteOnSubmit(dept);
db.SubmitChanges();
MessageBox.Show("Department removed");

1. Créer une procédure stockée au besoin. Exemple pour la mise à jour d'un département :

   CREATE PROCEDURE dbo.uspUpdateDepartment
       @Name varchar(50),
       @GroupName varchar(50),
       @ModifiedDate datetime
   AS
       UPDATE HumanResources.Department
       SET [Name] = @Name,
       [GroupName] = @GroupName,
       [ModifiedDate] = @ModifiedDate
   RETURN

2. Glisser-déposer la procédure stockée sur la liste des méthodes du fichier DBML.
3. Aller dans les propriétés de l'entité à partir du designer DBML.
4. Associer la propriété Delete, Insert ou Update avec la méthode ajoutée précédemment.

• Union → Retourne les éléments du premier ensemble ajouté au deuxième sans les doublons.
• Distinct → Enlève les doublons des ensembles.
• Concat → Retourne les éléments du premier ensemble ajouté au deuxième avec les doublons.
• Intersect → Retourne les éléments communs aux deux ensembles.
• Except → Enlève les éléments d'un ensemble qui se trouve dans un autre.

var q = (
        from c in db.Customers
        select c.Country
    ).Union(
        from e in db.Employees
        select e.Country
    );

var q = (
        from c in db.Customers
        select c.Phone
    ).Concat(
        from c in db.Customers
        select c.Fax
    ).Concat(
        from e in db.Employees
        select e.HomePhone
    );

var q = (
        from c in db.Customers
        select c.Country
    ).Intersect(
        from e in db.Employees
        select e.Country
    );

var q = (
        from c in db.Customers
        select c.Country
    ).Except(
        from e in db.Employees
        select e.Country
    );

Source : LINQ 2 SQL Set Operations

public class EmployeeType
{
    public static EmployeeType FullTime;
    public static EmployeeType PartTime;
    public static EmployeeType Occasional;
 
    static EmployeeType()
    {
        var lookup = (from t in CustomDataContext.GetInstance().EmployeeType
                      select t).ToDictionary(i => t.Value);
 
        FullTime = lookup["FullTime"];
        PartTime = lookup["PartTime"];
        Occasional = lookup["Occasional"];
    }
}

public partial class CustomDataContext : System.Data.Linq.DataContext
{
    private static CustomDataContext _instance;
 
    public static CustomDataContext GetInstance()
    {
        if (_instance != null)
        {
            return _instance;
        }
        else
        {
            _instance = new CustomDataContext();
            return _instance;
        }
    }
}

Pour déboguer le code SQL généré par LINQ, il peut être utile d'installer LINQ to SQL Debug Visualizer²⁾.

1. Télécharger le fichier SqlServerQueryVisualizer.zip³⁾
2. Dans le fichier ZIP, extraire le fichier SqlServerQueryVisualizer.dll qui se trouve dans SqlServerQueryVisualizer/bin/Debug.
3. Copier ce fichier dans C:\Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\. Fermer Visual Studio, avant de copier le fichier, si nécessaire.

Dans Visual Studio il suffit de mettre un point d'arrêt après la requête LINQ et quand l'application est arrêtée, mettre le curseur sur la variable représentant la requête.

En cliquant sur la loupe, on obtient la fenêtre SQL Server Query Visualizer. On peut cliquer sur Execute pour voir le resultat.

• The Linq to Sql Profiler
• Huagati Query Profiler - Runtime Profiling Tools for Linq to SQL and LLBLGen Pro

Il est possible d'utiliser LINQ-to-SQL sans passer par la génération de code et le ORM designer. Ceci peut être utile pour modifier le modèle de domaine déjà existant.

Dans le code suivant, on spécifie le nom de la table (Products) qui diffère du nom de l'entité (Product). Mais comme la table a été construite avec les mêmes noms de colonnes que les noms de propriétés, on a pas besoin de spécifier.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
 
namespace DomainModel.Entities
{
    [Table(Name = "Products")]
    public class Product
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        public int ProductID { get; set; } 
 
        [Column] public string Name { get; set; } 
        [Column] public string Description { get; set; } 
        [Column] public decimal Price { get; set; } 
        [Column] public string Category { get; set; }
    }
}

Et le repository (Design Pattern) qui doit accéder aux données :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using DomainModel.Entities;
 
namespace DomainModel.Concrete
{
    class SqlProductsRepository
    {
        private Table<Product> productsTable;
        public SqlProductsRepository(string connectionString)
        {
            productsTable = (new DataContext(connectionString)).GetTable<Product>();
        }
        public IQueryable<Product> Products
        {
            get { return productsTable; }
        }
    }
}

On peut utiliser dans un contrôleur :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DomainModel.Abstract;
using DomainModel.Concrete;
 
namespace WebUI.Controllers
{
    public class ProductsController : Controller
    {
        private IProductsRepository productsRepository;
        public ProductsController()
        {
            string connString = @"Server=SERVER;Database=SportsStore;Trusted_Connection=yes;";
            productsRepository = new SqlProductsRepository(connString);
        }
 
        public ViewResult List()
        {
            return View(productsRepository.Products.ToList());
        }
    }
}

• Source : Pro ASP.NET MVC Framework, Apress, p94.

/// <summary>
/// Cette méthode remet les IDENTITY SEED à 0 pour les tables présentes dans le DataContext.
/// </summary>
public static void ReseedAllTables()
{
    using (DataContext db = new DataContext())
    {
        var model = db.Mapping;
        foreach (var mt in model.GetTables())
        {
            db.ExecuteCommand("DBCC CHECKIDENT ({0}, RESEED, 0)", mt.TableName);
        }
    }
}

Northwind db = ...from somewhere
var model = db.Mapping;
foreach (var mt in model.GetTables())
{
    Console.WriteLine(mt.TableName);
}

Retrouver les noms de colonnes :

        var model = new AttributeMappingSource().GetModel(typeof(Northwind));
        foreach (var mt in model.GetTables()) {
            Console.WriteLine(mt.TableName);
            foreach (var dm in mt.RowType.DataMembers) 
                Console.WriteLine(" "+dm.MappedName);

• Source

string inputFilename = @"C:\temp\file.xlsm";
 
byte[] fileBytes = File.ReadAllBytes(inputFilename);
 
Fichiers.InsertOnSubmit(new Fichier {
  Contenu = fileBytes,
  Nom = "Gabarit.xlsm",
  DateCreation = DateTime.Now,
  Taille = fileBytes.Count()
});
 
SubmitChanges();

domInt[0] = 76;
domInt[1] = 70;
domInt[2] = 72;
 
var result= (from a in Employee
    select new
    {
        a.IdEmployeeDetails,
        DF = String.Join("|", (from d in Addresses
                where domInt.Contains(d.IdAddress)
                select new  {AddressName=d.AddressName}.ToString()).ToArray() ),
                a.IdDepartment,
                a.Status
    }).ToList();

int[] strs = (from c in EMPLOYEs
                select c.EMPLOYE_PK).ToArray();
 
var missing = from i in Enumerable.Range(strs.Min(),strs.Max()-strs.Min())
                where !strs.Contains(i)
                select i;

/// <summary>
/// Donne un bloc d'Ids qui sont disponibles dans la table us_Acces
/// et qui pourront être utilisés.
/// </summary>
/// <remarks>
/// Recueille les trous, c'est-à-dire les Id disponibles ayant des Ids
/// avant et après ceux-ci.  Par exemple, dans la liste :
///   [1,2,4,5,6,9,10,11,...]
/// retournera le 3,7,8,...
/// S'il n'y en a pas assez d'Id récupérés, on retrouve le dernier Id utilisé
/// et on incrémente les Ids à partir de celui-ci.
/// </remarks>
/// <param name="blockLentgth">Nombre de Id demandés.</param>
/// <returns>Bloc d'Ids disponibles qui pourront être utilisés.</returns>
private int[] GetNextAvailableIdBlock(int blockLentgth)
{
    using (AccesBDDataContext db = new AccesBDDataContext())
    {
        int[] IdBlock = new int[blockLentgth];
 
        // On recherche les trous, c'est-à-dire les IdAcces qui ne sont pas utilisés
        int[] t1 = (from a in db.us_Acces
                 where !(from a1 in db.us_Acces
                         select a1.IdAcces)
                         .Contains(a.IdAcces - 1)
                 select a.IdAcces - 1).ToArray();
 
        if (t1.Length < blockLentgth)  // il y a moins de "trous" dans les Id que de Id demandés
        {
            // On récupère le dernier Id disponible + 1 et on incrémentera à partir de cet Id
            int lastAvailableId = (from i in db.us_Acces select i.IdAcces).Max() + 1;
            for (int i = 0; i < IdBlock.Length; i++)
            {
                IdBlock[i] = lastAvailableId++;
            }
        }
        else
        {
            // On prend les premiers disponibles (au nombre de "blockLength") dans la liste.
            IdBlock = t1.Take(blockLentgth).ToArray();
        }
        return IdBlock;
    }
}

1. LINQPad
2. LINQ provider for Oracle, PostgreSQL, MySQL, Ingres, SQLite, Firebird and ... SQL Server (C# 3.0)
3. LINQ + type tables best practices - EnumMapping
4. "How Do I?" with LINQ
5. LINQ to SQL - 5 Minute Overview
6. LINQ to SQL - Detach Entities
7. LINQ to XML
   1. The C# ?? null coalescing operator (and using it with LINQ to XML)
8. LinqConnect
9. A LINQ Tutorial: Mapping Tables to Objects (avec WCF)
10. Common LINQ mistakes

• PLINQO (A set of CodeSmith templates for LINQ to SQL)
• T4 Toolbox (T4 Toolbox: LINQ to SQL schema generator)

1. P. MEHTA, Vijay, Pro LINQ Object Relational Mapping with C# 2008, Apress, 2008.
2. C. RATTZ, Joseph, Pro LINQ - Language Integrated Query in C-Sharp 2008, Apress, 2007.
3. Using LINQ to SQL
4. SQL SubQuery using LINQ
5. Introducing LINQ
6. How-To: Common Data Patterns with LINQ to SQL
7. LINQ to SQL - 5 Minute Overview
8. 101 LINQ Samples

Exemple de Where(predicate) avec une liste d'étudiants :

List<Etudiant> etudiants = Etudiant.GetAll();
Etudiant etudiant = etudiants.Where(e => e.Id == 2).First();

• LINQ to Objects

Il y a plusieurs façons de charger un fichier XML:

• Un document XML complet
• Un élément XML contenant des enfants ou non.

Pour charger un document complet on peut utiliser XDocument.Load(string filename). Le fichier XML doit être un fichier XML complet valide.

XDocument doc = XDocument.Load(@"C:\\Current.xml");

Ou un fichier contenant un élément particulier root avec XElement.Load(string filename).

XElement doc = XElement.Load(@"C:\\Current.xml");

Pour sauvegarder un fichier XML c'est avec XDocument.Save(string filename).

doc = XDocument.Save(@"C:\\Current_saved.xml");

Utiliser XDocument.Parse(string text).

XDocument doc = XDocument.Load(@"C:\\Current.xml");
var elements = from i in doc.Descendants("Revision")
               select i;
elements.Remove();
doc.Save(@"C:\\Current.xml");

  XElement weekToArchive = new XElement("week", new XAttribute("value", Week.GetCurrentWeekStr()));
    foreach (var comment in commentsToArchive) {
      weekToArchive.Add(
        new XElement("comment",
          new XAttribute("datetime", DateTime.Now.ToString()),
          new XAttribute("keyid", comment.Attribute("ID").Value),
          new XAttribute("text", comment.Attribute("Comment").Value)
        )
    );
}