方法''没有支持的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屋!