Perform calculation inside select statement in LINQ

Question

I have a situation where i have to calculate percentage of two values for example

IEnumerable<RenewalModel> result = 
    from r in renewalLists
    group r by r.CityID into grpCity
    select new RenewalModel
    {
        CityID = grpCity.Key,
        City = (from g in grpCity where g.CityID == grpCity.Key select g.City).First().Trim(),
        PotentialRenewalCount = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalCount).Sum(),
        PotentialRenewalSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalSQRT).Sum(),
        desiredCalucation=   (PotentialRenewalCount/PotentialRenewalCount)*100,
        RENEWALCOUNT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALCOUNT).Sum(),
        RENEWALSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALSQRT).Sum()
    };

and my calculation should be like this

(PotentialRenewalCount/PotentialRenewalCount)*100

as i have described it inside the select statement.

i even tried this query but i get the 0 as result

 IEnumerable<RenewalModel> result =
            (from r in renewalLists
            group r by r.CityID into grpCity
            select new RenewalModel
            {
                CityID = grpCity.Key,
                City = (from g in grpCity where g.CityID == grpCity.Key select g.City).First().Trim(),
                PotentialRenewalCount = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalCount).Sum(),
                PotentialRenewalSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalSQRT).Sum(),
                RENEWALCOUNT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALCOUNT).Sum(),
                RENEWALSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALSQRT).Sum()
            }).select(r => new RenewalModel
            {
                desiredCalucation = (r.PotentialRenewalCount / r.PotentialRenewalCount) * 100,
                CityID = r.CityID,
                City = r.City,
                PotentialRenewalCount = r.PotentialRenewalCount,
                PotentialRenewalSQRT = r.PotentialRenewalSQRT,
                RENEWALCOUNT = r.RENEWALCOUNT,
                RENEWALSQRT = r.RENEWALSQRT
            });

for some reason or another desiredCalucation variable is giving me 0 as result.

any help is appreciated. Thank you

Answer 1

For this purpose, use the LINQ let operator.

In general, I would recommend to split your big LINQ statement on several smaller ones. Otherwise, it will be very painful to debug this later.

Answer 2

If you're wanting to pull values from the original query, and then populate an additional property, you can do it like this:

        IEnumerable<RenewalModel> result =
            (from r in renewalLists
            group r by r.CityID into grpCity
            select new RenewalModel
            {
                CityID = grpCity.Key,
                City = (from g in grpCity where g.CityID == grpCity.Key select g.City).First().Trim(),
                PotentialRenewalCount = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalCount).Sum(),
                PotentialRenewalSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalSQRT).Sum(),
                RENEWALCOUNT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALCOUNT).Sum(),
                RENEWALSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALSQRT).Sum()
            }).select(r => new RenewalModel
            {
                desiredCalucation = (r.PotentialRenewalCount / r.PotentialRenewalCount) * 100,
                CityID = r.CityID,
                City = r.City,
                PotentialRenewalCount = r.PotentialRenewalCount,
                PotentialRenewalSQRT = r.PotentialRenewalSQRT,
                RENEWALCOUNT = r.RENEWALCOUNT,
                RENEWALSQRT = r.RENEWALSQRT
            });

Seems like you're doing an lot of "requerying" the same record in the individual assignments, though. You could probably accomplish the same thing in a much cleaner way by using a "join" on CityID.

Remember, that LINQ also supports subqueries:

        IEnumerable<RenewalModel> result =
            (from g in (
                from r in renewalList
                join c in cityList on r.CityID equals c.CityID
                select new RenewalModel
                {
                    CityID = grpCity.Key,
                    City = (from g in grpCity where g.CityID == grpCity.Key select g.City).First().Trim(),
                    PotentialRenewalCount = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalCount).Sum(),
                    PotentialRenewalSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalSQRT).Sum(),
                    RENEWALCOUNT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALCOUNT).Sum(),
                    RENEWALSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALSQRT).Sum()
                })
            group g by g.CityID into grpCity
            select new RenewalModel
            {
                desiredCalucation = (g.PotentialRenewalCount / g.PotentialRenewalCount) * 100,
                CityID = g.CityID,
                City = g.City.Trim(),
                PotentialRenewalCount = g.PotentialRenewalCount.Sum(),
                PotentialRenewalSQRT = g.PotentialRenewalSQRT.Sum(),
                RENEWALCOUNT = g.RENEWALCOUNT.Sum(),
                RENEWALSQRT = g.RENEWALSQRT.Sum()
            });

Answer 3

Well, assuming that you want the ratio between 2 properties of the actual RenewalModel, why not declare a read-only property that either does the computation each time or on first use?

class RenewalModel
{
    public int desiredCalucation => 
       (PotentialRenewalCount/PotentialRenewalCount)*100;

    // ...
}

By the way, you probably want to use double and fix the formula to use proper variables and do its computations using floating points.

You should also fix the typo in your variable name. Calucation is improperly spelled! Even worst, that name does tell much about the purpose of the property.

A benefit of doing such property is that it allows for code reuse if multiple queries return that information, it prevent the value to be changed from the outside and it won't affect LINQ code generation.

But since original code is not working and we do not have any example of what it should do, it is hard to guess what it should do.

Answer 4

Altough I do not understand why one would devide a number by itself just to multiply it by 100 - I believe the thing you're looking for is called let. Now, I have not tested this but it should work somehow similar to this:

IEnumerable<RenewalModel> result = 
    from r in renewalLists
    group r by r.CityID into grpCity
    let potentialRenewalCount = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalCount).Sum()
    let desiredCalculation = (PotentialRenewalCount/PotentialRenewalCount)*100
    select new RenewalModel
    {
    CityID = grpCity.Key,
    City = (from g in grpCity where g.CityID == grpCity.Key select g.City).First().Trim(),
    PotentialRenewalCount = potentialRenewalCount,
    PotentialRenewalSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.PotentialRenewalSQRT).Sum(),
    DesiredCalucation =   desiredCalculation,
    RENEWALCOUNT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALCOUNT).Sum(),
    RENEWALSQRT = (from g in grpCity where g.CityID == grpCity.Key select g.RENEWALSQRT).Sum()
};

And also think about the case when the SUM is 0 since this would lead to a division by zero!

Answer 5

When the expression is traslated to SQL by Linq, it truncates values to an integer while the query is executed, so when I use for example:

select new SomeModel{
    Value = 3/7
}

it returns 0.

but, when I use:

select new SomeModel{
    Value = 3.0 / 7.0
}

returns the correct value = 0.428...

So, I think when you use expressions to be used by Linq To Entities, wich must return double or decimal, you should write or cast all values explicitly as double...

I hope it helps.