Method'' has no supported translation to SQL

Question

I want to, in the midst of a linq to sql query where clause, check against a public int. I am getting this error: Method 'Int32 isInDept(System.String)' has no supported translation to SQL.

Vaguely related classes (from a public static class called ad):

    //get AD property
    public static string GetProperty(this Principal principal, String property) {
        DirectoryEntry directoryEntry = principal.GetUnderlyingObject() as DirectoryEntry;
        if (directoryEntry.Properties.Contains(property))
            return directoryEntry.Properties[property].Value.ToString();
        else
            return String.Empty;
    }

    public static string GetDepartment(this Principal principal) {
        return principal.GetProperty("department");
    }

The Classes in question (from a different class):

    public int isInDept(string department) {
        PrincipalContext domain = new PrincipalContext(ContextType.Domain);
        UserPrincipal userPrincipal = UserPrincipal.FindByIdentity(domain, GetUserId());

        if (department == userPrincipal.GetDepartment()) {
            return 3;
        }
        else { return 2; }
    }

    public intranetGS.viewArticle viewArticle(int id) {
        string user = GetUserId();

        var result = ( from a in n.articles
                       join s in n.sections on a.section equals s.section_id
                       join p in n.privacies on a.privacy equals p.privacy_id
                       let iid = isInDept(s.name)
                       where (a.active == true && a.article_id == id && a.privacy < iid) ||
                       (a.active == true && a.article_id == id && a.privacy == 3 && a.author == user)
                       select new intranetGS.viewArticle {
                           articleId = a.article_id,
                           title = a.title,
                           author = a.author,
                           html = a.html,
                           section = s.name,
                           privacy = p.name,
                           dateCreated = a.date_created,
                           dateModified = a.date_modified,
                           userCreated = a.user_created,
                           userModified = a.user_modified
                       }).First();

        var nv = (from v in n.navs
                            join s in n.sections on v.section equals s.section_id
                            let iid = isInDept(s.name)
                            where (v.active == true && s.name == result.section && v.privacy  < 3) ||
                            (v.active == true && s.name == result.section && v.privacy == iid && v.user_created == user)
                            select v.html);

        StringBuilder sb = new StringBuilder();

        foreach (var r in nv) {
            sb.Append(nv);
        }

        result.articleNav = sb.ToString();

        return result;
    }

What am I doing wrong? If I can't do it this way, how is it suggested that it be done?

Answer 1

It is not possible to translate that function to SQL, one workaround for this is to make most of your query with linq to sql, and use Linq to Objects for the rest. It should be something like this:

 var query = ( from a in n.articles
                       join s in n.sections on a.section equals s.section_id
                       join p in n.privacies on a.privacy equals p.privacy_id
                       where (a.active == true && a.article_id == id)
                       select new intranetGS.viewArticle {
                           articleId = a.article_id,
                           title = a.title,
                           author = a.author,
                           html = a.html,
                           section = s.name,
                           privacy = p.name,
                           privacyId = a.privacy,
                           dateCreated = a.date_created,
                           dateModified = a.date_modified,
                           userCreated = a.user_created,
                           userModified = a.user_modified
                       }).ToList();

And then filter the list:

var result = query.Where(a => (a.privacyId < isInDept(a.section)) ||
                       (a.privacyId == 3 && a.author == user)).First();

Then you can do the same for the second query.