Aggreagation on Mongo c# driver with not null field does not filter

Viewed 1417

I am doing an aggregation in Mongo and I cannot get the expected results; I want to count those with a null column and those with not null but apparently { "$ne" : ["$RequestedOn", null] } is coming always as true.

I cannot really see where the problem is. This is the data in the collection

/* 1 */
{
    "_id" : ObjectId("56cf03445667a09b17f661ef"),
    "Name" : "User1 Test1",
    "AccountRef" : "AccountRef1",
    "Voucher" : "Voucher1",    
    "Email" : "AccountRef1@server.ie",
    "CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}

/* 2 */
{
    "_id" : ObjectId("56cf034c5667a09b17f661f0"),
    "Name" : "User2 Test2",
    "AccountRef" : "AccountRef2",
    "Voucher" : "Voucher2",
    "Email" : "AccountRef1@server.ie",
    "CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}

/* 3 */
{
    "_id" : ObjectId("56cf03565667a09b17f661f1"),
    "Name" : "User3 Test3",
    "AccountRef" : "AccountRef3",
    "Voucher" : "Voucher3",
    "GuidLink" : "7f079244-d94f-5e4a-8096-59b6df3ef64f",
    "Email" : "AccountRef1@server.ie",
    "CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}

/* 4 */
{
    "_id" : ObjectId("56cf035e5667a09b17f661f2"),
    "Name" : "User4 Test4",
    "AccountRef" : "AccountRef4",
    "Voucher" : "Voucher4",
    "Email" : "AccountRef1@server.ie",
    "RequestedOn" : ISODate("2016-02-29T22:49:20.201Z"),
    "CampaignId" : ObjectId("56c752d439bac5655eec7fb7")
}

And this is how I build the query

var wlUsers=Database.Collection;
var cs = wlUsers.AsQueryable()
.Where(wl => wl.CampaignId == id)
.Select(p=> new { id = p.CampaignId, p.RequestedOn})
.GroupBy(wl => wl.id)
.Select(g => new CampaignStatistics{
    ExistingCustomers = g.Count(),                        
    TotalOrdered = g.Sum(w => w.RequestedOn.HasValue ? 1 :0),
    LastOrder = g.Max(w => w.RequestedOn),
    FirstOrder = g.Min(w => w.RequestedOn),
    Last24hOrders = g.Sum(w => 
        w.RequestedOn > DateTime.Today.AddDays(-1)?1:0),
});

The result is

{
    "ExistingCustomers" : 4,
    "TotalOrdered" : 4,
    "LastOrder" : ISODate("2016-02-29T22:49:20.201Z"),
    "FirstOrder" : ISODate("2016-02-29T22:49:20.201Z"),
    "Last24hOrders" : 4
}

But it should be

{
    "ExistingCustomers" : 4,
    "TotalOrdered" : 1,
    "LastOrder" : ISODate("2016-02-29T22:49:20.201Z"),
    "FirstOrder" : ISODate("2016-02-29T22:49:20.201Z"),
    "Last24hOrders" : 0
}

So the totalOrdered has to be 1 and the 'Last24hOrders' = 0.

Also I am new on Mongo so I'll appreciate any tips advises on how to build these projections

2 Answers
Related