Creating table in memory using LINQ to entity

Question

I have this table(A):

With help of LINQ to entites I want to create this table in memory from the SQL table above(B):

Here is my entity:

    private class HelperClass
    {
        public string clientName { get; set; }
        public string authority { get; set; }
        public int objectzsNumber { get; set; }
        public int month { get; set; }
        public int semiannual { get; set; }
        public int quarterly { get; set; }
        public int yearly { get; set; }
    }

Any idea how do I create table B in memory from table A using LINQ to entity?

Basicly your question is "How do I [Pivot data using LINQ](http://stackoverflow.com/questions/963491/pivot-data-using-linq)?" with the restriction that the solution must be compatible with Linq to entities. — Scott Chamberlain, Sep 16 '15 at 21:51
Which BTW, any solution can be made compatible by getting the result for `A` then doing `.AsEnumerable()` before you do any solution that works with Linq to Objects. The Pivot will be done in memory in the client instead of on the SQL server, but it will work. — Scott Chamberlain, Sep 16 '15 at 22:00
don't expect us to do the dirty work... use some time and do it yourself and tell us what you did... — balexandre, Sep 16 '15 at 22:11

score 2 · Answer 1 · answered Sep 17 '15 at 00:41

All you need is GroupBy here. When you say in memory, it means Linq To Object should be used, however it does not really matter the syntax (they are both the same, to get in memory objects call ToList() first):

//suppose your A table is mapped to clients
var result = clients.GroupBy(e=> new { e.ClientName, e.Authority, 
                                       e.ObjectsNumber, e.Frequency})
                    .Select(g => new HelperClass{
                         clientName = g.Key.ClientName,
                         authority = g.Key.Authority,
                         objectsNumber = g.Key.ObjectsNumber,
                         monthly = g.Key.Frequency == "Monthly" ? g.Count() : 0,
                         semiannual = g.Key.Frequency == "SemiAnnual" ? g.Count() : 0,
                         quaterly = g.Key.Frequency == "Quaterly" ? g.Count() : 0,
                         yearly = g.Key.Frequency == "Yearly" ? g.Count() : 0
                     });

Creating table in memory using LINQ to entity

1 Answers1