LINQ to Entities does not recognize the method Dou

Question

I've tried the below LINQ Query in Linqer it is working fine but it is giving the below error while i tried with C#

from IHeal_Mnt_Tickets in iHealEntities.iHeal_Mnt_Tickets
    where
        Tickets.Active == 1 &&
        Tickets.MntID == 1 &&
        Tickets.InsertedOn >= fromdate && 
        Mnt_Tickets.InsertedOn <= todate &&
        (new string[] { "Resolved", "Assigned" }).Contains(Tickets.status)
        group Tickets by new {
            Tickets.Instance
        } into g
            select new {
              Instance = g.Key.Summus_Instance,
              Assigned = (Int64?)g.Count(p => p.iHealID != null),
              resolved = (System.Int64?)g.Sum(p => (p.status == "Resolved" ? 1 : 0)),
              domain = (System.Int64?)g.Sum(p => (p.status == "Assigned" ? 1 : 0)),
              iHeal_Closure = (Decimal?)Math.Round((Double)(Double)g.Sum(p => (p.iHeal_Cur_status == "Resolved" ? 1 : 0)) * 1.0 / (Double)g.Count(p => p.iHealID != null) * 100, 2, MidpointRounding.AwayFromZero)
            };

The error is

"LINQ to Entities does not recognize the method 'Double Round(Double, Int32, System.MidpointRounding)' method, and this method cannot be translated into a store expression."

Answer 1

Not everything that's supported in the BCL has a direct equivalent in SQL. Given that this is the final part of the query, the simplest approach would be to just write a query which fetched all the data you need without rounding etc, and then transform that data into your preferred format using a local query:

var dbQuery = from item in source
              where filter
              select projection;
// The AsEnumerable() part is key here
var localQuery = from item in dbQuery.AsEnumerable()
                 select complicatedTransformation;

Using AsEnumerable() effectively just changes the compile-time type... so that the Select call is Enumerable.Select using a delegate rather than Queryable.Select using an expression tree.

I would hope that you could make the final transformation much simpler than your current approach though - things like (Double)(Double) really aren't necessary... and any time you convert from double to decimal or vice versa, you should question whether that's necessary or desirable... it's generally better to stick to either binary floating point or decimal floating point, rather than mixing them.