Converting complex sql stored proc into linq

Question

I'm using Linq to Sql and have a stored proc that won't generate a class. The stored proc draws data from multiple tables into a flat file resultset.

The amount of data returned must be as small as possible, the number of round trips to the Sql Server need to be limited, and the amount of server-side processing must be limited as this is for an ASP.NET MVC project.

So, I'm trying to write a Linq to Sql Query however am struggling to both replicate and limit the data returned.

Here's the stored proc that I'm trying to convert:

SELECT AdShops.shop_id as ID, Users.image_url_75x75, AdShops.Advertised,
    Shops.shop_name, Shops.title, Shops.num_favorers as hearts, Users.transaction_sold_count as sold,
    (select sum(L4.num_favorers) from Listings as L4 where L4.shop_id = L.shop_id) as listings_hearts, 
    (select sum(L4.views) from Listings as L4 where L4.shop_id = L.shop_id) as listings_views, 
    L.title AS listing_title, L.price as price, L.listing_id AS listing_id, L.tags, L.materials, L.currency_code, 
    L.url_170x135 as listing_image_url_170x135, L.url AS listing_url, l.views as listing_views, l.num_favorers as listing_hearts

FROM    AdShops INNER JOIN
    Shops ON AdShops.shop_id = Shops.shop_id INNER JOIN
    Users ON Shops.user_id = Users.user_id INNER JOIN
    Listings AS L ON Shops.shop_id = L.shop_id

WHERE   (Shops.is_vacation = 0 AND 
    L.listing_id IN
    (
        SELECT  listing_id
        FROM    (SELECT  l2.user_id , l2.listing_id, RowNumber = ROW_NUMBER() OVER (PARTITION BY l2.user_id ORDER BY NEWID())
                    FROM    Listings l2
                            INNER JOIN (
                            SELECT  user_id
                            FROM    Listings
                            GROUP BY
                                    user_id
                            HAVING  COUNT(*) >= 3
                            ) cnt ON cnt.user_id = l2.user_id  
                ) l2 
        WHERE   l2.RowNumber <= 3 and L2.user_id = L.user_id
    )
    )
ORDER BY Shops.shop_name

Now, so far I can return a flat file but am not able to limit the number of listings. Here's where I'm stuck:

Dim query As IEnumerable = From x In db.AdShops
                       Join y In (From y1 In db.Shops
                                  Where y1.Shop_name Like _Search + "*" AndAlso y1.Is_vacation = False
                                  Order By y1.Shop_name
                                  Select y1) On y.Shop_id Equals x.shop_id
                       Join z In db.Users On x.user_id Equals z.User_id
                       Join l In db.Listings On l.Shop_id Equals y.Shop_id
                       Select New With {
                            .shop_id = y.Shop_id,
                            .user_id = z.user_id,
                            .listing_id = l.Listing_id
                            } Take 24 ' Fields ommitted for briefity...

I assume to select a random set of 3 listings per shop, I'd need to use a lambda expression however am not sure how to do this. Also, need to add in somewhere consolidated totals for listing fieelds against individual shops...

Anyone have any thoughts?

UPDATE:

Here's the current solution that I'm looking at:

1. Result class wrapper:

    Public Class NewShops
        Public Property Shop_id As Integer
        Public Property listing_id As Integer
        Public Property tl_listing_hearts As Integer?
        Public Property tl_listing_views As Integer?
        Public Property listing_creation As Date
    End Class

1. Linq + code:

Using db As New Ads.DB(Ads.DB.Conn)
    Dim query As IEnumerable(Of IGrouping(Of Integer, NewShops)) =
        (From x In db.AdShops
         Join y In (From y1 In db.Shops
                    Where (y1.Shop_name Like _Search + "*" AndAlso y1.Is_vacation = False)
                    Select y1 
                    Skip ((_Paging.CurrentPage - 1) * _Paging.ItemsPerPage)
                    Take (_Paging.ItemsPerPage))
              On y.Shop_id Equals x.shop_id
         Join z In db.Users On x.user_id Equals z.User_id
         Join l In db.Listings On l.Shop_id Equals y.Shop_id
         Join lt In (From l2 In db.Listings _
                     Group By id = l2.Shop_id Into Hearts = Sum(l2.Num_favorers), Views = Sum(l2.Views), Count() _
                     Select New NewShops With {.tl_listing_views = Views,
                                               .tl_listing_hearts = Hearts,
                                               .Shop_id = id})
              On lt.Shop_id Equals y.Shop_id
         Select New NewShops With {.Shop_id = y.Shop_id,
                                   .tl_listing_views = lt.tl_listing_views,
                                   .tl_listing_hearts = lt.tl_listing_hearts,
                                   .listing_creation = l.Creation,
                                   .listing_id = l.Listing_id
                                  }).GroupBy(Function(s) s.Shop_id).OrderByDescending(Function(s) s(0).tl_listing_views)

    Dim Shops as New Dictionary(Of String, List(Of NewShops))

    For Each item As IEnumerable(Of NewShops) In query
        Shops.Add(item(0).shop_name, (From i As NewShops In item
                                      Order By i.listing_creation Descending
                                      Select i Take 3).ToList)
    Next
End Using

Anyone have any other suggestions?

Answer 1

From the looks of that SQL and code, I'd not be turning it into LINQ queries. It'll just obfuscate the logic and probably take you days to get it correct.

If SQLMetal doesn't generate it properly, have you considered using the ExecuteQuery method of the DataContext to return a list of the items you're after?

Assuming that your sproc you're trying to convert is called sp_complicated, and takes in one parameter, something like the following should do the trick

Protected Class TheResults
    Public Property ID as Integer
    Public Property image_url_75x75 as String
    '... and so on and so forth for all the returned columns. Be careful with nulls
End Class

'then, when you want to use it

Using db As New Ads.DB(Ads.DB.Conn)
    dim results = db.ExecuteQuery(Of TheResults)("exec sp_complicated {0}", _Search)
End Using

Before you freak out, that's not susceptible to SQL Injection. L2SQL uses proper SQLParameters, as long as you use the squigglies and don't just concatenate the strings yourself.