search

sqlkata / docs

SqlKata documentation
12 stars 13 forks source link

Outer Apply Situations - How to Handle "FOR JSON PATH" and "FOR XML PATH" #12

Open MichaelTimbes opened 1 year ago

MichaelTimbes commented 1 year ago

Hi, I've been trying to figure out how to handle generating primarily JSON from an outer apply statement with SqlKata. The outer apply, I don't have issues with, but I'm not sure how to turn this query for example into a SqlKata version if possible, thanks!

SELECT
  c.ID
, c.Name
, pr.Name AS ProviderName
, FooSatelliteDetails.JsonValue
FROM Connections c WITH (NOLOCK)
INNER JOIN Provider pr ON pr.ID = pc.ProviderID
INNER JOIN FooSatelliteTable fst WITH (NOLOCK) ON fst.ConnectionID = pc.ID
CROSS APPLY (
    SELECT
      fd.ID
    , fd.[Name]
    FROM FooSatelliteDetailTable fd 
    WHERE fd.RelatedSatelliteID = fst.ID
    FOR JSON PATH
)FooSatelliteDetails(JsonValue)