Show Day of the week numeric value in Grid row for whole week

I have a normalized table which shows the supply delivery days for different supplies. The table is normalized keeping with good DB practices and shows the day of the week as a numeric value (1,2,3 etc). I am using Entity framework and a Telerik grid and need to display the weekdays on the grid showing each day in the week and the min/max number of units that can be delivered on that day. This table (Supply Deliveries) is linked to the Product Table. I have shown the table design and the desired format in the grid below.

I am not sure how to display this data in the grid. I was told I can use Presentation model to display this? I haven't any examples of how to do this. If someone can show me with a code example preferably on what's the best way to do this with Entity Framework and C# so it can take the no of day and know where to bind in the grid that would be great. Many thanks in advance!

Table: Products

product_id  (PK, INT, not null)      
ProductName (varchar(150), not null) 
Cost (decimal(18,2), not null)   

Table : SupplyDeliveries

schedule_id (PK, INT, not null)    
product_id (FK, INT, not null)     
DayOfTheWeek (smallin开发者_StackOverflow社区t, not null)  //(Day of the week stored in number for ex 1,2,3 )   
MinNo (int, not null)         
MaxNo (int, not null)

*NOTE: So if I wanted to show schedule for Paper deliveries in table SupplyDeliveries here is what that record would look like for product_id = 1 (Paper), DayofWeek = 1 (Monday), MinNo=4, MaxNo=5

so in the grid you wil see for Dayoftheweek = 1 (Monday) the min/max units (4/5) I can recieve and there will be another record for product_id=1 (Paper), DayOftheWeek = 2 (Tuesday) to show the min/max units I can get as well..there will be a seperate record for each product for each day of the week.....hope that helps

This is what I want to show in a grid:

Product Name Cost   Mon  Tue  Wed  Thu  Fri  Sat  Sun

Paper          $5   4/5  4/5                            
Stationery    $20   4/5       8/10      8/10
Printers     $100   4/5       5/6  5/6

---

First of all, regarding your model, why do you have a schedule_id column in your Products table? You are storing the relationship between product and schedule in the SupplyDeliveries table, so it seems like the schedule_id column in your Products table is unnecessary.

What you are trying to do is called a pivot. You're taking data modeled as rows and displaying it as columns. As far as I know, there is no explicit mechanism for expressing a pivot in LINQ.

There are several approaches you could take here:

1. You could create a view in your database that pivots the data and expresses it just as you've shown in your results. Use EF to query the view and display the results in the grid. Display should be easy since the entities materialized by EF will be exactly what you're trying to display.

2. You could use EF and queries over a grouping expression to perform a pivot. This likely will not be as fast as doing the pivot in a view in the db, but should accomplish the same result. See below for an example.

3. You could also change your db model so that it is already column based. One thing to note about your existing model is that without a second unique index on SupplyDeliveries(product_id, DayOfTheWeek), you could have multiple "Monday" records for the same product. Maybe that's okay... However, if you don't want that in the first place, another model you could consider for your data would be to have columns: (product_id, mon_min, mon_max, tue_min, ...). This eliminates the pivot entirely.

Here's an example for #2:

from s in SupplyDeliveries
group s by s.product_id into g
select new 
{
    ProductId = g.Key, 
    MondayMin = (from x in g where x.DayOfTheWeek == 1 select x.MinNo).FirstOrDefault(),
    MondayMax = (from x in g where x.DayOfTheWeek == 1 select x.MaxNo).FirstOrDefault(),
    TuesdayMin = ...
}

Edit:

So to recap, approach #1 has you constructing a pivot query in SQL and exposing it to EF as a view, while #2 does it in EF as a LINQ expression over the underlying table. The advantage of #1 (depending on your underlying database) is you could take advantage of SQL operators like PIVOT and transform your data more efficiently before it hits the application layer. The advantage of #2 is that you can keep this transformation in the application layer, which might be easier for you to maintain, especially if your database up until this point is strictly just tables.

With regards to #3, it's just a suggestion and representative of what you could do. I don't know the details of your model and your application, so it's hard to make complete suggestions. However, I would not be concerned with the data being sparse in this case - there are relatively few columns involved, especially if you have only one min/max per weekday per product. From a space efficiency point of view, excluding product_id, you have 56 bytes in the column approach and 14 bytes in the row approach (in the row approach you also have to store the day of week and a separate schedule_id column). So if you have 4 days of the week specified on average per product you break even. This excludes the extra space you'll need in the row approach for appropriate indexing. Also, in the row approach, your queries will always be more complex (i.e. slower) because of extra joins and filtering.

---

Thanks for your help Michael, your suggestion got me thinking in the right direction I ended up making a pivot table and using a DTO class to bind to the result. I was able to get all the values the way I wanted. I hope this helps someone else as well I looked at the following example for creating pivot table

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110576