question about agregation (group by) C# driver

[Elfond Anatoly]

hi all .
i need to agregate a count on this query
  var result = from ri in collection.AsQueryable<UserCardCount>()
                         where ri.InsertedDate > dateFrom && ri.InsertedDate < dateTo
                         select ri;

how can i use group by ? cant find one working example on whole net :(

[craiggwilson]

It's not very  clear what you are wanting.  Do you simply want 1) the number of documents that match your query, or 2) a count of each grouping of documents in an unspecified query?

Regarding number 1, simply add a .Count() to it.

var result = ...

var count = result.Count();

[Elfond Anatoly]

lets say each customer has his own id
i want to know how many rows it has under the selected dates
 the result must be
list of
{"id":21,"count":300},....

something like that
thanks for you attention :)

[craiggwilson]

How about you post your current schema and then post the result you want.  That would make this easier...

[Elfond Anatoly]

that's what i did :

i have a structure

customerId , dateInserted ,.. another fields that are not relevant in this case

i want to select customerId ,count-aggregated field of how many records between selected date

the result i want is

customerId: 123 , count:12
customerId:32, count:343 and so on

[Elfond Anatoly]

On Monday, August 13, 2012 10:49:47 AM UTC+3, Elfond Anatoly wrote:

that's what i did :

i have a structure

customerId , dateInserted ,.. another fields that are not relevant in this case

i want to select customerId ,count-aggregated field of how many records between selected date

the result i want is

customerId: 123 , count:12
customerId:32, count:343 and so on

 in a collection i have :
id:23;dateInserted:2012-08-12
id:23;dateInserted:2012-08-12
id:23;dateInserted:2012-08-12
id:23;dateInserted:2012-08-12

so result will be

id:23 ; count:4


 

[Elfond Anatoly]

On Monday, August 13, 2012 10:51:30 AM UTC+3, Elfond Anatoly wrote:

On Monday, August 13, 2012 10:49:47 AM UTC+3, Elfond Anatoly wrote:

that's what i did :

i have a structure

customerId , dateInserted ,.. another fields that are not relevant in this case

i want to select customerId ,count-aggregated field of how many records between selected date

the result i want is

customerId: 123 , count:12
customerId:32, count:343 and so on

 in a collection i have :
id:23;dateInserted:2012-08-12
id:23;dateInserted:2012-08-12
id:23;dateInserted:2012-08-12
id:23;dateInserted:2012-08-12

id:25;dateInserted:2012-08-12
id:25;dateInserted:2012-08-12
id:27;dateInserted:2012-08-12
id:27;dateInserted:2012-08-12

so result will be

id:23 ; count:4

id:25;count:2
id:27;count2

[craiggwilson]

Ok.  Yeah, you didn't use customerid when I asked you to post your current schema and desired output, so I wanted to make sure we were on the same page.  

You cannot do a group by in linq for this and will need to use either the group command or the aggregation framework.  You can see documentation on Grouping here, and the example there is almost exactly your situation:  http://www.mongodb.org/display/DOCS/Aggregation .  Simply replace the csum references with a counter.

I'd encourage you to read through the aggregation framework as it will become the preferred method for doing aggregation in the future.

[Elfond Anatoly]

thank you Craig i found how to do it with javascript commands(strings) in c# code :((( its a bit ugly

  var query = Query.And(
                    Query.GTE("InsertDate", DateTime.Today.AddDays(-14)),
                    Query.LT("InsertDate", DateTime.Today),
                    Query.EQ("ItemValue", 102)
                );

            var map = @"function() {
                            var key = { id: this.PositionId,
                                        year : this.InsertDate.getYear(),
                                        month : this.InsertDate.getMonth(),
                                        day : this.InsertDate.getDate()}
                            emit(key, { count : 1 });}";
           
            var reduce = @"function(key, emits) {
                            total = 0;
                            for (var i in emits) {
                                total += emits[i].count;
                            }
                            return { count : total };}";

            var result = collection.MapReduce(query, map, reduce);
            var mr = result.GetResults().ToList();


the question is - is it any possibility to write it in Linq just to prevent a string code  ?
.

[craiggwilson]

Not right now, as I previously said.  We will be working to bring the aggregation framework into our linq provider, but that feature has not been started.

[Elfond Anatoly]

thanks alot Craig