Call a custom method in linq query

Question

I need to call a custom method in linq query, e.g.:

IQueryable<Person> query = _db.Persons.OrderBy(p => Decrypt(p.Name));

However, in this case Decrypt gives me an exception.

I also tried with this approach:

IQueryable<Person> query = _db.Persons.AsEnumerable()
                                      .OrderBy(p => Decrypt(p.Name))
                                      .AsQueryable<Person>();

However in this case it works but as I'm using this query as a source (SelectMethod) for my ListView, paging is not working when using DataPager control.

It accepts string and return decrypted string, as this field is encrypted in db. — Milan, Dec 16 '13 at 09:12
the exception would be helpful, but i think the problem is that your method `Decrypt` cannot be translated into sql. — Viper, Dec 16 '13 at 09:15
What is your paging criteria? Because you could omit the decrypting until after you have sized down your results. — Silvermind, Dec 16 '13 at 09:15
The problem is that OrderBy is performed by database which doesn't know anything about Decrypt method implemented in C#. There was a similar question about LINQ to SQL (see http://stackoverflow.com/questions/3635418/custom-method-in-linq-to-sql-query). — Michał Komorowski, Dec 16 '13 at 09:15
Yes, I understand. I have the same problem with other methods, such as Where(...), etc. My data in db is encrypted (by field) and I'm using EF Code First approach, so I need to decrypt fields. I can do this with the second approach, however then paging not work. — Milan, Dec 16 '13 at 09:34
As I see now the problem is that IQueryable must be sorted (with OrderBy) in order for paging to work. — Milan, Dec 16 '13 at 10:46
You may want to enumerate first, then do ordering similar to http://stackoverflow.com/a/24208901/3481183 — Believe2014, Jun 13 '14 at 15:53

score 1 · Answer 1 · answered Dec 16 '13 at 11:48

You need to actually figure out why IS the paging not working. It's possible to create custom SQL function and let Decrypt work. For that, you need to create SQL function Decrypt that does the work.

This is simple example:

(SQL part)

CREATE FUNCTION ReverseCustName(@string varchar(100))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @custName varchar(100)
    -- Implementation left as exercise for users.
    RETURN @custName
END

and LINQ part

[Function(Name = "dbo.ReverseCustName", IsComposable = true)]
[return: Parameter(DbType = "VarChar(100)")]
public string ReverseCustName([Parameter(Name = "string",
    DbType = "VarChar(100)")] string @string)
{
    return ((string)(this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        @string).ReturnValue));
}

See this for more information: http://msdn.microsoft.com/en-us/library/bb386973(v=vs.110).aspx

score 0 · Answer 2 · answered Dec 16 '13 at 09:20

if its LINQ to SQL you cant since it tries to render that to real SQL. try to cast the Persons to another object class of your own and then it will be Linq to Object and there it will work. i had this issue a few times, i played with it a little bit until it worked.

BTW i think one of the linq extensions is looking for an IComparable, so worse case you can just implement one

flindeberg · Answer 3 · 2013-12-16T10:05:48.170

0

I will give you a one-liner which "could" work, but please provide the exception as well.

var query = _db.Persons.AsEnumerable().OrderBy(p => Decrypt(p.Name));

Your problem is probably, but it is hard to know since we are not given the exception, that you are trying to get your database to execute Decrypt which is not possible. The added ToList() just enumerates, ie fetches the data from database in this case, and then we can order in memory.

You won't get paging to work the way you want since the database cannot execute the query.

edited Dec 16 '13 at 10:05

answered Dec 16 '13 at 09:39

flindeberg

4,887
1
24
37

This not work, because I must return `IQueryable`. I can try `_db.Persons.ToList().OrderBy(p => Decrypt(p.Name)).AsQueryable();` but in this case, also, paging not work. – Milan Dec 16 '13 at 09:42
Don't use `ToList` for this, use `AsEnumerable`. There's no need to make a list out of the data. – Rawling Dec 16 '13 at 09:43
1

I tried this already with `AsEnumerable`, it is noted in my first post. – Milan Dec 16 '13 at 09:46
What is the exception? Are you even getting an exception? – flindeberg Dec 16 '13 at 09:47
No, there is no exception. Only, paging with DataPager control is not working. Actually, pager is working but ListView is not showing new page data. – Milan Dec 16 '13 at 10:40
Basically I need to order IQueryable but by using a custom func, is it possible ? – Milan Dec 16 '13 at 11:08
Ah, now I get what you are trying to do and what the issue really is, I would solve it by using a viewmodel and get the viewmodel to load the entire dataset (as a List or similar) and then apply the necessary magic there. The only other way to get paging to work the way you want to is by implementing decrypt at a database level. – flindeberg Dec 16 '13 at 12:39

Call a custom method in linq query

3 Answers3