CODEWITHSUNDEEP
c#linq
Rawhi
Rawhi

Reputation: 6413

How to use Where operator in Linq

SomeObject.Where(p => FunctionOn(p.I));
<<>>
public bool FunctionOn(int i){
    return (i == 1);}
<<>>
error message : The method FunctionOn is not supported

but why when I use number instead of parameter it works fine ?

SomeObject.Where(p => FunctionOn(1));

I was wondering if there is a way to implement this code in Linq , because it gives me an error .
Thank you .

Upvotes: 1

Views: 1427

Answers (4)

user111013
user111013

Reputation:

As others have mentioned, your FunctionOn method isn't directly translatable by your data source.

However, there may be a way around it - depending upon what sort of comparison you need to do, and how big of a comparison you're making.

If instead, you create an extension method which takes an IQueryable<T> and returns IQueryable<T> you can write your expression directly.

This allows you to re-use complex expressions. You should also be able to compare against a list of reference data - however you'll need to take care that this comparison list doesn't exceed the parameters permitted for your datasource.

Example: 

static void Main(string[] args)
{


    var data = new List<Customer>
                {
                    new Customer {Id = 1, Name = "Anna"},
                    new Customer {Id = 2, Name = "Bob"},
                    new Customer {Id = 3, Name = "Claire"}
                };



    var result = data.AsQueryable().TestCustomer();




}

private static IQueryable<Customer> TestCustomer(this IQueryable<Customer> source)
{
    return source.Where(x => x.Id > 1);
}


public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Upvotes: 0

gideon
gideon

Reputation: 19465

This might work, Try :

SomeObject.AsEnumerable().Where(p => FunctionOn(p.I))

It would work for Linq-To-Entities and Linq-To-SQL too!

Why! How?

Because like stakx explains. IQueryable stores your expression. If you check out the docs for IQueryable it has an Expression property, this stores the Linq you write in your code, and then a LinqProvider then translates it into the right thing, in your case that would be MongoDB syntax/SQL IDK? For linq-to-sql this would turn into an T-SQL expression. So there is no SQL-way to write FunctionOn(). Can it magically call your function written in C# from SQL? Nope!

Now, IEnumerable is an object collection in memory. When you say AsEnumerable() you get the objects in memory, then when you do Where(x=> func(x)) Linq-To-Objects can handle it no problem.

Like @Will on the comments rightly says, this gets every single object into memory and then queries it. At times it may not be what you want to do, but I wanted you to understand why your code doesn't work and also if you needed there is sorta a workaround

Hope it gave you the same insight I discovered a few weeks ago while watching lots of channel9 Linq Videos. =)

Upvotes: 0

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84835

SomeObject.Where(p => FunctionOn(p.I));

You said that SomeObject is an IQueryable. This makes me suspect that you aren't querying an in-memory collection (LINQ-to-Objects), where your query should succeed without any problems, but you're possibly querying something more "dynamic". For the sake of demonstrating the problem, I will assume that we're dealing with LINQ-to-SQL here... but that assumption doesn't have to be necessarily true for what I would like to explain below:

Update: You've just said in a comment that you're using LINQ-to-MongoDB, so the below should apply to your issue.

With something like LINQ-to-SQL, the problem with your query isn't that something's wrong with the FunctionOn predicate method, or with the way you're combining it with the Where operator. The problem at hand is that LINQ-to-SQL (or whatever LINQ provider you're using) doesn't recognise your method. It's trying to convert your LINQ expression into an SQL query at runtime (that's exactly what IQueryable is good for, after all!), but it doesn't know how to convert your FunctionOn to SQL.

If LINQ-to-SQL were to achieve this, it would have to figure out what your method is doing, and then find a way to express that as valid SQL. That would take a lot of sophistication and intelligence. For example, it would have to inspect your method e.g. by decompiling it, and then re-compiling it as SQL. This clearly cannot work for any arbitrarily complex method. Imagine that your FunctionOn method contained a call to Debug.WriteLine. There's no way that this could be translated into an SQL query.

Thus LINQ-to-SQL is restricted to a few cases and expressions that it knows how to handle. If it encounters anything else in a LINQ expression, it'll let you know that it doesn't know how to handle FunctionOn (ie. how to convert it to SQL) by saying something like, "The method FunctionOn is not supported."

Once you write your query as:

SomeObject.Where(p => p.I == 1);

everything should work fine, since LINQ-to-SQL knows how to translate your p.I == 1 to a corresponding equality test in a SQL WHERE clause.

Upvotes: 2

James
James

Reputation: 82136

You need to be more specific with the error you are actually getting, however, as a general guide normally this would fail if FunctionOn cannot be translated into a database specific language query (if SomeObject is a DB entity) or it does not return a boolean value which the Where function expects.

Upvotes: 2

Related Questions