c# - How can I improve this Linq query (a kind of pivoting)? -

i have entity:

public  class delivery     {         public int id { get; set; }         public int productid { get; set; }         public int customerid { get; set; }         public int quantity { get; set; }         public datetime deliverydate { get; set; }         public virtual product product { get; set; }         public virtual customer customer { get; set; }     } 

i want display deliveries week, write query:

    public override ienumerable getmodeldata(applicationdbcontext context)             {                 return context.deliveries.groupby(x => x.product).select(x => new                 {                     id=x.key.id,                     product = x.key.name,                     wk1 =(int?) x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 1).sum(a => a.quantity), ... ... ...                     wk46 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 46).sum(a => a.quantity),                     wk47 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 47).sum(a => a.quantity),                     wk48 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 48).sum(a => a.quantity),                     wk49 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 49).sum(a => a.quantity),                     wk50 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 50).sum(a => a.quantity),                     wk51 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 51).sum(a => a.quantity),                     wk52 = (int?)x.where(a => sqlfunctions.datepart("wk", a.deliverydate) == 52).sum(a => a.quantity),                 }).tolist();             } 

is possible expected object smaller query?

i have in moment around 100 samples row in delivery database table, , have filling way data not best one.

the query working, want know if have in mind better way write kind of query.

the way make linq query shorter see generate selector programmatically.

however there way make generated sql query shorter (and more efficient). instead of where(condition).sum(expr) construct not translate well, use conditional sum, i.e. sum(condition ? expr : null) produces better sql:

wk1 = x.sum(a => sqlfunctions.datepart("wk", a.deliverydate) == 1 ? a.quantity : (int?)null), wk2 = x.sum(a => sqlfunctions.datepart("wk", a.deliverydate) == 2 ? a.quantity : (int?)null), ...