如何使这个SQL在LINQ到SQL？ [英] How to make this SQL in LINQ to SQL?

问题描述

我熟悉的方式SQL的作品，但与LINQ合作，SQL给我一个头痛的问题。我有下面的查询，但我不能将其更改为LINQ to SQL的。有人能帮助我与此查询或解释我如何来解决这个上了呢？

  SELECT
         DISTINCT Pr.P code_，
         Pr.Omschrijving，
         Pt.TypeOmschr
    从
        Projecten公关
    内部联接
        ProjectTypen AS铂
        ON Pr.ProjType_ = Pt.TypeID
        和
        Pr.Status NOT IN（'Afgerond'，'Afgewezen'）
        LEFT OUTER JOIN
        Personeel AS PE
        ON Pr.SeniorId_ = Pe.PerId_
        要么
        Pr.CorId_ = Pe.PerId_
        哪里
        （Pe.Naam LIKE'%%'）
         要么
        （Pr.Omschrijving LIKE'%%'）
        要么
        （Pr.P code_ LIKE'%%'）
        要么
       （Pt.TypeOmschr LIKE'%%'）
        ORDER BY
        Pr.P code_
 

编辑：结果
我有以下L2S，但它不工作。我得到了以下错误：'PE'这个名字是不是在范围上的'等于'左侧的我想这个问题是左加入，但如何解决

 来自PR的_db.Projectens
    加入铂在_db.ProjectTypens上Pr.ProjType_等于Pt.TypeID
    加入PE在_db.Personeels上（（Pr.SeniorId_ == Pe.PerId_）||（Pr.SeniorId_ == Pe.PerId_））为P
    其中，Pr.P code_.Contains（搜索）|| Pr.Omschrijving.Contains（搜索）|| Pt.TypeOmschr.Contains（搜索）|| Pe.Naam.Contains（搜索）
    选择新SearchProjectViewModel {
        项目code = Pr.P code_，
        项目名= Pr.Omschrijving，
        项目类型= Pt.TypeOmschr
    };
 

解决方案

我觉得

 加入PE在_db.Personeels上（（Pr.SeniorId_ == Pe.PerId_）||（Pr.SeniorId_ == Pe.PerId_））为P
 

应

 在_db.Personeels加入PE上（（Pr.SeniorId_等于Pe.PerId_）||（Pr.SeniorId_等于Pe.PerId_））为P
 

尝试，让我知道，如果这个工程。

I'm familiar with the way SQL works but working with LINQ to SQL gives me an headache. I've the following query but I not able to change it to LINQ to SQL. Can someone help me with this query or explain me how to work this on out?

 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_

Edit:
I have the following L2S but it is not working. I'm getting the following error: The name 'Pe' is not in scope on the left side of 'equals'. I guess the problem is the left join, but what is the solution?

    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  
    };

解决方案

I think

join Pe in _db.Personeels on ((Pr.SeniorId_ == Pe.PerId_) || (Pr.SeniorId_ == Pe.PerId_)) into P

should be

join Pe in _db.Personeels on ((Pr.SeniorId_ equals Pe.PerId_) || (Pr.SeniorId_ equals Pe.PerId_)) into P

try and let me know, if this works.

这篇关于如何使这个SQL在LINQ到SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！