HnD | Group by and Sum in VB, Page 1

Group by and Sum in VB

Posts

Antonio Li Causi
User

Posts: 15
Joined: 09-Oct-2008

# Posted on: 17-Nov-2008 00:06:22

Sorry, probably a stupid question... But I'm not able to let a simple Sum work on VB.NET...


Dim Q =  From R In Md.GoogleDataRow _
               Group R By R.Campagna Into Campagne = Group _
               Select New With {Campagna, .Impressioni = Campagne.Sum(Function(o) o.StatsImpressioni)}

I have tried in all the way.... but ever same error...


        Dim Q = From R In Md.GoogleDataRow _
                Group R By R.Campagna Into Campagne = Group _
                Select Campagna, Impressioni = Campagne.Sum(Function(o) o.StatsImpressioni)

and.. even...


        Dim Q = From R In Md.GoogleDataRow _
                Group R By R.Campagna Into Campagne = Group _
                Select Impressioni = Campagne.Sum(Function(o) o.StatsImpressioni.Value)

The expression with type 'MemberAccess' couldn't be converted to a SetExpression.

The stack...


[ORMQueryConstructionException: The expression with type 'MemberAccess' couldn't be converted to a SetExpression.]
   SD.LLBLGen.Pro.LinqSupportClasses.LinqUtils.CoerceToSet(Expression toCoerce, MappingTracker trackedMappings, Boolean throwOnFailure) +401
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleAggregateFunctionMethodCall(MethodCallExpression expressionToHandle, AggregateFunction function) +418
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) +2169
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) +297
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +1973
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +3401
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +129
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpressionList(ReadOnlyCollection`1 listToHandle) +208
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleNewExpression(NewExpression expressionToHandle) +123
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleNewExpression(NewExpression expressionToHandle) +982
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +3549
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +129
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle) +115
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +3475
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +129
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle) +605
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) +1988
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) +297
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +1973
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +3401
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +129
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) +383
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +71
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +71
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() +75
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +75

I have seen the syntax in C#....


            var result = from o in metaData.Orders
                         where o.OrderDate > new DateTime(1998,1,1)
                         group o by o.CustomerId into g
                         select new { g.Key, cnt = g.Count(), total = g.Sum(a => a.OrderId)};

On a different thread... and I think my VB syntax should be ok... but.... Obviously I'm doing something wrong!

Thanks as ever!

Antonio Li Causi
User

Posts: 15
Joined: 09-Oct-2008

# Posted on: 17-Nov-2008 00:59:57

To be more sure...

I have done some tests with my poor test DB...

The same I have use during my last post on a different thread...

and like the other post, the problem seems is in The VB sytax....

infact... on c# the following work...


LinqMetaData Md = new LinqMetaData();
var q = from P in Md.Person group P by P.DateOfBirth into g select new {g.Key, Nr = g.Count() };

The same in VB is not working!!!


Dim Md As New LinqMetaData()
Dim q = From P In Md.Person Group P By P.DateOfBirth Into PG = Group _
             Select DateOfBirth, Nr = PG.Count

Even here same error...

The expression with type 'MemberAccess' couldn't be converted to a SetExpression.

And even the stack...


  in SD.LLBLGen.Pro.LinqSupportClasses.LinqUtils.CoerceToSet(Expression toCoerce, MappingTracker trackedMappings, Boolean throwOnFailure)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleAggregateFunctionMethodCall(MethodCallExpression expressionToHandle, AggregateFunction function)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpressionList(ReadOnlyCollection`1 listToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleNewExpression(NewExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleNewExpression(NewExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle)
   in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
   in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
   in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
   in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   in System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

Hope it help to understand what i'm doing wrong... Thanks.

daelmo
Support Team

Posts: 8245
Joined: 28-Nov-2005

# Posted on: 17-Nov-2008 05:57:07

Hi Antonio,

Linq and VB sometimes are a really pain . You should read some VB notes and the way you could achieve the groupBy in VB: read this.

And, when you experiment an issue, please follow these instructions so we can help you better

David Elizondo | LLBLGen Support Team

Otis
LLBLGen Pro Team

Posts: 39861
Joined: 17-Aug-2003

# Posted on: 17-Nov-2008 09:30:23

If a field is a nullable field (Nullable(Of T)), then add '.HasValue' in VB.

Frans Bouma | Lead developer LLBLGen Pro

Antonio Li Causi
User

Posts: 15
Joined: 09-Oct-2008

# Posted on: 17-Nov-2008 11:50:10

Just to make an ex..

On documentation there is no example on some thing like...

From A in AppleProduction 
Group A by A.Type into AppleTypeGroup = Group
Select Type, AppleTypeGroup.Sum(function(o) o.FieldToSum)

With INTO and a SELECT from THE GROUP and with SUM

but only...

 per country-city the # of customers is determined.
From c In metaData.Customer _
Group By c.Country, c.City _
Into Count() _
Select Country, City, Count

Or...

' returns set of objects which contain per country the customers in a hierarchy
Dim q = From c In metaData.Customer _
Group c By c.Country Into G = Group

Not so useful for thus case...

I repeat the following breaks.

From A in Md.AppleProduction _ Group A by A.Type into AppleTypeGroup = Group _ Select Type, AppleTypeGroup.Sum(function(o) o.FieldToSum)

please, check: Is my syntax wrong? If it is provide an example... other way.... F5 and go on debug...

Thanks.

Walaa
Support Team

Posts: 14993
Joined: 21-Aug-2005

# Posted on: 17-Nov-2008 17:07:06

I found the following article about possible syntaxes of writing a Linq GoupBy with VB.

GROUP BY and HAVING (Bill Horst)