error in calling and calculate function with int? as input

Question

I have a function like that :

public int? calculateContractPrice(int? comid)
{
    int? sum = 0;
    var q = from i in dbconnect.tblMaterialGroups
            where i.tenderId == _tenderId
            select i.id;
    foreach (int i in q )
    {
        var q2 = from g in dbconnect.tblMaterialTenderAnnouncePrices
                 where g.MaterialGroupId == i && g.companyId == comid
                 select g;
        sum = q2.First().amount*q2.First().price + q2.First().amount*q2.First().PriceForElse + sum;


    }
    return sum ;
}

When i try to execute this :

List<presentationcontract> q = (from i in dbconnect.tblContracts
                                where i.tender == _tenderId
                                select new presentationcontract()
                                    {                                            
                                         tax =(calculateContractPrice(i.companyId)*(6/100)).ToString()

                                    }).ToList();

Tax is string .after executing i got this error :

couldn't translate expression calculateContractPrice(i.companyId)*(6/100),invoke(value(system.Func1[system.nullable1[system.Int32]]))).ToString() into SQL and could not treat it as a local expression

This looks like a poorly designed function in general. You have specifically created a situation which causes an [n+1 problem](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue). If this is absolutely necessary to do this way I would at the very least do a sanity check that `q.Length <= someSaneValue` — George Mauer, Feb 01 '14 at 20:04
Why is sum and the return value `int?` instead of `int`? I can't see any execution path where it would be set to null. Also, what is ` return sum i.tax;`? That is not valid c# syntax. — George Mauer, Feb 01 '14 at 20:07
Just a FYI, after you fix your calling bug `6/100` will always return 0 due to integer division. — Scott Chamberlain, Feb 01 '14 at 20:16

George Mauer · Accepted Answer · 2014-02-01T20:28:12.767

Your edit makes clear the issue. You're trying to do

tax =(calculateContractPrice(i.companyId)*(6/100)).ToString()

in a sql statement but calculateContractPrice is in c#! To understand what's going on you really need to understand a bit how LINQ works.

First of all, stop using the silly sql-style syntax for LINQ. It is less powerful than the lambda syntax and hides what is going on under the hood in a way that makes it hard to understand.

Second consider a LINQ statement

users.Where(u => u.Name == "George").ToList();

where users is IEnumerable<User>. What happens here is that the lambda part is of type Func<User, bool> and gets compiled to a method that gets run against every single instance of User.

Now consider this LINQ statement

db.Users.Where(u => u.Name == "George").ToList();

where db.Users is IQueryable<T>. This looks the same but what happens is VERY different. What happens is that lambda is actually of type Expression<Func<User, bool>> this doesn't get compiled to a method, instead it gets compiled into something called an expression tree. This gets passed to the LINQ provider (in your case Entity Framework I'm guessing) which examines it and converts that into a SQL statement

SELECT Id, Name, Address FROM users WHERE Name = 'George'

What is happening in your case is that it sees the call to calculateContractPrice and simply has no way of converting that to SQL.

What you should therefore do is ensure the query runs first, then use the IEnumerable<T> form of LINQ that runs in c# to call your method.

var contracts = dbconnect.tblContracts.Where(i => i.tender == _tenderId)
                 .ToList()   //Query executes here, now you have IEnumerable<T>
                 .Select(i => new PresentationContract {
                                  Tax = ...
                              }).ToList();  //this ToList is only necessary if you want to prevent multiple iteration

You will want to solve all the other problems everyone else pointed out as well of course.

A few other notes - you will want to read up on .Net naming conventions. Usually, anything public,protected, or internal (classes, fields, properties, etc.) is recommended to be PascalCase. Also you probably want to move the division portion into the PresentationContract class. This class that has a Tax property should probably be the one that knows how to generate it.

"*the silly sql-style syntax for LINQ*" There are some things you can't easily do unless you use the query sytax, like the [`let`](http://msdn.microsoft.com/en-us/library/bb383976.aspx) clause which can greatly simplify the generated SQL when using a LinqToEntities based system. — Scott Chamberlain, Feb 01 '14 at 20:23
That's a fair point, @ScottChamberlain but unless you're using those very specific features you should avoid it. I've seen time and time and time again novice devs who have a much easier time understanding LINQ when they switch to lambda syntax. — George Mauer, Feb 01 '14 at 20:25
I agree, [Method syntax](http://msdn.microsoft.com/en-us/library/bb397947.aspx) (that's its real name, not lambada syntax, using the correct names makes it easier for people go google them if they want to look it up) is much easier to learn. — Scott Chamberlain, Feb 01 '14 at 20:28

score 0 · Answer 2 · answered Feb 01 '14 at 20:02

0

Try this:

int? ret = calculateContractPrice(i.companyId);
if(ret.HasValue)
{
    tax =(ret.Value*(6/100)).ToString();
}

You should make sure that the function indeed returned a value and then you use that integer value in calculation.

answered Feb 01 '14 at 20:02

Ahmed Salman Tahir

1,783
1
17
26

Good point but no reason why this would give the OP the error they are reporting. I'm having a lot of difficulty seeing what could possibly generate that error. Also as I pointed out in a comment, there is no execution path where the return value would be set to null. – George Mauer Feb 01 '14 at 20:09

error in calling and calculate function with int? as input

2 Answers2