LINQ 2 SQL query does not work with a function call

Question

I am quite sure this question has already been asked several times and I do apolgize for asking it once again, but I made a few research and unfortunately I did not find my pleasure on the internet...

I have a IQueryable like this :

triggers = triggers.Where(t => GetIdFromName(t.Name) == filter.Id.ToString());

The function GetIdFromName get a part of the name to retrieve the Id :

public string GetIdProfiloFromName(string name)
        {
            return name.Substring(name.IndexOf(NameFirstSeparator)+1,name.IndexOf(NameSecondSeparator)-name.IndexOf(NameFirstSeparator)-1);
        }

I know it is not nice, but it is the best I could managed to do so far. My question is that using Linq to sql is not permitted using these two statements. The application throws an error, whereas this is ok :

triggers = triggers.Where(t => t.Name.Substring(t.Name.IndexOf(NameFirstSeparator) + 1, t.Name.IndexOf(NameSecondSeparator) - t.Name.IndexOf(NameFirstSeparator) - 1) == filter.Id.ToString());

I suspect that the function GetIdFromName should give something different than a string, but I really wonder what and how...

Thanks for your enlightenment,

Yoann

[EDIT] Update to what I understood so far:

I cannot do what i wanted, because in order to do so I would need to do something of this kind :

static Expression<Func<Trigger,string,  bool>> IsId = (a,b) => a.name.Substring(a.name.IndexOf(NameFirstSeparator) + 1, a.name.IndexOf(NameSecondSeparator) - a.name.IndexOf(NameFirstSeparator) - 1)==b;

But this would not get into

triggers = triggers.Where(func<Trigger,bool>);

And I could manage to do it, but I would have to get all the results from my database to perform my test in memory.

Thanks a lot all, you made this get really clear to me!!

Answer 1

LINQ 2 SQL is converting your query into an expression tree, and then translating that expression tree into SQL. Since your custom function doesn't have an SQL correlated function, it doesn't know how to translate it and throws an exception.