‫بهینه سازی کوئری‌های LINQ - بخش اول

public class User
{
   public int ID { get; set; }
   public string Name { get; set; }
   public int Age { get; set; }
}

public class Login
{
   public int ID { get; set; }
   public DateTime Date { get; set; }
   public int UserID { get; set; }
   public User User { get; set; }
}

int usersCount = 1200;
Random rnd = new Random();
for(int i=0; i<usersCount; i++)
{
   Users.Add(new User()
     {
       Name = $"User {i + 1}",
       Age = rnd.Next(10, i + 10) / 10
     });
}

SaveChanges();

$"Users: {Users.Count()}".Dump();

var usersID = Users.Select(x => x.ID).ToArray();

int loginsCount  = 20000;

for(int i=0; i<loginsCount; i++)
{
    Logins.Add(new Login()
    {
        UserID = usersID[rnd.Next(0, usersID.Length - 1)],
        Date = DateTime.Now.AddDays(rnd.Next(0, i))
    });

    if(i % 1000 == 0)
   {
      SaveChanges();
      $"Save {i + 1}".Dump();
   }
}

SaveChanges();
$"Logins: {Logins.Count()}".Dump();

var data =
(
   from u in Users
   join x in Logins on u.ID equals x.UserID into g
   from x in g.DefaultIfEmpty()
   select new
     {
        UserID = u.ID,
        Name = u.Name,
        Age = u.Age,
        Date = x.Date
     }
);

var result =
(
   from d in data
   group d by d.UserID into g
   select new
   {
       UserID = g.Key,
       Name = g.FirstOrDefault().Name,
       LoginsCount = g.Count(x => x.Date != null),
       LastLogin = g.Max(x => (DateTime?) x.Date) ?? null
   }
);

SELECT 
    [Project7].[ID] AS [ID], 
    [Project7].[C2] AS [C1], 
    [Project7].[C3] AS [C2], 
    [Project7].[C1] AS [C3]
    FROM ( SELECT 
        [Project6].[ID] AS [ID], 
        CASE WHEN ([Project6].[C3] IS NULL) THEN CAST(NULL AS datetime2) ELSE [Project6].[C4] END AS [C1], 
        [Project6].[C1] AS [C2], 
        [Project6].[C2] AS [C3]
        FROM ( SELECT 
            [Project5].[ID] AS [ID], 
            [Project5].[C1] AS [C1], 
            [Project5].[C2] AS [C2], 
            [Project5].[C3] AS [C3], 
            (SELECT 
                MAX( CAST( [Extent9].[Date] AS datetime2)) AS [A1]
                FROM  [dbo].[Users] AS [Extent8]
                LEFT OUTER JOIN [dbo].[Logins] AS [Extent9] ON [Extent8].[ID] = [Extent9].[UserID]
                WHERE [Project5].[ID] = [Extent8].[ID]) AS [C4]
            FROM ( SELECT 
                [Project4].[ID] AS [ID], 
                [Project4].[C1] AS [C1], 
                [Project4].[C2] AS [C2], 
                (SELECT 
                    MAX( CAST( [Extent7].[Date] AS datetime2)) AS [A1]
                    FROM  [dbo].[Users] AS [Extent6]
                    LEFT OUTER JOIN [dbo].[Logins] AS [Extent7] ON [Extent6].[ID] = [Extent7].[UserID]
                    WHERE [Project4].[ID] = [Extent6].[ID]) AS [C3]
                FROM ( SELECT 
                    [Project3].[ID] AS [ID], 
                    [Project3].[C1] AS [C1], 
                    (SELECT 
                        COUNT(1) AS [A1]
                        FROM [dbo].[Logins] AS [Extent5]
                        WHERE [Project3].[ID] = [Extent5].[UserID]) AS [C2]
                    FROM ( SELECT 
                        [Distinct1].[ID] AS [ID], 
                        (SELECT TOP (1) 
                            [Extent3].[Name] AS [Name]
                            FROM  [dbo].[Users] AS [Extent3]
                            LEFT OUTER JOIN [dbo].[Logins] AS [Extent4] ON [Extent3].[ID] = [Extent4].[UserID]
                            WHERE [Distinct1].[ID] = [Extent3].[ID]) AS [C1]
                        FROM ( SELECT DISTINCT 
                            [Extent1].[ID] AS [ID]
                            FROM  [dbo].[Users] AS [Extent1]
                            LEFT OUTER JOIN [dbo].[Logins] AS [Extent2] ON [Extent1].[ID] = [Extent2].[UserID]
                        )  AS [Distinct1]
                    )  AS [Project3]
                )  AS [Project4]
            )  AS [Project5]
        )  AS [Project6]
    )  AS [Project7]
    ORDER BY [Project7].[C3] ASC, [Project7].[ID] ASC

var data =
(
  from x in Logins
  group x by x.UserID into g
  orderby g.Key descending
  select new
  {
    UserID = g.Key,
    LoginsCount = g.Count(),
    LastLogin = g.Max(d => d.Date)
  }
);

var result =
(
  from u in Users
  join d in data on u.ID equals d.UserID into g
  from d in g.DefaultIfEmpty()
  select new
  {
    UserID = u.ID,
    LoginsCount = d != null ? d.LoginsCount : 0,
    LastLogin = d != null ? (DateTime?)d.LastLogin : null
  }
);

SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        CASE WHEN ([GroupBy1].[K1] IS NOT NULL) THEN [GroupBy1].[A1] ELSE 0 END AS [C1], 
        CASE WHEN ([GroupBy1].[K1] IS NOT NULL) THEN  CAST( [GroupBy1].[A2] AS datetime2) END AS [C2]
        FROM  [dbo].[Users] AS [Extent1]
        LEFT OUTER JOIN  (SELECT 
            [Extent2].[UserID] AS [K1], 
            COUNT(1) AS [A1], 
            MAX([Extent2].[Date]) AS [A2]
            FROM [dbo].[Logins] AS [Extent2]
            GROUP BY [Extent2].[UserID] ) AS [GroupBy1] ON [Extent1].[ID] = [GroupBy1].[K1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC, [Project1].[ID] ASC