Linq, type anonyme et redondance
Bonjour à tous,
Encore moi sur Linq ;-)
Je créé un type anonyme et j'aimerais optimizer. Je suppose que je ne m'y prend pas de la meilleure manière et j'aimerais corriger cela au plus vite.
Voici le code:
Code:
1 2 3 4 5 6 7 8 9 10 11
| var results = from f in DataContext.Forums
select new
{
f.Name,
NumberOfTopic = f.Messages.Where(m => m.Topic == null).Count(),
NumberOfMessage = f.Messages.Count(),
LastMessageDate = f.Messages.OrderBy(m => m.CreationDate).ThenBy(m => m.Id).FirstOrDefault().CreationDate,
LastMessageTitle = f.Messages.OrderBy(m => m.CreationDate).ThenBy(m => m.Id).FirstOrDefault().Title
};
return results; |
Et evidement, cela génère un sql avec une requete doublon:
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| SELECT [t0].[Name], (
SELECT COUNT(*)
FROM [dbo].[Messages] AS [t1]
WHERE ([t1].[TopicId] IS NULL) AND ([t1].[ForumId] = [t0].[Id])
) AS [NumberOfTopic], (
SELECT COUNT(*)
FROM [dbo].[Messages] AS [t2]
WHERE [t2].[ForumId] = [t0].[Id]
) AS [NumberOfMessage], (
SELECT [t4].[CreationDate]
FROM (
SELECT TOP (1) [t3].[CreationDate]
FROM [dbo].[Messages] AS [t3]
WHERE [t3].[ForumId] = [t0].[Id]
ORDER BY [t3].[CreationDate], [t3].[Id]
) AS [t4]
) AS [LastMessageDate], (
SELECT [t6].[Title]
FROM (
SELECT TOP (1) [t5].[Title]
FROM [dbo].[Messages] AS [t5]
WHERE [t5].[ForumId] = [t0].[Id]
ORDER BY [t5].[CreationDate], [t5].[Id]
) AS [t6]
) AS [LastMessageTitle]
FROM [dbo].[Forums] AS [t0] |
Comment modifier cela pour avoir un objet intermédiaire "Message" pour éviter de faire deux fois la même requete?
Merci à vous!