linq to entities - try this one!

Question

Hi

Im converting to linq to entities and I am finding problems attempting to convert the stored procs I have created as an overview of data.

How do I convert this sql statement to linq to entities:

I have a venue table with a child venuerooms table. With the last part I want to get the largest capacity for that venue across all rooms and roomtypes.

This is currently working in sql server 2005

Any help would be greately appreciated

ALTER proc [dbo].[sp_getVenueOverview]
   (@venue varchar(100)) as
   SELECT (Select accomrooms
        from tblvenue
        where venueid=(select venueid from tblvenue where urlfriendly = @venue))
        as accomrooms,
        (Select count(*)
         from tblvenueroom
         where venueid=(select venueid from tblvenue where urlfriendly = @venue))
      as roomcount,
     (Select Max(dbo.Greatest(theatrestyle,classroom,boardroom,ushape,banquet,cocktail))
     from tblvenueroom
     where venueid=(select venueid from tblvenue where urlfriendly = @venue))
        as largest

Answer 1

you might want to refactor the query first, here is my try:

SELECT 
    v.accomrooms,r.roomcount,r.largest
    FROM tblvenue  v
        LEFT OUTER JOIN (SELECT
                             v.venueid
                                 ,COUNT(*) AS roomcount
                                 ,Max(dbo.Greatest(r.theatrestyle,r.classroom,r.boardroom,r.ushape,r.banquet,r.cocktail) AS largest --dbo.Greatest() kills performance!
                             FROM tblvenue                 v
                                 INNER JOIN tblvenueroom   r ON v.venueid=r.venueid
                             WHERE v.urlfriendly = @venue
                             GROUP BY v.venueid
                        ) dt ON v.venueid=dt.venueid
    WHERE v.urlfriendly = @venue