How to make this SQL in LINQ to SQL?

Question

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've tried this `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 {...};` But it's of course a possibility to make a stored procedure, but i'd like to learn the LINQ to SQL syntax.. — Giedo Donkers, Jun 27 '12 at 12:43
@GiedoDonkers looks to me like your L2S is almost right, apart from a missing `where` clause before your select - something like `where Pe.Naam != null || Pr.Omschrivijving != null || ....` (I think I'm right in thinking that your `LIKE` statements are just checking for strings? If not you might be interested in http://stackoverflow.com/questions/835790/how-to-do-sql-like-in-linq) — Andras Zoltan, Jun 27 '12 at 13:07
@AndrasZoltan thank you for the link. I've got this now `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 };` but I'm getting the error _The name 'Pe' is not in scope on the left side of 'equals'._ What is wrong? — Giedo Donkers, Jun 27 '12 at 13:35
@GiedoDonkers I'm not sure although you're using `==` for the `join Pe` when it should be `equals`. Also I think it would now be a great idea to update your question, adding at the end that linq code you've just put in that comment; I think you might get a bit more attention once you've done that :) — Andras Zoltan, Jun 27 '12 at 13:57
@GiedoDonkers - much better, if I could rescind my 'close' vote I would — Andras Zoltan, Jun 27 '12 at 14:24

score 0 · Answer 1 · answered Jun 27 '12 at 14:32

0

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.

answered Jun 27 '12 at 14:32

HaBo

13,999
36
114
206

I've tried that before but unfortunately that doesn't work, thanks for trying. – Giedo Donkers Jun 27 '12 at 14:35

How to make this SQL in LINQ to SQL?

1 Answers1

Linked