error - calculating percentage when using Linq

Question

I have a Ling Query for calculate percentage.

        var total = db.Schema_Details.Where(x => x.RID == sName).Select(x => new{
Rid = x.RID,
Amount = Convert.ToDouble(x.Lum_Sum_Amount),
Allocation = Convert.ToDouble(x.Allocation),
total = ((x.Allocation / 100) * x.Lum_Sum_Amount)}).ToList();

But exception occurred.

How can I solve this?

From the error it sounds like one of the variables you're converting to a double is already one in the DB — Alfie Goodacre, Nov 14 '16 at 10:38
I am not sure can use Concert.ToDouble in the lambda. It s parsed into sql by ef. You can try to get the list with ToList After the where call (wich Will execute the sql) and then use the select method on the list. Let me know if it works — Béranger, Nov 14 '16 at 10:42
check the type of Allocation and lum_sum_amount. It shouldn't be varchar or string. Better you convert these two double and then perform the divide and multiply operation. — Mukund, Nov 14 '16 at 10:46
You cannot use any kind of Function calls within your lambda expressions, What you can do is first get the plain data into a object list and then run your own logic to change the data into desired format. Idea is to keep Linq and the Data conversion logic in 2 different steps — Rajshekar Reddy, Nov 14 '16 at 10:57

score 2 · Accepted Answer · edited May 23 '17 at 10:32

Since you use the identifier db, I assume db is a DbContext. This means that your linq statements will be executed in SQL on the database server side and not in your memory (SQL or something similar, depending on the type of DB your are using).

You can see this if you check the type of your statement in the debugger: is it an IQueryable or an IEnumerable? IQueryables are executed by the database server, IEnumerable are usually executed in your memory. Usually your database server can execute your linq statements faster and more efficient than you, so it is best to make sure your linq statements are IQueryable.

However, the disadvantage is, that you database server does not know any of your classes, functions, nor .NET. So in IQueryables you can only use fairly simple calculations.

If you really need to call some local functions somewhere halfway your linq statement, add AsEnumerable()to your statement:

var myLinqResult = db. ...      // something IQueryable
    .Where(item => ... )        // simple statement: still IQueryable
    .Select(item => ...)        // still simple, still IQueryable
    // now you want to call one of your own functions:
    .AsEnumerable()
    .Select(item => myOwnLocalFunctions(item));

See stackoverflow: Difference between IQueryable and IEnumerable

The local function you want to perform is Convert.ToDouble. IQueryable does not know the Convert class, and thus can't perform your function as IQueryable. AsEnumerable will solve this problem.

However, in this case I would not advise to use AsEnumerable. When executing linq statements on a database try to avoid using local classes and function calls as much as possible. Instead of Convert.ToDoubleuse (double)x.Sum_Amount. Your linq-to-sql translator (or similar database language converter) will know how to translate this as IQueryable.

There are some calculations of which there are SQL equivalents, like DateTime calculations, or string reverse. It would be a shame if you had to do things like DateTime.AddDays(1) in local memory while there are perfect SQL equivalents for it. This would especially be a problem if you need to Join, Select or GroupBy after your local functions. Your database server can do these things far more efficiently than you. Luckily there are some IQueryable extension functions for them. They can be found in System.Data.Entity.DbFunctions

error - calculating percentage when using Linq

1 Answers1