Click here to Skip to main content
13,591,506 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
HI ALl,
i have one sql query which i need to rewrite in LINQ?


Can someone please help me ?

 SELECT  Publications.PublicationID,   Publications.Name, Articles.Title,Publications.PublicationID,Articles.ArticleID,Subscriptions.EmployeeID
		   ,(SELECT CASE WHEN ArticleReadUnread.EmployeeID = 2084 THEN ArticleReadUnread.ArticleID else 0 end) as 'ARTICLEREAD'
		   ,(SELECT  CASE WHEN BookmarkArticles.EmployeeID  = 2084 THEN BookmarkArticles.ArticleID else 0 end) as 'BOOKMARKED'
		   ,ArticleReadUnread.EmployeeID
		   ,BookmarkArticles.EmployeeID 
			
			
FROM                  Subscriptions 
					  INNER JOIN
                      Publications ON Subscriptions.PublicationID = Publications.PublicationID 
                      INNER JOIN
                      Articles ON Publications.PublicationID = Articles.PublicationID 
                      LEFT JOIN 
                      ArticleReadUnread ON Articles.ArticleID = ArticleReadUnread.ArticleID 
                      LEFT JOIN   
                      BookmarkArticles ON Articles.ArticleID = BookmarkArticles.ArticleID 

                      
WHERE    (Subscriptions.EmployeeID = 2084) and Publications.PublicationID=1007 
          and publications.Enabled=1  and Articles.Enabled=1 and Articles.Published=1 
Posted 2-Mar-14 23:25pm
Updated 1-Jan-18 18:46pm
Comments
lw@zi 3-Mar-14 5:28am
   
No one is going to do your work for free. Please put in some effort.
ravikhoda 3-Mar-14 5:29am
   
Well this is all i could post for now as linq is very new for me. however will try and add some code which i am trying Right now ....
vivvicks 2-Jan-18 6:25am
   
tool name linqpad might help u out
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Use tools like LINQPad[^], it's FREE. And read Why LINQ beats SQL[^]
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You can use Linqer for that.There is a trial version also.Try it.

Quote:
Linqer is a SQL to LINQ conversion tool. It helps learning LINQ and convert existing SQL statements.

Linqer
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

try with this:

var query=(from a in Subscriptions  INNER JOIN b in Publications on  a.PublicationID equals b.PublicationID inner join  c in Articles on b.PublicationID equals c.PublicationID  left join d in ArticleReadUnread on c.ArticleID equals d.ArticleID  LEFT JOIN e in
 BookmarkArticles ON c.ArticleID equals e.ArticleID WHERE    (a.EmployeeID = 2084) and b.PublicationID=1007 and b.Enabled=1  and c.Enabled=1 and c.Published=1
 
select new {b.PublicationID,   b.Name, c.Title,b.PublicationID,c.ArticleID,a.EmployeeID
           ,
(SELECT CASE WHEN d.EmployeeID = 2084 THEN d.ArticleID else 0 end) as 'ARTICLEREAD'
           ,(SELECT  CASE WHEN e.EmployeeID  = 2084 THEN e.ArticleID else 0 end) as 'BOOKMARKED'
           ,d.EmployeeID
           ,e.EmployeeID
}).ToList(); 
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

select r.actionitemid from actionitemmast r left join ActionItemResult ra

on r.actionitemid = ra.actionitemid

inner join MemberMast m on r.memberid = m.memberid

inner join MemberMast ma on r.assignfromid = ma.memberid

inner join project_member p on r.memberid = p.memberid

left join projectmast pa on p.projectid = pa.projectid

where (ra.ActionTaken like '%dipa%' or r.Description like '%dipa%'

or r.Task like '%dipa%' or m.First_name like '%dipa%' or ma.First_name like '%dipa%' or pa.projectname like '%dipa%')

group by r.actionitemid
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.180618.1 | Last Updated 2 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100