11

I have the following code:

public void SalesCount(string customerId)
{
  ..
  ..
  return ...;
}

var resultQuery = dataContext.Customers
.Where (c => c.Name == "Alugili")
.Where (c => SalesCount(c.CustomerId) < 100);

When I execute resultQuery I get a translation to SQL exception.

I need to call SalesCount in the Where can I do that is there any workaround for this problem!

Improve this question
11
  • 1
    Linq2SQL in header, EF in tags. Where is the truth? :)
    – Hamlet Hakobyan
    Commented Sep 10, 2013 at 9:13
  • 1
    Linq2SQL and EF is similar but exactly different technologies. I think, you are confused somewhere.
    – Hamlet Hakobyan
    Commented Sep 10, 2013 at 9:19
  • 1
    When faced with a situation such as this, you musk ask yourself: Is it possible to implement whatever SalesCount is doing in T-SQL ? If so, try to make a VIEW which represents the Customers table but adds an extra column (the result of SalesCount). Then map that VIEW in your LINQ to SQL entities, and perform selects on that entity. In this manner, you avoid bringing a load of records to be treated in .NET and let the RDBMS do it for you.
    – Eduard Dumitru
    Commented Sep 10, 2013 at 9:24
  • 1
    Might be worth reading scheibler-entwicklung.de/blog/post/…
    – Rowland Shaw
    Commented Sep 10, 2013 at 9:36
  • 1
    Extremely, you can make CLR UDF and call it in your linq. In cases, mentioned below, you will get memory load whwn use ToArray, AsEnumerable or so.
    – Hamlet Hakobyan
    Commented Sep 10, 2013 at 9:42

4 Answers 4

Reset to default
12

Simply you can't. You can't execute C# code on the SQL server directly, you can use only Expressions and some special recognized functions...

Unless you transform your query (at least partially) in a LINQ-to-Objects...

var resultQuery = dataContext.Customers
    .Where (c => c.Name == "Alugili")
    .AsEnumerable()
    .Where (c => SalesCount(c.CustomerId) < 100);

Be aware that the last Where will be executed on the client side, so many useless rows will be fetched from the DB.

Improve this answer
5
  • 1
    +1 for using AsEnumerable() this prevents unneccessary loading in memory when the 'in memory query' is expanded.
    – Silvermind
    Commented Sep 10, 2013 at 9:15
  • ToList and ToArray loads the data directly but AsEnumerable does not I will test it and give you a feedback thanks a lot!
    – Bassam Alugili
    Commented Sep 10, 2013 at 9:19
  • 1
    In this case the ToList, ToArray and AsEnumerable similar in point of view of memopry loading. Keyword is materialized query.
    – Hamlet Hakobyan
    Commented Sep 10, 2013 at 9:22
  • @HamletHakobyan do you have any link or any useful information about that?
    – Bassam Alugili
    Commented Sep 10, 2013 at 9:28
  • I think this is the most reasonable soultion for the problem the entities are loaded in the memory EF does not support this function for now I will use this and I can extend the dynamic library when I have time I will post the extenation. thanks a lot!
    – Bassam Alugili
    Commented Sep 10, 2013 at 9:36
2

Try

var resultQuery = dataContext.Customers
.Where (c => c.Name == "Alugili")
.ToArray()
.Where (c => SalesCount(c.CustomerId) < 100);

But then the second Where is not run as SQL but locally - all customers with the name "Alugili" are pulled from the DB...

Otherwise you have to write out your method directly in the where method as lambda expression.

Improve this answer
3
  • It is working thanks a lot can you tell me please ToArray will loads all entities from database? I have a lazy loading I do not need to load them all is this possible or not?
    – Bassam Alugili
    Commented Sep 10, 2013 at 9:13
  • 1
    Have a look at this: damieng.com/blog/2009/06/24/…. Maybe this solves your problem.
    – Rico Suter
    Commented Sep 10, 2013 at 9:19
  • 2
    You must understand that @xanatos made the same thing. It is also working. No, it will load all the entities which survive the c => c.Name = "Alugili".. In a wider scenario, dangerously many records, but possibly not all of them...
    – Eduard Dumitru
    Commented Sep 10, 2013 at 9:22
-1

Add && between two where condition..It will work

Improve this answer
-2

yes, you can.

but you should change return type for your function:

public **int** SalesCount(string customerId)
{
  ..
  ..
  return 500;
}


var resultQuery = dataContext.Customers.AsEnumerable()
                 .Where (c => c.Name == "Alugili" && SalesCount(c.CustomerId) < 100);
Improve this answer
3
  • 2
    no, you can't. The RDBMS is the one actually executing the query. In this scenario, the LINQ code is just an orchestrator based on which SQL queries are written, sent to the database and executed there. If it were true, then executing .Count() on a random LINQ query would cause the entire table to be transported into .NET, via the TCP connection, and each row checked for whatever condition the user programmer had in mind, just to compute a Count.
    – Eduard Dumitru
    Commented Sep 10, 2013 at 9:20
  • 1
    @EduardDumitru my code is just convert linq2sql to linq2Object
    – Tim.Tang
    Commented Sep 10, 2013 at 9:22
  • 1
    If you pay attention to what the asking user wrote you'll notice the line of code var resultQuery = dataContext.Customers. Presuming he's not trying to trick us in any way intentionally, he's more than likely using LINQ to SQL and one could also presume he's doing all of this with SQL Server. It is highly expensive to do what you said. All the where clauses that can be performed by the DB should be placed before the call to .AsEnumerable(). Furthemore: The answer was no, no you cant, but you CAN bring everything in .NET, and cause major resource consumption
    – Eduard Dumitru
    Commented Sep 10, 2013 at 9:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.