0

I have a problem I have to solve. Can I convert the resultset below to editable pivot using Entity Framework? I also want to keep the data in the context. My data is like that:

enter image description here

At end of the operation the result I would like to see is that: enter image description here Please note, the table created as pivot should be editable..

esquare
  • 3,947
  • 10
  • 34
  • 37

1 Answers1

1

Entity framework does not have any support for showing the data in pivot format.So the only way to do this is- Create a class with properties same as your resultset

public class Orders
{
    public int OrderId{get;set;}
    public int OrderShippingId{get;set;}
    public int ColorId{get;set;}
    [DisplayName("11")]
    public int Sizeid11 { get; set; }
    [DisplayName("12")]
    public int Sizeid12 { get; set; }
    [DisplayName("13")]
    public int Sizeid13 { get; set; }
    [DisplayName("14")]
    public int Sizeid14 { get; set; }
    [DisplayName("15")]
    public int Sizeid15 { get; set; }
    [DisplayName("16")]
    public int Sizeid16 { get; set; }
    [DisplayName("17")]
    public int Sizeid17 { get; set; }
}

Add the data from EF in List using LINQ query- private OrderEntities ctx = new OrderEntities();

// A Linq to EF which creates a List of concreate class called IsoPivot.
private List<Orders> queryResults =
    (from ord in ctx.Orders
        orderby ord.Colorid ascending
        group ord by ord.OrderId
        into ordGroup
        select new Orders()
        {
            OrderId = ordGroup.Key,
            OrderShippingId= ordGroup.OrderShippingId,
            ColorId=ordGroup.ColorId,
            Sizeid11=ordGroup.where(j=>j.Sizeid==11).sum(j=>j.Quantity)
            Sizeid12=ordGroup.where(j=>j.Sizeid==12).sum(j=>j.Quantity)
            Sizeid13=ordGroup.where(j=>j.Sizeid==13).sum(j=>j.Quantity)
            Sizeid14=ordGroup.where(j=>j.Sizeid==14).sum(j=>j.Quantity)
            Sizeid15=ordGroup.where(j=>j.Sizeid==15).sum(j=>j.Quantity)
            Sizeid16=ordGroup.where(j=>j.Sizeid==16).sum(j=>j.Quantity)
             Sizeid17=ordGroup.where(j=>j.Sizeid==17).sum(j=>j.Quantity)
        }).ToList();
}

This the way to show the list data in editable fields.

In order to edit, you can commit the changes in the list on a button click and commit changes back to EF.

Debashree
  • 11
  • 3