renvoie une liste de données à dapper via une proc stockée

Question

J'essaie de renvoyer des données à l'aide de Dapper via une procédure stockée.

Ma classe DTO est similaire à la suivante (certaines propriétés ont été supprimées pour des raisons de concision)

public class CarDTO
{
    public int CarID { get; set; }
    public string Manufacturer { get; set; }
    public List<CarOptionDTO> CarOptions { get; set; }
}

Dans la base de données, j'ai une table CarOption avec une colonne CarID - c'est-à-dire qu'une voiture peut avoir plusieurs options.

Mon appel DAL Layer est actuellement le suivant :

    private string getCarDataSp = "[dbo].[GetCarData]";

    public IEnumerable<CarDTO> GetCarData(int customerId, int year)
    {
        return Get(db => db.Query<CarDTO>(getCarDataSp , new { CustomerID = customerId, Year = year },
                                commandType: CommandType.StoredProcedure));
    }

L'implémentation de ma fonction Get se trouve dans ma classe BaseRepository sous la forme suivante :

    public T Get<T>(Func<IDbConnection, T> query)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            return query.Invoke(db);
        }
    }

Est-il possible d'utiliser Dapper pour renvoyer les options de la voiture à partir de ma procédure stockée ?

Ma procédure stockée à l'heure actuelle est la suivante :

ALTER PROCEDURE [dbo].[GetCarData]
    @CustomerID int, 
    @Year int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * from [dbo].Car c
    JOIN [dbo].Customer cust ON c.CarID = cust.CarID
    WHERE cust.CustID = @CustomerID AND cust.Year = @Year

END

la requête ci-dessus peut renvoyer de nombreuses lignes et l'ID de la voiture, le fabricant et les autres propriétés ont été supprimés par souci de concision. Dapper les renverra vers le DTO comme prévu.

Cependant, il est difficile de renvoyer la liste des options de voiture dans la procédure stockée - est-ce possible avec une autre requête ou faut-il la séparer d'une manière ou d'une autre ? Si j'ai retourné CarID 1 et CarID 2, par exemple, il peut y avoir 6 lignes dans la table CarOption avec CarID 1 et 4 lignes dans la table CarOption avec CarID 2 et idéalement, j'aimerais qu'elles soient toutes retournées dans la collection CarOptions via Dapper si possible ?

Answer 1

Oui, c'est possible. Il existe plusieurs façons d'aborder le scénario "one-to-many" avec dapper :

MÉTHODE 1 - Renvoyer deux requêtes, les combiner dans le DAL

ALTER PROCEDURE [dbo].[GetCarData]
    @CustomerID int, 
    @Year int
AS
BEGIN
    SET NOCOUNT ON;

    --return cars
    SELECT c.*
        from [dbo].Car c
    INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
    WHERE cust.CustID = @CustomerID AND cust.Year = @Year

    --return options
    SELECT opt.*
        from [dbo].Car c
    INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
    INNER JOIN dbo.CarOptions opt ON op.CarID = c.CarID
    WHERE cust.CustID = @CustomerID AND cust.Year = @Year

END

DAL

var multi = db.QueryMultiple(getCarDataSp , new { CustomerID = customerId, Year = year },
                                commandType: CommandType.StoredProcedure));

var cars = multi.Read<CarDTO>();
var options = multi.Read<CarOptionDTO>();

//wire the options to the cars
foreach(var car in cars){
    var carOptions = options.Where(w=>w.Car.CarID == car.CarID);        //I would override Equals in general so you can write w.Car.Equals(car)...do this on a common DataModel class
    car.Options = carOptions.ToList();
}

MÉTHODE 2 - Renvoi d'une requête, divisée en DAL

Proc

ALTER PROCEDURE [dbo].[GetCarData]
    @CustomerID int, 
    @Year int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT c.*,  opt.*
     from [dbo].Car c
    INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
    LEFT OUTER JOIN dbo.CarOptions opt ON op.CarID = c.CarID
    WHERE cust.CustID = @CustomerID AND cust.Year = @Year

END

DAL

var tuples = db.Query<CarDTO, CarOptionDTO,Tuple<CarDTO,CarOptionDTO>>(getCarDataSp , new { CustomerID = customerId, Year = year },
(car,opt)=> Tuple.Create(car,opt),                       commandType: CommandType.StoredProcedure);

//group tuples by car
var cars = tuple.GroupBy(gb=>gb.Item1.CarID)                    //again, overriding equals makes it so you can just to GroupBy(gb=>gb.Item1)
            .Select(s=>{
            var car = s.First().Item1;
            var carOptions = s.Select(t=>t.Item2).ToList()

            return car;
            });

Améliorations

Utilisation d'une table temporaire dans la requête

Cela permet de regrouper tous les paramètres de filtrage en une seule requête. Les requêtes suivantes sont des sélections simples par ID.

ALTER PROCEDURE [dbo].[GetCarData]
    @CustomerID int, 
    @Year int
AS
BEGIN
    SET NOCOUNT ON;

    declare @t table(CarID int);

    --filter cars (only deal with parameters here)
    INSERT INTO @t(CarID)
    SELECT c.CarID
    FROM dbo.Car c
        INNER JOIN [dbo].Customer cust ON c.CarID = cust.CarID
    WHERE cust.CustID = @CustomerID AND cust.Year = @Year

    --return cars
    SELECT c.*
    FROM [dbo].Car c
        INNER JOIN @t t ON t.CarID = c.CarID

    --return options
    SELECT opt.*
    FROM dbo.CarOptions opt
        INNER JOIN @t t ON t.CarID = opt.CarID

END

Application d'un BaseDTO pour faciliter l'égalité

Une fois que vous avez le BaseDTO, et que vous avez câblé votre ID, vous pouvez simplement dire des choses comme:cars.Where(w=>w.Equals(car)), dictionary[car] (s'il est là), if(car.Equals(otherCar)), ou results.GroupBy(gb=>gb.Car)...

public class BaseDTO
{
    internal int ID { get; set; }

    /// <summary>
    /// If the obj is the same type with the same id we'll consider it equal.
    /// </summary>
    public override bool Equals(object obj)
    {
        if(obj == null || this.GetType() != obj.GetType())
        {
            return false;
        }

        return this.GetType().GetHashCode() == obj.GetType().GetHashCode() &&
                this.ID == (BaseDTO)obj.ID;
    }

    /// <summary>
    /// If you override equals, you should override gethashcode.  
    /// http://stackoverflow.com/questions/263400/what-is-the-best-algorithm-for-an-overridden-system-object-gethashcode#263416
    /// </summary>
    public override int GetHashCode()
    {
        unchecked
        {
            int hash = 17;

            hash = hash * 23 + this.GetType().GetHashCode();
            hash = hash * 23 + this.ID;

            return hash;
        }
    }
}

public class CarDTO : BaseDTO
{
    public int CarID
    {
        get { return this.ID; }
        set { this.ID = value; }
    }
    public string Manufacturer { get; set; }
    public List<CarOptionDTO> CarOptions { get; set; }
}