Group By and multiplication
Joined: 20-Sep-2011
Joined: 20-Sep-2011
Joined: 20-Sep-2011
Joined: 20-Sep-2011
Yes, it was that. In you original code, LINQ2LLBL can't understand Math.Exp/Log as a DB function so it's used as an in-memory one. The problem is that such functions involves IQueryable elements, so it fails on that. You have to make custom function mappings for the functions that are not built-in mapped. You can find more info about this in the documentation.
I created the mappings for you. So put this class into your WinForms project:
using SD.LLBLGen.Pro.ORMSupportClasses;
namespace GroupByMultiplicationTest
{
public class MyFunctions
{
// EXP(x)
public static double Exp(double x)
{
// empty by default, as it's just here to make the query compile. The call is converted to a SQL function.
return double.MinValue;
}
// LOG(x)
public static double Log(double x)
{
// empty by default, as it's just here to make the query compile. The call is converted to a SQL function.
return double.MinValue;
}
}
/// Class which defines the custom mapping between MyFunctions.Lile and MySql LIKE function
public class MyFunctionMappings : FunctionMappingStore
{
public MyFunctionMappings() : base()
{
// EXP(x)
this.Add(new FunctionMapping(typeof(MyFunctions), "Exp", 1, "EXP({0})"));
// LOG(x)
this.Add(new FunctionMapping(typeof(MyFunctions), "Log", 1, "LOG({0})"));
}
}
}
... and into your MainForm.cs:
var adapter = new DataAccessAdapter();
LinqMetaData metaData = new LinqMetaData(adapter);
metaData.CustomFunctionMappings = new MyFunctionMappings();
var q1 =
from odd in metaData.OrderDetailDiscount
group odd by odd.FkOrderDetailId into oddGroup
select new
{
Id = oddGroup.Key,
Discount = MyFunctions.Exp(oddGroup.Sum(o => 1 - MyFunctions.Log(o.Discount)))
};
var q2 =
from od in metaData.OrderDetail
where od.Id == 1
select new
{
Id = od.Id,
Price = od.Price,
FinalDiscount = (from x in q1 where x.Id == od.Id select x.Discount).Single()
};
var results = q2.ToList();
Hope helpful 
Looking again into your code and your data, I have some additional comments:
- About the data you have on DB, you should divide the discount by 100 to make it a percentage.
- I think you don't need the GROUP BY because you already are aggregating on the correlated row.
- I think it's better to join at the outer query. My guess is that the SQL is equivalent. I leave this test to you.
- To make things easier, you also could use a single function mapping to handle all the expression.
New example:
New FunctionMappings
using SD.LLBLGen.Pro.ORMSupportClasses;
namespace GroupByMultiplicationTest
{
public class MyFunctions
{
public static double CalculateAggregateDiscount(double x)
{
return double.MinValue;
}
}
public class MyFunctionMappings : FunctionMappingStore
{
public MyFunctionMappings() : base()
{
this.Add(new FunctionMapping(typeof(MyFunctions), "CalculateAggregateDiscount", 1, "EXP(SUM(LOG(1 - {0}/100)))"));
}
}
}
Usage
using (var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
metaData.CustomFunctionMappings = new MyFunctionMappings();
var results =
(from od in metaData.OrderDetail
join odd in metaData.OrderDetailDiscount on od.Id equals odd.FkOrderDetailId
where od.Id == 1
select new OrderDetailProjection
{
Id = od.Id,
Price = od.Price,
TotalDiscount = MyFunctions.CalculateAggregateDiscount(odd.Discount)
})
.ToList();
}
Generated SQL
SELECT
`LPA_L1`.`Id`,
`LPA_L1`.`Price`,
EXP(SUM(LOG(1 - `LPA_L2`.`Discount`/100))) AS `TotalDiscount`
FROM ( `order_detail` `LPA_L1`
INNER JOIN `order_detail_discount` `LPA_L2` ON `LPA_L1`.`Id` = `LPA_L2`.`FK_order_detail_Id`)
WHERE ( ( ( ( ( `LPA_L1`.`Id` = 1)))))
Joined: 20-Sep-2011
sybjeb wrote:
1) About the data you have on DB, you should divide the discount by 100 to make it a percentage => the discount stored in the database is already divided so I dont need to divide it by 100 in the query. You could not know it because I didn't tell it.
Ok, I supposed it because of the data you send in the script, but it's ok, It was only an observation.
sybjeb wrote:
2) I think you don't need the GROUP BY because you already are aggregating on the correlated row => I thought about this but didn't achieve to write the query without it (see 4)
Ok.
sybjeb wrote:
4) To make things easier, you also could use a single function mapping to handle all the expression. => OK but I'm interesting to know how I can write the same in Linq using mapping of Exp and Log and not CalculateAggregateDiscount ? In fact, to be able to use Sum method, I didn't see another way than using a GROUP.
Ok. Then you should look to my previous post (just before the last one). Good luck