我熟悉SQL的工作方式,但是使用LINQ to SQL让我很头痛。我有以下查询语句,但我无法将其更改为LINQ to SQL。有人能帮我处理这个查询语句或者解释一下如何处理吗?
SELECT
DISTINCT Pr.Pcode_,
Pr.Omschrijving,
Pt.TypeOmschr
FROM
Projecten AS Pr
INNER JOIN
ProjectTypen AS Pt
ON Pr.ProjType_ = Pt.TypeID
AND
Pr.Status NOT IN ( 'Afgerond', 'Afgewezen' )
LEFT OUTER JOIN
Personeel AS Pe
ON Pr.SeniorId_ = Pe.PerId_
OR
Pr.CorId_ = Pe.PerId_
WHERE
( Pe.Naam LIKE '%%' )
OR
( Pr.Omschrijving LIKE '%%' )
OR
( Pr.Pcode_ LIKE '%%' )
OR
( Pt.TypeOmschr LIKE '%%' )
ORDER BY
Pr.Pcode_
编辑:
我有以下的L2S代码,但它不能正常工作。我得到了以下错误信息:'Pe'的名称不在“等于”的左侧范围内。 我猜问题在于左连接,但解决方法是什么呢?
from Pr in _db.Projectens
join Pt in _db.ProjectTypens on Pr.ProjType_ equals Pt.TypeID
join Pe in _db.Personeels on ((Pr.SeniorId_ == Pe.PerId_) || (Pr.SeniorId_ == Pe.PerId_)) into P
where Pr.Pcode_.Contains(search) || Pr.Omschrijving.Contains(search) || Pt.TypeOmschr.Contains(search) || Pe.Naam.Contains(search)
select new SearchProjectViewModel {
ProjectCode = Pr.Pcode_,
ProjectName = Pr.Omschrijving,
ProjectType = Pt.TypeOmschr
};
from Pr in _db.Projectens join Pt in _db.ProjectTypens on Pr.ProjType_ equals Pt.TypeID join Pe in _db.Personeels on ((Pr.SeniorId_ == Pe.PerId_) || Pr.SeniorId_ == Pe.PerId_) into P select new {...};
当然,也可以使用存储过程,但我想学习LINQ to SQL语法。 - Giedo Donkerswhere
子句 - 类似于where Pe.Naam != null || Pr.Omschrivijving != null || ....
(我想我是对的,你的LIKE
语句只是检查字符串吗?如果不是,你可能会对 https://dev59.com/tnRA5IYBdhLWcg3wxA1N 感兴趣) - Andras Zoltanfrom Pr in _db.Projectens join Pt in _db.ProjectTypens on Pr.ProjType_ equals Pt.TypeID join Pe in _db.Personeels on ((Pr.SeniorId_ == Pe.PerId_) || Pr.SeniorId_ == Pe.PerId_) into P where Pr.Pcode_.Contains(search) || Pr.Omschrijving.Contains(search) || Pt.TypeOmschr.Contains(search) || Pe.Naam.Contains(search) select new SearchProjectViewModel { ProjectCode = Pr.Pcode_, ProjectName = Pr.Omschrijving, ProjectType = Pt.TypeOmschr };
但是我遇到了错误:“The name 'Pe' is not in scope on the left side of 'equals'.” 这是什么问题? - Giedo Donkersjoin Pe
中使用==
而不是equals
。另外,我认为现在更新你的问题并在末尾添加你刚刚在评论中放入的linq代码是一个好主意。我认为一旦你完成这个操作,你就会得到更多的关注 :) - Andras Zoltan