linq2db: Error while grouping after a join

I see the following exception

Exception message: 'p.ProductDescription' cannot be converted to SQL.
Stack trace:    in LinqToDB.Linq.Builder.SelectContext.GetMemberExpression(MemberInfo member, Boolean add, Type type, Expression sourceExpression) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 1163
   in LinqToDB.Linq.Builder.SelectContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 344
   in LinqToDB.Linq.Builder.SelectContext.ConvertToIndexInternal(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 556
   in LinqToDB.Linq.Builder.SelectContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 432
   in LinqToDB.Linq.Builder.SubQueryContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SubQueryContext.cs:riga 100
   in LinqToDB.Linq.Builder.SelectContext.<>c__DisplayClass37_0.<ConvertToSql>b__0(IBuildContext ctx, Expression ex, Int32 l) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 302
   in LinqToDB.Linq.Builder.SelectContext.ProcessScalar[T](Expression expression, Int32 level, Func`4 action, Func`1 defaultAction) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 903
   in LinqToDB.Linq.Builder.SelectContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 299
   in LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:riga 856
   in LinqToDB.Linq.Builder.ExpressionBuilder.ConvertExpressions(IBuildContext context, Expression expression, ConvertFlags queryConvertFlag) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:riga 661
   in LinqToDB.Linq.Builder.SelectContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 281
   in LinqToDB.Linq.Builder.SelectContext.ConvertToIndexInternal(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 479
   in LinqToDB.Linq.Builder.SelectContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 432
   in LinqToDB.Linq.Builder.FirstSingleBuilder.FirstSingleContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\FirstSingleBuilder.cs:riga 276
   in LinqToDB.Linq.Builder.ExpressionBuilder.GetSubQuery(IBuildContext context, MethodCallExpression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:riga 238
   in LinqToDB.Linq.Builder.ExpressionBuilder.GetSubQueryContext(IBuildContext context, MethodCallExpression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.QueryBuilder.cs:riga 310
   in LinqToDB.Linq.Builder.ExpressionBuilder.GetSubQueryExpression(IBuildContext context, MethodCallExpression expr, Boolean enforceServerSide) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.QueryBuilder.cs:riga 327
   in LinqToDB.Linq.Builder.ExpressionBuilder.TransformExpression(IBuildContext context, Expression expr, Boolean enforceServerSide) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.QueryBuilder.cs:riga 260
   in LinqToDB.Linq.Builder.ExpressionBuilder.<>c__DisplayClass74_0.<BuildExpression>b__0(Expression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.QueryBuilder.cs:riga 49
   in LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func) in C:\projects\linq2db\Source\LinqToDB\Expressions\Extensions.cs:riga 1353
   in LinqToDB.Expressions.Extensions.<>c__DisplayClass34_0.<Transform>g__Modify|1(MemberBinding b) in C:\projects\linq2db\Source\LinqToDB\Expressions\Extensions.cs:riga 1500
   in LinqToDB.Expressions.Extensions.Transform2[T](ICollection`1 source, Func`2 func) in C:\projects\linq2db\Source\LinqToDB\Expressions\Extensions.cs:riga 1321
   in LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func) in C:\projects\linq2db\Source\LinqToDB\Expressions\Extensions.cs:riga 1546
   in LinqToDB.Linq.Builder.ExpressionBuilder.BuildExpression(IBuildContext context, Expression expression, Boolean enforceServerSide) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.QueryBuilder.cs:riga 49
   in LinqToDB.Linq.Builder.SelectContext.BuildExpression(Expression expression, Int32 level, Boolean enforceServerSide) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 112
   in LinqToDB.Linq.Builder.SelectContext.BuildQuery[T](Query`1 query, ParameterExpression queryParameter) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:riga 65
   in LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]() in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:riga 159
   in LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:riga 280
   in LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:riga 233
   in LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache) in C:\projects\linq2db\Source\LinqToDB\Linq\ExpressionQuery.cs:riga 84
   in LinqToDB.Linq.ExpressionQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in C:\projects\linq2db\Source\LinqToDB\Linq\ExpressionQuery.cs:riga 179
   in System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   in System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   in Import2TSS.Alerting.ComputPLInternal(DataConnection db, Action`1 Log, Nullable`1 cargoid) in C:\compliance\MAR_Reporting_API_Sourcing\OilPhysical_ImportCsv2DB\DB\Alerting.cs:riga 635
   in Import2TSS.Alerting.Detect(Action`1 Log, String ConnName, DateTime asofDate) in C:\compliance\MAR_Reporting_API_Sourcing\OilPhysical_ImportCsv2DB\DB\Alerting.cs:riga 844

Steps to reproduce

after the code

var queryTotCosts =
            from c in db.GetTable<Cost>()
            join n in db.GetTable<NominationValid>()
                on new
                {
                    c.CargoID,
                    c.DeliveryID,
                    c.DealNumber,
                    c.ParcelID
                } equals new
                {
                    n.CargoID,
                    n.DeliveryID,
                    DealNumber = n.DeliveryDealNumber > 0 ? n.DeliveryDealNumber : n.ReceiptDealNumber,
                    ParcelID = n.DeliveryDealNumber > 0 ? n.DeliveryParcelID : n.ReceiptParcelID
                }
            join t in db.GetTable<TradeValid>()
                on new { c.DealNumber, c.ParcelID } equals new { t.DealNumber , t.ParcelID }
            where c.OperationType != Cost.Operations.Delete.GetEnumDescription() && 
                  (cargoid == null || c.CargoID == cargoid)
            group new { c.FeePV, n.ProductDescription, t.PortfolioName } 
                by new { n.CargoID, c.Currency ,c.PayRec, n.TitleTranfertDate} into cargoGr
            select new CostTotal
            {
                CargoID = cargoGr.Key.CargoID,
                PayRec = cargoGr.Key.PayRec,
                TotAmount = cargoGr.Sum(cst => cst.FeePV),
                Currency = cargoGr.Key.Currency,
                TitleTransferDate = cargoGr.Key.TitleTranfertDate,
                Commodity = cargoGr.FirstOrDefault().ProductDescription,
                Portfolio = cargoGr.FirstOrDefault().PortfolioName
            };
var totCosts = queryTotCosts.ToArray();

Environment details

linq2db version: 2.5.4.0 Database Server: Ms Sql 2016 Operating system: Windows 7 Framework version: *.NET Framework 4.5.2

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 33 (13 by maintainers)

Most upvoted comments

~Maybe you have to add booking_company into partition?~

Good 😃