J'ai trois modèles suivants. Je souhaite les rejoindre et les regrouper avec LINQ dans mon application MVC pour obtenir les données de résultat requises.

ESSAYÉ AVEC DE FAUTES DONNÉES

var query = from i in db.Invoices
                    join id in db.Invoice_Details
                    on i.INVOICENO equals id.INVOICENO
                    join m in db.Mixings
                    on id.INVOICEDETAILID equals m.INVOICEDETAILID into ms
                    from m in ms.DefaultIfEmpty()
                    group new { id,m } by new
                    {
                        INVOICENO = id.INVOICENO,
                        DATE = i.DATE


                    }
                    into temp
                    select new Invoice_List
                    {
                        ID = temp.Key.INVOICENO,
                        INVOICENO = temp.Key.INVOICENO,
                        CARET = temp.Sum(g => g.id.CARET),
                        DATE = temp.Key.DATE,
                        ISSUECARET = (decimal?)temp.Select(c => c.m.CARETUSED).DefaultIfEmpty(0).Sum() ?? 0,
                        AVAILABLECARET = ((decimal?)temp.Select(c => c.id.CARET).DefaultIfEmpty(0).Sum() ?? 0) - ((decimal?)temp.Select(c => c.m.CARETUSED).DefaultIfEmpty(0).Sum() ?? 0)
                    };

Tableau de facturation

INVOICENO        DATE
---------------------
1                2017-01-23 00:00:00
2                2017-01-23 00:00:00

Tableau détaillé de la facture

INVOICEDETAILID        INVOICENO        CARET
----------------------------------------------
1                      1                100.00
2                      1                200.00
3                      2                300.00
4                      2                400.00  

Table de mélange

MIXINGNO        INVOICEDETAILID        CARETUSED
------------------------------------------------
1               1                      50.00
1               2                      100.00                
2               1                      25.00
2               2                      50.00

Maintenant, je veux suivre les données de résultat en joignant ces trois tables avec group by.

RÉSULTAT ATTENDU

INVOICENO        DATE                        TOTALCARET          CARETUSEDCARET        AVAILABLECARET
------------------------------------------------------------------------------------------
1                2017-01-23 00:00:00         300.00              225.00                75.00
2                2017-01-23 00:00:00         700.00              0.00                  700.00

MAUVAIS RÉSULTAT (EN TOTALCARET POUR 1 INVOICENO)

INVOICENO        DATE                        TOTALCARET          CARETUSEDCARET        AVAILABLECARET
------------------------------------------------------------------------------------------
1                2017-01-23 00:00:00         600.00              225.00                375.00
2                2017-01-23 00:00:00         700.00              0.00                  700.00
1
Hemal 24 janv. 2017 à 10:31

4 réponses

Meilleure réponse

L'une des plus grandes fonctionnalités d'EF est ce que l'on appelle les propriétés de navigation . Lorsqu'elles sont utilisées dans les requêtes LINQ to Entities, elles fournissent les métadonnées nécessaires pour créer les jointures nécessaires lors de la traduction de la requête en SQL. Et vous permet de créer les requêtes comme si elles étaient exécutées sur des objets, ce qui élimine fondamentalement le besoin de penser aux jointures, mais concentrez-vous sur votre logique.

Supposons que votre modèle ressemble à ceci (affichant uniquement les propriétés de navigation):

public class Invoice
{
    // ...
    public ICollection<InvoiceDetail> Details { get; set; }
}

public class InvoiceDetail
{
    // ...
    public ICollection<Mixing> Mixings { get; set; }
}

En regardant également les tables, il semble que InvoiceNo est un PK du Invoice.

Dans ce cas, vous n'avez même pas besoin de GroupBy. Les 2 premiers champs proviennent de Invoice, les autres sont récupérés avec Sum des enfants:

var query =
    from i in db.Invoices
    let TOTALCARET = i.Details.Sum(d => (decimal?)d.CARET) ?? 0
    let USEDCARET = i.Details.SelectMany(d => d.Mixings).Sum(m => (decimal?)m.CARETUSED) ?? 0
    select new
    {
        i.INVOICENO,
        i.DATE,
        TOTALCARET,
        USEDCARET,
        AVAILABLECARET = TOTALCARET - USEDCARET
    };

La seule astuce consiste à promouvoir le type non nullable en nullable lors de l'utilisation de la fonction Sum pour éviter une exception lorsque la séquence source est vide. Ensuite, l'opérateur null-coalescing est utilisé pour le ramener à non nullable si nécessaire.

2
Ivan Stoev 24 janv. 2017 à 13:13

J'ai écrit un exemple en réponse aux commentaires que nous avons eu, cela pourrait ne pas résoudre tous vos problèmes, mais pourrait être un bon début. Voici mon environnement de test:

public class Invoice
{
    public int InvoiceNo { get; set; }
    public DateTime DateTime { get; set; }
}


public class InvoiceDetails
{
    public int InvoiceDetailId { get; set; }
    public int InvoiceNo { get; set; }
    public decimal Caret { get; set; }
}

public class Mixing
{
    public int MixingNo { get; set; }
    public int InvoiceDetailId { get; set; }
    public decimal CaretUsed { get; set; }

}

private static void ExecQuery()
{
    var invoices = new List<Invoice>();
    invoices.Add(new Invoice { InvoiceNo = 1, DateTime = new DateTime(2017, 1, 23) });
    invoices.Add(new Invoice { InvoiceNo = 2, DateTime = new DateTime(2017, 1, 23) });

    var invoiceDetails = new List<InvoiceDetails>();
    invoiceDetails.Add(new InvoiceDetails { InvoiceDetailId = 1, InvoiceNo = 1, Caret = 100 });
    invoiceDetails.Add(new InvoiceDetails { InvoiceDetailId = 2, InvoiceNo = 1, Caret = 200 });
    invoiceDetails.Add(new InvoiceDetails { InvoiceDetailId = 3, InvoiceNo = 2, Caret = 300 });
    invoiceDetails.Add(new InvoiceDetails { InvoiceDetailId = 4, InvoiceNo = 2, Caret = 400 });

    var mixings = new List<Mixing>();
    mixings.Add(new Mixing { MixingNo = 1, InvoiceDetailId = 1, CaretUsed = 50 });
    mixings.Add(new Mixing { MixingNo = 2, InvoiceDetailId = 2, CaretUsed = 100 });
    mixings.Add(new Mixing { MixingNo = 3, InvoiceDetailId = 1, CaretUsed = 25 });
    mixings.Add(new Mixing { MixingNo = 4, InvoiceDetailId = 2, CaretUsed = 50 });


    // select all from invoices
    var query = from i in invoices
                // join the details
                join id in invoiceDetails on i.InvoiceNo equals id.InvoiceNo
                // group the details on invoice
                group id by new { i.InvoiceNo, i.DateTime } into ig

                // again join the details (from the mixing)
                join id in invoiceDetails on ig.Key.InvoiceNo equals id.InvoiceNo
                // join the mixing
                join mix in mixings on id.InvoiceDetailId equals mix.InvoiceDetailId into mix2 // store in temp for outer join
                from mbox in mix2.DefaultIfEmpty()
                // group mixing (and sum the caret of the previous group
                group mbox by new { ig.Key.InvoiceNo, ig.Key.DateTime, TotalCaret = ig.Sum(item => item.Caret) } into igm
                // calculate the caret used (because it is used twice in the results)
                let caretUsedCaret = igm.Where(item => item != null).Sum(item => item.CaretUsed)
                // select the results.
                select new
                {
                    igm.Key.InvoiceNo,
                    igm.Key.DateTime,
                    igm.Key.TotalCaret,
                    CaretUsedCaret = caretUsedCaret,
                    Available = igm.Key.TotalCaret - caretUsedCaret
                };


    foreach (var row in query)
    {
        Trace.WriteLine(row.ToString());
    }

}

Quels résultats montrent:

{ InvoiceNo = 1, DateTime = 23-Jan-17 00:00:00, TotalCaret = 300, CaretUsedCaret = 225, Available = 75 }
{ InvoiceNo = 2, DateTime = 23-Jan-17 00:00:00, TotalCaret = 700, CaretUsedCaret = 0, Available = 700 }
3
Jeroen van Langen 24 janv. 2017 à 09:04

SI QUELQU'UN A LA SOLUTION AVEC LINQ, VEUILLEZ LA POSER ICI. La réponse de @Jeroen van Langen est très proche mais me donne une erreur.

Avec quelques modifications et essais, j'ai résolu au moins le problème avec Raw SQLQuery. LINQ ne m'aide pas avec des requêtes complexes et imbriquées. Voici mon code SQL brut que je connais assez bien et qui fonctionne comme une solution temporaire. J'ai ajouté quelques autres tables également selon mon exigence finale.

SOLUTION TEMP WORKING AVEC RAW SQL QUERY

var str = "select";
        str += " a.ID,a.INVOICENO,a.TOTAL,a.CARET,a.DATE,a.PARTY,a.BROKER,";
        str += " ISNULL(b.CARET,0) as ISSUECARET,";
        str += " ISNULL(a.CARET,0) - ISNULL(b.CARET,0) as AVAILABLECARET";
        str += " from";
        str += " (";
        str += " select";
        str += "     i.INVOICENO as ID,";
        str += " i.INVOICENO,";
        str += " i.DATE,";
        str += " a.accountname as PARTY,";
        str += " b.accountname as BROKER,";
        str += " SUM(id.CARET) as CARET,";
        str += " SUM(id.TOTAL) as TOTAL";
        str += "     from invoice i";
        str += "     inner";
        str += " join Invoice_Details id";
        str += "                 on i.INVOICENO = id.INVOICENO";
        str += " inner join account a on a.ID=i.party inner join account b on b.ID=i.broker";
        str += "     group by";
        str += " i.id,";
        str += " i.INVOICENO,";
        str += " i.DATE,";
        str += " a.accountname,";
        str += " b.accountname";
        str += " )";
        str += " as a";
        str += " left join";
        str += " (";
        str += " select";
        str += "     id.INVOICENO,";
        str += " SUM(m.caret) as CARET";
        str += "     from";
        str += " Invoice_Details id";
        str += "     left";
        str += " join";
        str += " Mixing m";
        str += " on id.ID = m.INVOICEDETAILID";
        str += "     group by id.invoiceno";
        str += " )";
        str += "    as b";
        str += " on a.INVOICENO = b.INVOICENO";
        var query = db.Database.SqlQuery<Invoice_List>(str);
0
Hemal 24 janv. 2017 à 12:45

Ha Ha :) J'ai écrit la même chose juste avec la chaîne de méthodes ...

   public class Invoice
    {
        public int INVOICENO { get; set; }
        public DateTime DATE { get; set; }
    }

    public class InvoiceDetail
    {
        public int INVOICEDETAILID { get; set; }
        public int INVOICENO { get; set; }
        public int CARET { get; set; }
    }

    public class Mixing
    {
        public int MIXINGNO { get; set; }
        public int INVOICEDETAILID { get; set; }
        public int CARETUSED { get; set; }
    }

    [Fact]
    public void LinqTest()
    {
        List<int>  ints = new List<int> {1,2,3};

        List<Invoice> invoices = new List<Invoice>
        {
            new Invoice {INVOICENO = 1, DATE = DateTime.Parse("23/01/2017")},
            new Invoice {INVOICENO = 2, DATE = DateTime.Parse("23/01/2017")}
        };

        List<InvoiceDetail> invoiceDetails = new List<InvoiceDetail>
        {
            new InvoiceDetail{ INVOICEDETAILID = 1, INVOICENO = 1, CARET = 100},
            new InvoiceDetail { INVOICEDETAILID = 2, INVOICENO = 1, CARET = 200},
            new InvoiceDetail { INVOICEDETAILID = 3, INVOICENO = 2, CARET = 300},
            new InvoiceDetail {INVOICEDETAILID = 4, INVOICENO = 2, CARET = 400}
        };

        List<Mixing> mixings = new List<Mixing>
        {
            new Mixing {MIXINGNO = 1, INVOICEDETAILID = 1, CARETUSED = 50},
            new Mixing {MIXINGNO = 1, INVOICEDETAILID = 2, CARETUSED = 100},
            new Mixing {MIXINGNO = 2, INVOICEDETAILID = 1, CARETUSED = 25},
            new Mixing {MIXINGNO = 2, INVOICEDETAILID = 2, CARETUSED = 50}
        };

        var q =
            invoices.Join(invoiceDetails, i => i.INVOICENO, id => id.INVOICENO, (invoice, detail) => new {invoice, detail})
                .GroupJoin(mixings, arg => arg.detail.INVOICEDETAILID, m => m.INVOICEDETAILID,
                    (arg, m) => new {arg.invoice, arg.detail, Mixings = m})
                .GroupBy(arg => arg.invoice)
                .Select(
                    g =>
                        new
                        {
                            g.Key.INVOICENO,
                            g.Key.DATE,
                            Tot_Caret = g.Sum(arg => arg.detail.CARET),
                            Tot_Used = g.Sum(arg => arg.Mixings.Sum(mixing => mixing.CARETUSED)),
                            Available = g.Sum(arg => arg.detail.CARET) - g.Sum(arg => arg.Mixings.Sum(mixing => mixing.CARETUSED))
                        });
    }
2
Ofir Winegarten 24 janv. 2017 à 09:01