0

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
Improve this question
4
  • This looks like a poorly designed function in general. You have specifically created a situation which causes an n+1 problem. If this is absolutely necessary to do this way I would at the very least do a sanity check that q.Length <= someSaneValue Commented Feb 1, 2014 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. Commented Feb 1, 2014 at 20:07
  • Just a FYI, after you fix your calling bug 6/100 will always return 0 due to integer division. Commented Feb 1, 2014 at 20:16
  • sorry i.sum tax was my fault Commented Feb 1, 2014 at 20:16

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes exactly.so what should i do
"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 clause which can greatly simplify the generated SQL when using a LinqToEntities based system.
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.
I agree, Method syntax (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.
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.

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.