mSchmidt
asked on
Linq - Add custom calculated property based on other properties
Hi i have a DataContext where i have tabel called tbl_ingredient.
this table holds alot of values.
however one of the values i need is calculated using some of the other fields.
I tried adding the property in another file as a partial class compiles well but gives me an error at runtime:
"Could not translate expression "
Cant i create a property like this ?
this table holds alot of values.
however one of the values i need is calculated using some of the other fields.
I tried adding the property in another file as a partial class compiles well but gives me an error at runtime:
"Could not translate expression "
Cant i create a property like this ?
public partial class tbl_ingredient
{
public decimal ingredient_price_pr
{
get { return this._ingredient_price / this._ingredient_quantity; }
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just tried your idea with early execution and then it works
THIS WORKS
RadGrid_Details.DataSource = que2.ToList().Concat(que.T oList());
THIS DOESNT
RadGrid_Details.DataSource = que2.Concat(que);
THIS WORKS
RadGrid_Details.DataSource
THIS DOESNT
RadGrid_Details.DataSource
Can you give me the code for your two queries? I suspect that the reason you are getting the exception is due to what I mentioned above. Where() isn't the only statement that will cause the behavior. It all has to do with how Linq to Sql translates each of the Linq extension methods to SQL. I suspect something in that translation is stumbling on a reference to your added property.
If I can see both queries, I should be able to analyze them and tell you what in which query is causing the hiccup.
If I can see both queries, I should be able to analyze them and tell you what in which query is causing the hiccup.
ASKER
Sture here you go, as you can see my current impentation usesearly execution
private void rebindDetails()
{
var que =
(from v in myData.tbl_calculation_det ails
join a in myData.tbl_calculations on v.calculation_calculation equals a.calculation_id
where v.calculation_id == selectedID
select new
{
id = v.id,
internt = a.calculation_number,
eksternt = "Kalkyle",
tekst = a.calculation_name,
amount = v.calculation_amount,
vGroup = v.calculation_group_name,
svind = (decimal)0,
prcPr = (decimal)200,
brand = "-",
unit = a.tbl_unit.unit_abbreviati on
}
);
var que2=
(
from v in myData.tbl_calculation_det ails
join a in myData.tbl_ingredients on v.calculation_ingredient equals a.ingredient_id
where v.calculation_id == selectedID
select new
{
id = v.id,
internt = a.ingredient_itemnumber,
eksternt = a.ingredient_itemnumber_cr editor.ToS tring(),
tekst = a.ingredient_name,
amount = v.calculation_amount,
vGroup = v.calculation_group_name,
svind = a.ingredient_loss,
prcPr = a.ingredient_price_pr,
brand = a.ingredient_brand,
unit = a.tbl_unit.unit_abbreviati on
}
);
RadGrid_Details.DataSource = que2.ToList().Concat(que.T oList()).O rderBy(a=> a.brand );
RadGrid_Details.Rebind();
}
private void rebindDetails()
{
var que =
(from v in myData.tbl_calculation_det
join a in myData.tbl_calculations on v.calculation_calculation equals a.calculation_id
where v.calculation_id == selectedID
select new
{
id = v.id,
internt = a.calculation_number,
eksternt = "Kalkyle",
tekst = a.calculation_name,
amount = v.calculation_amount,
vGroup = v.calculation_group_name,
svind = (decimal)0,
prcPr = (decimal)200,
brand = "-",
unit = a.tbl_unit.unit_abbreviati
}
);
var que2=
(
from v in myData.tbl_calculation_det
join a in myData.tbl_ingredients on v.calculation_ingredient equals a.ingredient_id
where v.calculation_id == selectedID
select new
{
id = v.id,
internt = a.ingredient_itemnumber,
eksternt = a.ingredient_itemnumber_cr
tekst = a.ingredient_name,
amount = v.calculation_amount,
vGroup = v.calculation_group_name,
svind = a.ingredient_loss,
prcPr = a.ingredient_price_pr,
brand = a.ingredient_brand,
unit = a.tbl_unit.unit_abbreviati
}
);
RadGrid_Details.DataSource
RadGrid_Details.Rebind();
}
Hmmm...Am I correct to assume that the only calculated field in these 2 queries is a.ingredient_price_pr?
I'm trying to reproduce the behavior in some other data, and so far am not having a problem.
Assuming this is the only calculated field, have you tried commenting out the prcPr from your anonymous type in both queries...and run it again without the ToList() calls? Does it still fail?
I'm trying to reproduce the behavior in some other data, and so far am not having a problem.
Assuming this is the only calculated field, have you tried commenting out the prcPr from your anonymous type in both queries...and run it again without the ToList() calls? Does it still fail?
ASKER
Yep tried that
Then it doesnt fail :)
Then it doesnt fail :)
So, it appears that in cases where a computed field is used in a final/tail Select() statement, Linq to SQL is smart enough to just do the proper column selection (in your case selecting both columns that make up your computed field), and then reference the computed field locally.
However, in the case of Concat(), the SQL generated includes a UNION (not surprising) and the Linq to SQL provider apparently chokes if either side of the UNION contains a computed field. This makes sense when you look at the generated SQL. The Linq provider does a select (this is the final select where computed fields would work) against a union of 2 other selects...i.e. both of your queries. If either of those "inner" queries contains a computed field - you'll see the exception.
So, at this point, I believe your choices are:
1) Do the ToList() - with the risks aforementioned.
2) Change the select in the offending query to do the computation as part of the select. See snippet below. This doesn't read as elegantly...and decreases encapsulation and reuse...but will work.
I'm giving some thought to a more elegant solution that would involve delaying the reference to the computed field until the very end...but it may be difficult since your source data of both queries is shaped differently. I'll let you know.
I hope this helps.
However, in the case of Concat(), the SQL generated includes a UNION (not surprising) and the Linq to SQL provider apparently chokes if either side of the UNION contains a computed field. This makes sense when you look at the generated SQL. The Linq provider does a select (this is the final select where computed fields would work) against a union of 2 other selects...i.e. both of your queries. If either of those "inner" queries contains a computed field - you'll see the exception.
So, at this point, I believe your choices are:
1) Do the ToList() - with the risks aforementioned.
2) Change the select in the offending query to do the computation as part of the select. See snippet below. This doesn't read as elegantly...and decreases encapsulation and reuse...but will work.
I'm giving some thought to a more elegant solution that would involve delaying the reference to the computed field until the very end...but it may be difficult since your source data of both queries is shaped differently. I'll let you know.
I hope this helps.
Change:
prcPr = a.ingredient_price_pr,
To:
prcPr = a._ingredient_price / a._ingredient_quantity
ASKER
And if i execute these queries without Concat there is no problem, however using concat throws me the error, any idea why this is ?