方法''没有支持的SQL转换 [英] Method'' has no supported translation to SQL

问题描述

我想在linq to sql查询where子句中，检查一个公共int.我收到此错误:方法'Int32 isInDept(System.String)'不支持SQL转换.

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.

模糊关联的类(来自称为ad的公共静态类):

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?

推荐答案

无法将该函数转换为SQL，一种解决方法是使用linq to sql来完成大部分查询，并使用Linq来其余的对象.应该是这样的:

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();

然后过滤列表:

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.

这篇关于方法''没有支持的SQL转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！