Hi All:

I am attempting to perform a linq group expression and have encountered an exception. To comply with the requirements for this forum post, here's the necessary information:

Linq Code:

protected IList<PerformanceReturnInvestmentVehicleMetaData> PerformanceReturnInvestmentVehicleMetaDataList
        {
            get
            {
                if(this._performanceReturnInvestmentVehicleMetaDataList == null)
                {
                    var query = from privp in this._linqMetaData.PerformanceReturnInvestmentVehiclePortfolio
                                where this._dateRange.Select(month => month.Month).Contains(privp.PerformanceMonth) &&
                                    this._dateRange.Select(year => year.Year).Contains(privp.PerformanceYear)
                                group privp by privp.InvestmentVehicleId into g
                                select new PerformanceReturnInvestmentVehicleMetaData
                                {
                                    InvestmentVehicleId = g.Key,
                                    PercentReturn = g.Average(avg => avg.PercentageReturn),
                                }
                                ;

                    this._performanceReturnInvestmentVehicleMetaDataList = query.ToList();
                }

                return this._performanceReturnInvestmentVehicleMetaDataList;
            }
        }

Similar Code in LinqPad: (Provides the desired results)

void Main()
{
    IList<DateTime> dateTimes = new List<DateTime>();
    dateTimes.Add(new DateTime(2008, 12, 1));
    dateTimes.Add(new DateTime(2009, 1, 1));
    
    var query = from privp in Performance_return_investment_vehicle_portfolio 
    where dateTimes.Select(month => month.Month).Contains(privp.Performance_month) &&
        dateTimes.Select(year => year.Year).Contains(privp.Performance_year)
    group privp by privp.Investment_vehicle_id into result
    select new 
    {
        InvestmentVehicleId = result.Key,
        PercentReturn = result.Average(avg => avg.Percentage_return)
    };
    
    foreach(var i in query.ToList())
    {
        Console.WriteLine(i.InvestmentVehicleId + "\t" + i.PercentReturn);
    }
}

Exception:

 The expression with type 'MemberAccess' couldn't be converted to a SetExpression.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The expression with type 'MemberAccess' couldn't be converted to a SetExpression.

Source Error:

Line 53:                                 ;
Line 54: 
Line 55:                     this._performanceReturnInvestmentVehicleMetaDataList = query.ToList();
Line 56:                 }
Line 57: 


Source File: C:\Projects\GT_Clients\Investure\InvestmentManagement\Code\Investure.Ipm\Investure.Ipm.BizServer\Services\HistoricalReturnService.cs   Line: 55

Stack Trace:

[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) +332
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle) +193
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) +1447
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) +212
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +1424
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2818
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleInMemoryEvalCandidateExpression(InMemoryEvalCandidateExpression expressionToHandle) +109
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +1176
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallContains(MethodCallExpression expressionToHandle) +342
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +651
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2818
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpression(BinaryExpression expressionToHandle) +110
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleBinaryExpression(BinaryExpression expressionToHandle) +125
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpressionBooleanOperator(BinaryExpression expressionToHandle) +36
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2408
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle) +83
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2880
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallWhere(MethodCallExpression expressionToHandle) +387
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) +1653
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) +212
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +1424
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2818
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallGroupBy(MethodCallExpression expressionToHandle) +209
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) +1191
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) +212
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +1424
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2818
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle) +168
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle) +1447
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType) +212
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle) +1424
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) +2818
   SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleExpression(Expression expressionToHandle) +85
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) +319
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +40
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +48
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() +40
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +68
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +369
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +54
   Investure.Ipm.BizServer.Services.HistoricalReturnService.get_PerformanceReturnInvestmentVehicleMetaDataList() in C:\Projects\GT_Clients\Investure\InvestmentManagement\Code\Investure.Ipm\Investure.Ipm.BizServer\Services\HistoricalReturnService.cs:55
   Investure.Ipm.BizServer.Services.HistoricalReturnService.Save(IDataAccessAdapter adapter) in C:\Projects\GT_Clients\Investure\InvestmentManagement\Code\Investure.Ipm\Investure.Ipm.BizServer\Services\HistoricalReturnService.cs:160

LLBLGen Versions: SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll - 2.6.8.1006 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll - 2.6.8.1031 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.6.8.1013

Approx. Desired SQL:

SELECT AVG([t0].[percentage_return]) AS [PercentReturn], [t0].[investment_vehicle_id] AS [InvestmentVehicleId]
FROM [performance_return_investment_vehicle_portfolio] AS [t0]
WHERE ([t0].[performance_month] IN (@p0, @p1)) AND ([t0].[performance_year] IN (@p2, @p3))
GROUP BY [t0].[investment_vehicle_id]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [12]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [2008]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [2009]

DDL For Table:

[performance_return_investment_vehicle_portfolio_id] [int] IDENTITY(1,1) NOT NULL,
    [investment_vehicle_portfolio_id] [int] NOT NULL,
    [investment_vehicle_id] [int] NOT NULL,
    [performance_month] [int] NOT NULL,
    [performance_year] [int] NOT NULL,
    [performance_time_period_id] [int] NOT NULL,
    [percentage_return] [decimal](18, 10) NOT NULL,
    [market_value] [money] NOT NULL,
    [create_date] [datetime] NOT NULL CONSTRAINT [DF_performance_return_investment_vehicle_portfolio_create_date]  DEFAULT (getdate()),
    [create_user_id] [int] NOT NULL,
    [update_date] [datetime] NULL,
    [update_user_id] [int] NULL,

If there is a better way to achieve the same result, please advise. Otherwise could this be a bug?

Thanks!

Please try with the latest build of the runtime libs first.

Hi Frans:

Downloaded the 01212009 Runtime Libs.

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll - 2.6.9.116

The following gets spit out from the <add name="LinqExpressionHandler" value="3"/> trace.

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Investure.Ipm.DataEntities.Generic. EntityClasses.PerformanceReturnInvestmentVehiclePortfolioEntity]).Where(privp => (value(Investure.Ipm.BizServer.Services.HistoricalReturnService)._dateRange.Select(month => month.Month).Contains(privp.PerformanceMonth) && value(Investure.Ipm.BizServer.Services.HistoricalReturnService)._dateRange.Select(year => year.Year).Contains(privp.PerformanceYear))).GroupBy(privp => privp.InvestmentVehicleId).Select(g => new PerformanceReturnInvestmentVehicleMetaData() {InvestmentVehicleId = g.Key, PercentReturn = g.Average(avg => avg.PercentageReturn)})
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException' occurred in SD.LLBLGen.Pro.LinqSupportClasses.NET35.DLL

Then the same exception.

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

Thanks!

I think _dateRange is a local member variable, a collection?

This is the piece of code which causes the problem: this._dateRange.Select(month => month.Month)

You're mixing linq to objects here with linq to llblgen pro (database), and it's not really clear for the linq provider what to do here, as the Select call is simply part of the Expression tree but shouldn't be part of the query ran on the DB.

As you can see in the stacktrace, the Select method is seen as an in-memory candidate and should be evaluated as-is, however somewhere this goes wrong. I'll look into that.

You can easily solve this problem yourself: execute the in-memory parts outside the query first:

List<int> months = this._dateRange.Select(month => month.Month).ToList();
List<int> years = this._dateRange.Select(year => year.Year).ToList();
var query = from privp in this._linqMetaData.PerformanceReturnInvestmentVehiclePortfolio
            where months.Contains(privp.PerformanceMonth) &&
                years.Contains(privp.PerformanceYear)
            group privp by privp.InvestmentVehicleId into g
            select new PerformanceReturnInvestmentVehicleMetaData
            {
                InvestmentVehicleId = g.Key,
                PercentReturn = g.Average(avg => avg.PercentageReturn),
            }
            ;

this._performanceReturnInvestmentVehicleMetaDataList = query.ToList();

(haven't tried it but it should work properly).

I'll see if I can track down the issue with this in-memory candidate.

(edit) I can reproduce the exception here. Looking into a way to work around that.

please see the attached dll for the fix of the error. You still should be careful with mixing linq to object queries with linq queries which have to run on the db as it might be the query at hand might not be convertable at runtime.

Ahhh... I would not have spotted that, but I see where local linq mixed with linq to llblgen might create some code confusion. Thanks for making me aware, and thanks for providing a solution so promptly!!!