none
Sql to Linq conversion - is this possible and if so how? RRS feed

  • Question

  • I'm trying to convert this sql to a linq query:

    select 
    SystemReleases.SystemReleasesID, 
    SystemReleases.ReleaseTitle, 
    SystemReleases.Created,
    SystemReleases.CreatedBy,
    statuss.ReleaseStageValue
    from SystemReleases
    	outer apply
    	(
    		select top 1 *
    		from ReleaseStatus
    			inner join StageID
    				on StageID.ReleaseStageKey = ReleaseStatus.ReleaseStage
    		where ReleaseStatus.ReleaseID = SystemReleases.SystemReleasesID
    		order by ReleaseStatus.Created desc
    	) statuss

    I have tried Linqer but it says it Outer Apply cannot be converted. (The above SQL returns what I need)

    So, had a go at hand cranking the code and got to this:

    IQueryable<ReleaseData> q = (from systemReleases in SME.SystemReleases
                             join releaseStatus in SME.ReleaseStatus on systemReleases.SystemReleasesID equals
                                 releaseStatus.ReleaseID into r
                             orderby r.Select(c => c.Created).OrderByDescending(c => c).FirstOrDefault()
                             join stageLookups in SME.StageIDs on r.FirstOrDefault().ReleaseStage equals
                                 stageLookups.ReleaseStageKey
                             
                             select new ReleaseData
                                        {
                                            Releases = systemReleases,
                                            ReleaseStageLookups = stageLookups
                                        });
                        return q;

    Which returns a dataset with the correct number of rows but the second table (ReleaseStatus which has the multiple rows) returns the oldest row, not the newest. (Basically I need the newest ReleaseStatus.created entry)

    Any ideas what I need to do.

    Monday, July 30, 2012 11:07 AM

Answers

All replies