NotSupportedException when I'm using a method in the where clause

Question

I'm using nHibernate 3.2.0.4000. I wrote this query using nHibernate.Linq

var entities = (from t in this.Session.Query<Task>()
                where NotIn(t, role.Tasks)
                select t).ToList();

Here's the definition of the method NotIn()

private bool NotIn(Task t, IEnumerable<TaskDto> tasks)
{
    foreach (var task in tasks)
    {
        if (t.Name == task.Name) return false;
    }
    return true;
}

When I'm executing this query, I've got an NotSupportedException error:

Boolean NotIn(Probel.NDoctor.Domain.DAL.Entities.Task, System.Collections.Generic.IEnumerable`1[Probel.NDoctor.Domain.DTO.Objects.TaskDto])

I found a non Linq solution that is less readable but I still want to, at least, understand why it is impossible to build a Linq query like this.

Thank you in advance for your help!

You probably get this because LINQ doesn't know how to convert your method into SQL. You can tell it how, but I think that involves quite a bit of work, using Expression Trees. — Kendall Frey, May 23 '12 at 14:31
Would something like `where !role.Tasks.Contains(t.Name)` work? — Mark M, May 23 '12 at 14:36

score 2 · Accepted Answer · answered May 23 '12 at 14:41

2

You have to translate NotIn to nHibernate SQL query using expression tree.

nhibernate linq provider extension is a good starting point.

this link has In and NotIn extension method for nHibernate.

answered May 23 '12 at 14:41

Tilak

30,108
19
83
131

score 1 · Answer 2 · edited Nov 18 '12 at 21:50

1

Your code in Linq is ultimately translated to SQL query by nhibernate. You can not use a method which could not be translated into SQL code.

edited Nov 18 '12 at 21:50

marc_s

732,580
175
1,330
1,459

answered May 23 '12 at 14:31

Asif Mushtaq

13,010
3
33
42

score 0 · Answer 3 · answered May 23 '12 at 14:32

0

NHibernate cannot decompile and then parse your code to get valid SQL. There is no way to generate an sql statement from your method.

answered May 23 '12 at 14:32

Axel Wilczek

195
2
9

score 0 · Answer 4 · answered May 23 '12 at 14:36

0

Instead use:

var entities = (from t in this.Session.Query<Task>()
                where !role.Tasks.Any(rt => rt.Name == t.Name)
                select t).ToList();

answered May 23 '12 at 14:36

dzendras

4,721
1
25
20

Still the NotSupportedException, which is normal considering the other answers about translation of a Linq expression into SQL – JiBéDoublevé May 23 '12 at 14:46

NotSupportedException when I'm using a method in the where clause

4 Answers4