[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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