Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Linq - Add custom calculated property based on other properties

Posted on 2008-10-19
8
Medium Priority
?
3,858 Views
Last Modified: 2013-11-11
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 ?

public partial class tbl_ingredient
    {
        public decimal ingredient_price_pr
        {
            get { return this._ingredient_price / this._ingredient_quantity; }
        }
 
    }

Open in new window

0
Comment
Question by:mSchmidt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 4

Accepted Solution

by:
novynov earned 2000 total points
ID: 22752266
With Linq to SQL, you can do something like the above, as long you only reference this property in statements like the Select() statement - basically just selecting/reading the value. You would not be able to use it in a Where() or similar statement that will be translated into a SQL WHERE clause, since the LINQ to SQL provider has no translation for that expression into SQL - thus the exception you are getting.

That said, there is a way to do a Where() using this property. It would require that you force early execution of the Linq to SQL expression by calling something like ToList() on the query. Once this is done, you are no longer operating with the Linq to SQL provider, you are now in the land of Linq to Objects...and doing a Where() with that property against the returned list of objects is completely valid. The downside is that you will get early execution of the SQL...even if you never iterate the collection.

Let me know if you need more info, or are still having problems.

0
 

Author Comment

by:mSchmidt
ID: 22752321
hmm iam actually not using it in a where statement, however i was using it within a query which i CONCAT with another query.
And if i execute these queries without Concat there is no problem, however using concat throws me the error, any idea why this is ?
0
 

Author Comment

by:mSchmidt
ID: 22752327
Just tried your idea with early execution and then it works

THIS WORKS
RadGrid_Details.DataSource = que2.ToList().Concat(que.ToList());

THIS DOESNT
RadGrid_Details.DataSource = que2.Concat(que);
0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 
LVL 4

Expert Comment

by:novynov
ID: 22753095
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.
0
 

Author Comment

by:mSchmidt
ID: 22753111
Sture here you go, as you can see my current impentation usesearly execution

private void rebindDetails()
        {
            var que =
                (from v in myData.tbl_calculation_details
                 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_abbreviation  
                 }
                      );
                     
             var que2=
                      (
                          from v in myData.tbl_calculation_details
                          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_creditor.ToString(),
                              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_abbreviation
                          }
                      );


            RadGrid_Details.DataSource = que2.ToList().Concat(que.ToList()).OrderBy(a=>a.brand );
            RadGrid_Details.Rebind();
        }
0
 
LVL 4

Expert Comment

by:novynov
ID: 22753226
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?
0
 

Author Comment

by:mSchmidt
ID: 22753267
Yep tried that

Then it doesnt fail :)
0
 
LVL 4

Expert Comment

by:novynov
ID: 22753474
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.

Change:
 
prcPr = a.ingredient_price_pr,
 
To:
 
prcPr = a._ingredient_price / a._ingredient_quantity

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question