none
I wanted to get this done through the LINQ to SQL RRS feed

  • Question

  • Can someone please translate this query to LINQ, I started to learn capabilities of LINQ and wanted to know wthether I could do this with LINQ

    Here, I use a CTE and use it in a SELECT query where I wanted to get data on latest modified date (ModifiedDateTime).

    In this context there are multipe records for a same where clause and wanted to get only latest modified record (ModifiedDateTime).


    WITH
     MyCTE(
     [Name],OrgUnitId,LastUpdatedUserId,OrgLevel,
     LastUpdatedDateTime,FinancialQuarterCode,ModifiedDateTime)
     AS
     (
     SELECT
     A.[Name], A.OrgUnitId, A.LastUpdatedUserId, A.OrgLevel,
     A.LastUpdatedDateTime,C.FinancialQuarterCode,C.ModifiedDateTime
     FROM dbo.OrgUnits A
     INNER JOIN dbo.Deals B
     ON A.OrgUnitId = B.OrgUnitId
     INNER JOIN dbo.DealsForecasts C
     ON B.DealId = C.DealId
     WHERE A.LastUpdatedUserId IS NOT NULL
     AND A.LastUpdatedDateTime IS NOT NULL
     AND A.OrgUnitId = 1254545'
     AND C.FinancialQuarterCode = 1999.25
    )

    SELECT
    [Name],OrgUnitId,LastUpdatedUserId,OrgLevel,
    LastUpdatedDateTime,FinancialQuarterCode,ModifiedDateTime
    FROM MyCTE
    WHERE ModifiedDateTime = (SELECT MAX(ModifiedDateTime)
    FROM MyCTE)

    Wednesday, September 30, 2009 4:02 AM

Answers

  • Thank you Syed, Highly appreciate your prompt response...
    I'll try this and let you know.

    NSJ
    • Marked as answer by NSJ SL Wednesday, September 30, 2009 5:26 AM
    Wednesday, September 30, 2009 5:26 AM

All replies

  • Hi,

    How about something like:

    var ctequery = 
    from A in db.OrgUnits
     join B in db.Deals on A.OrgUnitId equals B.OrgUnitId
     join C in db.DealsForecasts on B.DealId equals C.DealId
     where
     	A.LastUpdatedUserId != null
     	&& A.LastUpdatedDateTime != null
     	&& A.OrgUnitId == "1254545"
     	&& C.FinancialQuarterCode == 1999.25
     select new
     {
    	A.Name, A.OrgUnitId, A.LastUpdatedUserId, A.OrgLevel,
    	A.LastUpdatedDateTime,C.FinancialQuarterCode,C.ModifiedDateTime
     };
    
    var query = 
    	from q in ctequery
    	where q.ModifiedDateTime = ( ctequery.Max(q2=>q2.ModifiedDateTime) )
    	select q;

    Hope that helps.

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Wednesday, September 30, 2009 5:15 AM
  • Thank you Syed, Highly appreciate your prompt response...
    I'll try this and let you know.

    NSJ
    • Marked as answer by NSJ SL Wednesday, September 30, 2009 5:26 AM
    Wednesday, September 30, 2009 5:26 AM