Not Able to perform group by using dynamically generated group and id selector expression

[Ravi Singh]

Hi ,

I have a use case where my idselector expression and Group Expression are generated at run time. but i am unable to use them. below is the code

  db.GetCollection<BsonDocument>("CollectionName")

                            .Aggregate()

                            .Group(Bakerc178f660479a4db4ba176dc62b7ec21c.idfunc, Bakerc178f660479a4db4ba176dc62b7ec21c.func).ToListAsync()

Below is the dynamically generated Expression funcs

public static class Bakerc178f660479a4db4ba176dc62b7ec21c{

                    public static readonly Expression<Func<IGrouping<dynamic, BsonDocument>, dynamic>> func = key => new {MyKey= key.Key };

                    public static readonly Expression<Func<BsonDocument, dynamic>> idfunc = key =>new { Year=(double)key["Year"]} ;

}

On Executing i get following error

{"Value type of serializer is <>f__AnonymousType1`1[[System.Double, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] and does not match member type System.Object.\r\nParameter name: serializer"}

Please note that above works  if i put those funcs directly in groupby and idselector as following

db.GetCollection<BsonDocument>("CollectionName")

                            .Aggregate()

                            .Group(key => new { Year = (double)key["Year"] }, key => new { MyKey = key.Key }).ToListAsync();

but i can't use this as my expressions are generated dynamically.  Let me know how to handle this.

Many Thanks

Ravinder Singh

 

[Craig Wilson]

Hi Ravi,

The dynamic type is not legal to use in Expressions. This is not a driver limitation, but a C# limitation. There are things you can do with reflection and manually building up an expression tree to provide to the driver, but that will be difficult. My suggestion is that if your data is going to be dynamically built, then you should use a BsonDocument instead.

Craig

[Ravi Singh]

Hi Craig,

Thanks for your reply. i am using BsonDocument only. but my UseCase is following.

1.All the data is stores as multiple collections inside DB as BsonDocuments.  

2.Users can selecting which fields they want to choose and which aggregation they want to perform(sum,count,avg) sort of BI capabilities.

Let me know how i can handle this. one option  i have is to dynamically generate this method using Roslyn Compiler capabilities like http://www.tugberkugurlu.com/archive/compiling-c-sharp-code-into-memory-and-executing-it-with-roslyn 

but i don't think that would be optimal.

Thanks

Much Appreciated
Ravi

[Craig Wilson]

If you are already using BsonDocument, then you should just build up your aggregation with a BsonDocument.

new BsonDocument("$project", new BsonDocument

{

  { myDynamicFieldName, "awesome" }

});

[Ravi Singh]

Can you throw some more light on how to achieve custom created expressions e.g. [Field1]*[Field2]/([Field3] +[Fields4]) and can be more complex in nature using BSON document using projections etc?  

On Monday, September 14, 2015 at 3:10:29 PM UTC+5:30, Ravi Singh wrote:

[Craig Wilson]

Hi Ravi,

I'm really not sure what you are asking, so I'm going to answer based on what I think you are asking.

This is the JSON for the operation... Sub in your intended (or user defined) field names where appropriate.

var json = "{result: { $div: [ {$mult: ['$Field1', '$Field2'] }, {$add: ['$Field3', '$Field4']} ] } }";

Basically, you simply build up the document using the expressions here: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/. You can use BsonDocument if you want, but with something complete like this, it might be easier to build up a JSON string and use string interpolation or string formatting.

Craig

[Ravi Singh]

Thanks Craig i would try and implement that, but that means i need to take care about operator precedence and all that if i build that Json.

Can you give me one more hint on how to achieve different sum values in a single group by clause e.g i am trying this

var collectionlist = db.GetCollection<BsonDocument>(result.Collection)

                        .Aggregate()

                        //.Group(key => new { Year = (double)key["Year"] }, key => new { MyKey = key.Key }).ToListAsync();

                        //.Group(BsonHelper.CreateFunc(lambda), BsonHelper.CreateGroupExpressionFunc(groupLambdaExpression)).ToListAsync();

                        //.Match(BsonHelper.CreateFilter(filter))

                        .Group(x => x["Category"], key => new

                        {

                            Category = key.Key,

                            Sum2014 = key.Where(x => (double) x["Year"] == 2014.00).Sum(z => (double) z["Value"]),

                            Sum2015 = key.Where(x => (double)x["Year"] == 2015.00).Sum(z => (double)z["Value"])

                        })

Intent here i so find out sum of values for both 2014 and 2015 for same category in different columns but this is giving me error like {"get_Item of type MongoDB.Bson.BsonValue is an unsupported method in a $project or $group pipeline operator."}.  

Let me know if i need to have separate method calls to get that data and then do the join?.

Thanks Again

Ravi

On Monday, September 14, 2015 at 3:10:29 PM UTC+5:30, Ravi Singh wrote:

[Craig Wilson]

A Grouping statement like this doesn't support a Where expression. However, you can do this differently...

Sum2014 = key.Sum(x => (double)x["Year"] == 2014 ? (double)x["Value"] : 0)

And then the same thing for 2015...

[Ravi Singh]

Thanks a Lot it works.

On Monday, September 14, 2015 at 3:10:29 PM UTC+5:30, Ravi Singh wrote: