• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

I need to convert a complex SQL query to an ASP.NET LINQ statement for use in my MVC project.

This is the query I need to convert to a LINQ statement for use inside my ASP.NET MVC 3 project.  I'm not all that good with LINQ yet so I'm going to need some help please.

Thank you very much!

WITH CTE AS (
	SELECT a.GalleryID GalleryID_A, a.GalleryTitle, a.GalleryDate, b.*,  
	ROW_NUMBER() OVER (PARTITION BY  a.GalleryID, a.GalleryTitle, a.GalleryDate 
	ORDER BY b.MediaThumb DESC) AS Rn
	FROM Media b
	INNER JOIN Galleries A
	ON a.GalleryID = b.GalleryID
), 
CTE2 AS
(
	SELECT a.GalleryID GalleryID_A, a.GalleryTitle, a.GalleryDate, b.*,  
	1 Rn
	FROM Media b
	LEFT OUTER JOIN Galleries A
	ON a.GalleryID = b.GalleryID
)
SELECT * FROm CTE where rn = 1
UNION ALL
SELECT * FROM CTE2 WHERE rn = 1 and galleryid is null
ORDER BY MediaID DESC

Open in new window

0
maddhacker24
Asked:
maddhacker24
  • 3
  • 2
1 Solution
 
DcpKingCommented:
I presume that you can't just encase it as-is in a stored procedure and use LINQ to access that ? It would seem to be much easier.
0
 
maddhacker24Author Commented:
Dcpking that is not an option or I would have done that. I need a LINQ to SQL statement.
0
 
maddhacker24Author Commented:
I solved this on my own. I embedded SQL into my controller.

var Results = DB.Database.SqlQuery<WhatsNew>
                (@" WITH CTE AS (
                    SELECT a.GalleryTitle, a.GalleryDate, b.*,  
                    ROW_NUMBER() OVER (PARTITION BY  a.GalleryID, a.GalleryTitle, a.GalleryDate
                    ORDER BY b.MediaThumb DESC) AS Rn
                    FROM Media b
                    INNER JOIN Galleries A
                    ON a.GalleryID = b.GalleryID
                ),
                CTE2 AS
                (
                    SELECT a.GalleryTitle, a.GalleryDate, b.*,  
                    1 Rn
                    FROM Media b
                    LEFT OUTER JOIN Galleries A
                    ON a.GalleryID = b.GalleryID
                )
                SELECT * FROM CTE where rn = 1
                UNION ALL
                SELECT * FROM CTE2 WHERE rn = 1 and galleryid is null
                ORDER BY MediaID DESC ");
0
 
DcpKingCommented:
Congrats, MaddHacker!
0
 
maddhacker24Author Commented:
I solved the problem on my own. There were no other acceptable answers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now