none
trying to mimic Transact-SQL RRS feed

  • Question

  • hello,

    The question is at the end...

    Here is a Transact-SQL query (I hope it will be enough to understand the underlying model (which is not mine))

    select 
        idDelais, IdUtilisateur, Count, LastAlert 
    from
        tableDel dl
    	left join tableF f on dl.IdFichier = f.idFichier
    	left join (
    	    select 
    		    IdDeadLine, IdUser, 
    			count(1) as Count, 
    			max(CreationDate) as LastAlert 
    		from DeadLineAlerts 
    		group by IdDeadLine, idUser 
    	) dlStatus on dl.idDelais = IdDeadLine and f.idUtilisateur = IdUser

    And now my 2 linq to entities tries, first:

    from dl in _repo.DeadLines
    select new DeadLineAndStatus {
        DeadLine = dl,
        Status = new DeadLineStatus {
            Count = (from alert in dl.Alerts where alert.IdUser == dl.File.IdUser select alert.CreationDate).Count(),
            LastAlert = (from alert in dl.Alerts where alert.IdUser == dl.File.IdUser select alert.CreationDate).Max()
        }
    }

    which is translated as :

    SELECT 
    [Project1].[idDelais] AS [idDelais], 
    [Project1].[idFichier] AS [idFichier], 
    [Project1].[intituleDelais] AS [intituleDelais], 
    [Project1].[DateDelais] AS [DateDelais], 
    [Project1].[Acquitter] AS [Acquitter], 
    [Project1].[C1] AS [C1], 
    (SELECT 
    	MAX([Extent4].[CreationDate]) AS [A1]
    	FROM  [dbo].[DeadLineAlerts] AS [Extent4]
    	INNER JOIN [dbo].[tableF] AS [Extent5] ON [Extent4].[IdUser] = [Extent5].[idUtilisateur]
    	WHERE ([Project1].[idFichier] = [Extent5].[idFichier]) AND ([Project1].[idDelais] = [Extent4].[IdDeadLine])) AS [C2]
    FROM ( SELECT 
    	[Extent1].[idDelais] AS [idDelais], 
    	[Extent1].[idFichier] AS [idFichier], 
    	[Extent1].[intituleDelais] AS [intituleDelais], 
    	[Extent1].[DateDelais] AS [DateDelais], 
    	[Extent1].[Acquitter] AS [Acquitter], 
    	(SELECT 
    		COUNT(1) AS [A1]
    		FROM  [dbo].[DeadLineAlerts] AS [Extent2]
    		INNER JOIN [dbo].[tableF] AS [Extent3] ON [Extent2].[IdUser] = [Extent3].[idUtilisateur]
    		WHERE ([Extent1].[idFichier] = [Extent3].[idFichier]) AND ([Extent1].[idDelais] = [Extent2].[IdDeadLine])) AS [C1]
    	FROM [dbo].[tableDel] AS [Extent1]
    )  AS [Project1]

    Not bad... but two sub-queries when only one in TSQL

    second (pure mimic of sql):

    from dl in _repo.DeadLines
    join dlag in (
        from dla in _repo.DeadLineAlerts
        group dla by new { IdUser = dla.IdUser, IdDeadLine = dla.IdDeadLine } into dlaGroup
        select new {
            dlaGroup.Key,
            Count = dlaGroup.Count(),
            LastAlert = dlaGroup.Max(dla => dla.CreationDate)
        }) on new { dl.IdDeadLine, dl.File.UserInCharge.IdUser } equals
                new { dlag.Key.IdDeadLine, dlag.Key.IdUser } into dlGroup
    select new DeadLineAndStatus {
        DeadLine = dl,
        Status = (from state in dlGroup select new DeadLineStatus {Count = state.Count, LastAlert = state.LastAlert}).FirstOrDefault()
    }

    which is transalted as:

    SELECT 
    [Extent1].[idDelais] AS [idDelais], 
    [Extent1].[idFichier] AS [idFichier], 
    [Extent1].[intituleDelais] AS [intituleDelais], 
    [Extent1].[DateDelais] AS [DateDelais], 
    [Extent1].[Acquitter] AS [Acquitter], 
    [Limit1].[IdDeadLine] AS [IdDeadLine], 
    [Limit1].[C2] AS [C1], 
    [Limit1].[C1] AS [C2]
    FROM  [dbo].[tableDel] AS [Extent1]
    OUTER APPLY  (SELECT TOP (1) 
    	 CAST( [GroupBy1].[A2] AS datetime2) AS [C1], 
    	[GroupBy1].[K1] AS [IdDeadLine], 
    	[GroupBy1].[A1] AS [C2]
    	FROM    (SELECT 
    		[Extent2].[IdDeadLine] AS [K1], 
    		[Extent2].[IdUser] AS [K2], 
    		COUNT(1) AS [A1], 
    		MAX([Extent2].[CreationDate]) AS [A2]
    		FROM [dbo].[DeadLineAlerts] AS [Extent2]
    		GROUP BY [Extent2].[IdDeadLine], [Extent2].[IdUser] ) AS [GroupBy1]
    	INNER JOIN [dbo].[tableF] AS [Extent3] ON [Extent3].[idUtilisateur] = [GroupBy1].[K2]
    	LEFT OUTER JOIN  (SELECT 
    		[Extent4].[idFichier] AS [idFichier]
    		FROM [dbo].[tableF] AS [Extent4]
    		WHERE [Extent1].[idFichier] = [Extent4].[idFichier] ) AS [Project1] ON 1 = 1
    	WHERE ([Extent1].[idFichier] = [Extent3].[idFichier]) AND ([Extent1].[idDelais] = [GroupBy1].[K1]) ) AS [Limit1]

    which does the job but... imho... will never appear somewhere else by an human analysis.

    So how to write the link query to use only one sub query in sql ?

    thank you in advance

    thierry


    thierry


    Sunday, March 25, 2012 8:07 PM

Answers

  • Linq always create complex T-SQL, it won't create the exact T-SQL you want. Try to test the performance of the execution, if the performance is not bad, ignore it. Linq bring us convenience, so we can't force it to do something it can't. If the performance can't be beared, execute T-SQL in EF directly.

    Fighting like Allen Iverson. Neve give up!

    Tuesday, March 27, 2012 2:47 AM
  • Hi,

    If you group Alerts by UserId, you might be able to compute Count and LastAlert using a single request. Has Hero said, how complex is the query doesn't matter. Just check how well it performs...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, March 27, 2012 5:51 PM

All replies

  • Linq always create complex T-SQL, it won't create the exact T-SQL you want. Try to test the performance of the execution, if the performance is not bad, ignore it. Linq bring us convenience, so we can't force it to do something it can't. If the performance can't be beared, execute T-SQL in EF directly.

    Fighting like Allen Iverson. Neve give up!

    Tuesday, March 27, 2012 2:47 AM
  • Hi,

    If you group Alerts by UserId, you might be able to compute Count and LastAlert using a single request. Has Hero said, how complex is the query doesn't matter. Just check how well it performs...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, March 27, 2012 5:51 PM