CODEWITHSUNDEEP
c#linq
hakkim
hakkim

Reputation: 23

error - calculating percentage when using Linq

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.

enter image description here

How can I solve this?

Upvotes: 0

Views: 207

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

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

Upvotes: 2

Related Questions