Perform SELECT statement on another SELECT using LINQ

1
votes

In my .NET Core app, I have the following in LINQ:

 return  await db.ApplicationUsers.Where(u=>u.Name.Contains(name) && !u.Deleted && u.AppearInSearch)
                                    .OrderByDescending(u => u.Verified)
                                    .Skip(page * recordsInPage)
                                    .Take(recordsInPage)
                                    .Select(u => new UserSearchResult()
                                    {
                                        Name = u.Name,
                                        Verified = u.Verified,
                                        PhotoURL = u.PhotoURL,
                                        UserID = u.Id,
                                        Subdomain = u.Subdomain
                                    }).ToListAsync();

which translates to the following SQL:

 SELECT [t].[Name], [t].[Verified], 
        [t].[PhotoURL], [t].[Id], 
        [t].[Subdomain]  
 FROM 
      (SELECT [u0].*      
       FROM [AspNetUsers] AS [u0]      
       WHERE (((CHARINDEX('khaled', [u0].[Name]) > 0) OR ('khaled' = N''))
         AND ([u0].[Deleted] = 0)) 
         AND ([u0].[AppearInSearch] = 1)      
       ORDER BY [u0].[Verified] DESC      
       OFFSET 10 ROWS 
       FETCH NEXT 10 ROWS ONLY  ) AS [t]

But due to performance issues, Microsoft support suggested that I only query columns of fixed length (not varchar(max)). I was asked to change the SQL query to:

  SELECT [t].[Name], [t].[Verified], 
         [t].[PhotoURL] , [t].[Id], [t].[Subdomain]
  FROM 
        (Select u0.Name, u0.Verified, u0.PhotoURL, u0.id, u0.Subdomain,
             u0.Deleted, u0.AppearInSearch FROM [AspNetUsers] ) As [u0]
  WHERE (((CHARINDEX('khaled', [u0].[Name]) > 0) OR ('khaled' = N'')) 
    AND ([u0].[Deleted] = 0)) 
    AND ([u0].[AppearInSearch] = 1) 
  ORDER BY [u0].[Verified] DESC      
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  ) AS [t]

Which is a SELECT on another SELECT. Unfortunately I don't know how to do it in LINQ. Can someone please guide me how to make the second SQL query in LINQ?

Thank you

2
c#sqllinqlinq-to-sqlasp.net-core
First of all, I would check execution plans of both queries in the profiler. I doubt there's any difference. – JustAndrei
What's the difference between those 2 sql queries? First is select on another select and second one is the same. – Evk
There only difference I noticed is that * in the sub-select is replaced with the strict list of columns. I'm pretty sure that will have totally NO effect on the SQL server query execution plan. – JustAndrei
I agree with @JustAndrei . I woud be very surprised if the second query was any faster / more efficient than the other. The query optimiser will automatically treat the former as the latter anyway. – mjwills
@JustAndrei I need to translate the SQL query to LINQ. Please advise if you can. – Techy

2 Answers

1
votes

First build the inner select:

var s1 = from u in db.AspNetUsers
          select new UserSearchResult
          {
              Name = u.Name,
              Verified = u.Verified,
              PhotoURL = u.PhotoURL,
              UserID = u.Id,
              Subdomain = u.Subdomain
         };

then use it in the outer select:

 return  await (from u1 in s1 
                where u.Name.Contains(name) && !u.Deleted && u.AppearInSearch
                orderby u.Verified
                select u1)
                 .Skip(page * recordsInPage)
                 .Take(recordsInPage);
0
votes

When you make the output of select query .ToList(), the output is a list again. Over this list you can apply another .Select(). So, as in your previous query, you can do it as:

 return  await db.ApplicationUsers.Where(u=>u.Name.Contains(name) && !u.Deleted && u.AppearInSearch)
                                .OrderByDescending(u => u.Verified)
                                .Skip(page * recordsInPage)
                                .Take(recordsInPage)
                                .Select(u => new UserSearchResult()
                                {
                                    Name = u.Name,
                                    Verified = u.Verified,
                                    PhotoURL = u.PhotoURL,
                                    UserID = u.Id,
                                    Subdomain = u.Subdomain
                                })
                                .ToList()
                                .Select(<your new query>)
                                .ToListAsync();