none
LINQ to DataSet with multiple inner joins

    Question

  • I have been tasked with moving a desktop app to a more portable platform.  The desktop app looks at several tables on a SQL server. The tables are small, and I have them in datatables in memory now (they will not be updated unless the program is).  Next, I need to query them to get the same results as the desktop app.  The following is a paired down version of the desktop query:

    DECLARE @DecFluidFlow DECIMAL, @sideOfBodyCondition BIT
    SET @DecFluidFlow = 250.1
    SET @sideOfBodyCondition = 1
    	SELECT
    		 S.ModelNumber AS ModelNumber, S.IsStandardProduct AS IsStandardProduct, S.ImageName AS ImageName, S.DatasheetUrl AS DatasheetUrl
    		,SP.FluidFlow AS FluidFlow, SP.PressureDrop AS PressureDrop, 
    	FROM vw_Stilts AS S
    	INNER JOIN (
    		SELECT SP.*
    		FROM   StiltPerformances AS SP
    		INNER JOIN (
                SELECT StiltDimensionId ,MIN(ABS(@DecFluidFlow - FluidFlow)) AS TargetFluidFlowDelta
                FROM   StiltPerformances
                WHERE  [Include] != 0  
    			  AND  ((@sideOfBodyCondition = 1 AND FluidFlow >= 0) OR (@sideOfBodyCondition = 0 AND FluidFlow <= 0))
                GROUP BY StiltDimensionId
            ) AS SQ ON (SP.StiltDimensionId = SQ.StiltDimensionId AND ABS(@DecFluidFlow - SP.FluidFlow) = SQ.TargetFluidFlowDelta )
        WHERE [Include] != 0 
     	  AND ((@sideOfBodyCondition = 1 AND SP.FluidFlow >= 0) OR (@sideOfBodyCondition = 0 AND SP.FluidFlow <= 0))) AS SP ON S.StiltDimensionId = SP.StiltDimensionId
    WHERE [Length] > 0 
      AND S.ManufacturerId = 1
    

    I have been looking at this for several days trying to wrap my apparently feeble brain around it.  I started with trying to get the inner most join, and I came up with this:

    SPs = from StiltPerformance in StiltPerformances.AsEnumerable()
             where StiltPerformance["Include"].ToString() == "1" && int.Parse(StiltPerformance["FluidFlow"].ToString()) >= 0
             join sp in StiltPerformances.AsEnumerable() on StiltPerformance["Id"] equals sp["Id"]
             select StiltPerformance;

    I am not even sure I got this correct.  These join translations are blowing my mind...  Any help in getting me pointed in the right direction would be greatly appreciated.

    Tuesday, July 16, 2013 8:27 PM

Answers

  • Hi Opus,

    It is not very easy to translate this SQL statement to LINQ without debugging.

    I write part of the inner most join with group statement. Hope it may be a little helpful.

    from sp in StiltPerformances
    join sq in 
    	(
    		from sp1 in StiltPerformances
    		where sp1[Include] != 0 && ( (sideOfBodyCondition == 1 && sp1[FluidFlow] >= 0) ||
    								(sideOfBodyCondition == 0 && sp1[FluidFlow] <= 0) )
    		group sp1 by sp1[StiltDimensionId]
    		into g
    		select new {g.Key, TargetFluidFlowDelta = g.Average(sp2 => DecFluidFlow - sp2[FluidFlow])}
    	)
    on sp[StiltDimensionId] equals sq[StiltDimensionId]
    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Friday, July 19, 2013 10:00 AM
    Moderator