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

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.

Answer 1

Hi Ajones1981,

Welcome to the MSDN forum.

I think you can execute T-SQL directly against the database: http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

Have a nice day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Ajones1981,

I would suggest that you could put this T-SQL statements in a stored procedure which lives in Sql Server side and call it by Linq, it’s would be very easy and efficiently by this way, for more information about how to call stored procedure by linq you could refer to the following blog:

l  http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

Hope this could help you.

Best Regards,

---

Please mark this as answer if it helps with this issue!