score:2

Accepted answer

since you also want to order the results by dayofweek, using a dictionary is not appropriate, and you should start with a view model to represent what you want in the view

public class salesvm
{
    public dayofweek day { get; set; }
    public int sales { get; set; }
}

in order to prevent the exception, materialize your query first (using tolist() or .asenumerable()) and i suggest you just satrt by selecting only the data you need from the database

viewbag.dailygraph = ctx.storeitemtransactions
    .select(x => new { date = x.date, sales = x.sales }) // select only required data
    .tolist() // materialize
    .groupby(x => x.date.dayofweek) // group by day of week
    .select(x => new testvm() { day = x.key, sales = x.sum(y => y.sales) }) // project to view model
    .orderby(x => (int)x.day); // order by day of week

score:2

linq to entities does not know how to tranlsate that property of datetime to sql, you need to use sqlfunctions.datepart method for getting the dayofweek in the groupby which can be done following way :

var result = ctx.storeitemtransactions
                .groupby(x => sqlfunctions.datepart("weekday", x.transactiondate))
                .select(pr => new hourlygraph
                        { 
                           day = pr.key.tostring(),
                           sales = pr.sum(x => x.quantitysoldtransaction) 
                       });

Related Query

More Query from same tag