38667

How to generate a Pivot Datagridview in WinForms and EntityFramework

I want to generate a Pivot Datagridview in WinForms and EntityFramework, starting from theses Entities:

public class Return { public Return() { this.ReturnQty = new List<ReturnQty>(); } public int Id { get; set; } public Nullable<System.DateTime> Date { get; set; } public Nullable<int> ReturnReason { get; set; } public Nullable<System.DateTime> Belastungsdatum { get; set; } public virtual ICollection<ReturnQty> ReturnQty { get; set; } } public class ReturnQty { public int ID { get; set; } public int Return_ID { get; set; } public string ItemNo { get; set; } public Nullable<decimal> Qty { get; set; } public virtual Return Return { get; set; } }

The Pivot Dataviewgrid should look like this:

<img src="https://i.stack.imgur.com/C1BtW.jpg" alt="enter image description here">

This is what I have now:

public static class ReturnReasons { public static string a { get { return "Grund nicht erfasst"; } } public static string a1 { get { return "Artikel mangelhaft"; } } public static string a2 { get { return "Bestellirrtum Kunde"; } } public static string a3 { get { return "Doppelbestellung Kunde"; } } public static string a4 { get { return "AV Kunde"; } } public static string a5 { get { return "Kundenadresse falsch"; } } public static string a6 { get { return "Kunde nicht erreichbar"; } } public static string a7 { get { return "Kundenstorno"; } } public static string a8 { get { return "Nichtgefallen"; } } public static string a9 { get { return "Transportschaden UPS/DHL/GLS"; } } public static string a10 { get { return "Transportschaden Spedition"; } } public static string a11 { get { return "Transportschaden verdeckt"; } } public static string a12 { get { return "Kommissionierungsfehler"; } } public static string a13 { get { return "Fehler Auftragserfasung"; } } public static string a14 { get { return "Lieferverzögerung"; } } public static string a15 { get { return "Warenrücksendung lt. Vereinbarung"; } } public static string a16 { get { return "ohne Grund/sonstiges"; } } } public class RetourenPivot { public string Item{ get; set; } public IEnumerable<int?> ReturnReason{ get; set; } public IEnumerable<decimal?> Qty{ get; set; } } private void DG_databind() { var query = _data.RepositoryRetouren.GetAll<ReturnQty>(); // A Linq to EF which creates a List of concreate class called RetourenPivot. var queryResults = (from iso in query orderby iso.ItemNo ascending group iso by iso.ItemNo into isoGroup select new RetourenPivot() { Item = isoGroup.Key, ReturnReason = isoGroup.Select(y => y.Return.ReturnReason), Qty = isoGroup.Select(v => v.Qty) }).ToList(); // Call a function to create a dynamically created data table with the needed columns // Create a DataTable as a DataSource for the grid DataTable dt = new DataTable(); // Create the DataColumns for the data table DataColumn dc = new DataColumn("Artikel", typeof(string)); dt.Columns.Add(dc); // Get a list of Distinct Reasons var ReasonLabel = (from yList in queryResults.Select(Reason => Reason.ReturnReason) from Reason in yList select Reason.ToString()).Distinct().ToList(); // Create the DataColumns for the table ReasonLabel.ForEach(delegate(string Reason) { var reasonTexts = typeof(ReturnReasons).GetProperties().Where(p => p.Name == "a" + Reason).ToList()[0].GetValue(null, null).ToString(); dc = new DataColumn(reasonTexts, typeof(string)); dt.Columns.Add(dc); }); // Populate the rowa of the DataTable foreach (RetourenPivot rec in queryResults) { // The first two columns of the row always has a ISO Code and Description DataRow dr = dt.NewRow(); dr[0] = rec.Item; // For each record var Reason = rec.ReturnReason.ToList(); var Qty = rec.Qty.ToList(); // Because each row may have different reasons I am indexing // the with the string name for (int i = 0; i < Qty.Count; i++) { var reasonTexts = typeof(ReturnReasons).GetProperties().Where(p => p.Name == "a" + Reason[i].ToString()).ToList()[0].GetValue(null, null).ToString(); dr[reasonTexts] = Qty[i].Value; } // Add the DataRow to the DataTable dt.Rows.Add(dr); } // Bind the DataTable to the DataGridView dataGridViewSummary1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing; dataGridViewSummary1.ColumnHeadersHeight = 200; //Connect Grid with DataSource //this.dataGridViewSummary1.AutoGenerateColumns = true; this.dataGridViewSummary1.DataSource = dt; }

The problem is, that each RetourenPivot in queryResults might have several quantities with the same ReturnReason and I don’t know how to sum them up. E.g. now it looks like

Item 123 ReturnReason {0, 0, 0, 0, 1, 16} Qty {1, 1, 2, 1, 5, 1}

What means, that for the reason 0 there are 4 quantities. It should look like

Item 123 ReturnReason { 0, 1, 16} Qty {5, 5, 1}

Answer1:

this is not a recipe but only some advices to solve your problem.

1) ReturnReason should be a class:

public class ReturnReason { public int Code { get; set; } public string Description {get; set;} }

2) Create a ReturnReasonList class. Implement it as a singleton and use it to provide Reasons:

public sealed class ReturnReasonList { protected List<ReturnReason> pInstance = new List<ReturnReason> { { .Code=1, .Description="whatever reason 1" }, { .Code=2, .Description="whatever reason 2" }, ... { .Code=n, .Description="whatever reason n" }}; private ReturnReasonList() {} public List<Returnreason> pInstance { get { return pInstance; } } }

3) Consider what you really need for each pair (ItemNo, ReturnReason):

public class ReturnedQuantity { int ItemNumber { get; set; } Returnreason { get; set;} int Quantity {get; set;} }

4) Now you have to create a List from your return data:

var query = _data.RepositoryRetouren.GetAll<ReturnQty>(); List<ReturnedQuantity> returns = from r in query join rs in ReturnReasonList.Instance on r.ReturnReason = rs.Code group by rs, r.ItemNo select new ReturnedQuantity() {.ItemNo = r.ItemNo, .Reason= rs, .Quantity = SUM(r.Qty) };

5) Fill your DataGridView columns collections with items in ReturnReasonList.Items

6) Fill your DataGridView rows collection with items in your article list.

7) Fill your DataGridView cells with the quantities.

Answer2:

I hereby answer my own question. I don't know if this solution is the best possible one.

I created two temporary classes for the query

public class RetourenNeu { public string Artikel { get; set; } public int? Retourengrund { get; set; } public decimal? Anzahl { get; set; } } public class RetourenPivot { public string Artikel { get; set; } public IEnumerable<int?> Retourengrund { get; set; } public IEnumerable<decimal?> Anzahl { get; set; } }

And than I make two queries. The first query groups by ReturnReason and Itemnumber and sums up the quantities. The second query groups the first query by Itemnumber.

var queryResults = from iso in query orderby iso.Artikelnummer ascending group iso by new { iso.Artikelnummer, iso.Retourenkopfdaten.Retourengrund } into isoGroup select new RetourenNeu() { Artikel = isoGroup.Key.Artikelnummer, Retourengrund = isoGroup.Key.Retourengrund.HasValue ? isoGroup.Key.Retourengrund.Value : 0, Anzahl = isoGroup.Select(v => v.Anzahl).Sum() }; var neu = (from n in queryResults group n by n.Artikel into source select new RetourenPivot() { Artikel = source.Key, Retourengrund = source.Select(s => s.Retourengrund), Anzahl = source.Select(s => s.Anzahl) }).ToList();

Now the result is as required.

Recommend