AnswerBun.com

Entity Framework Skip and Take didn't translated to SQL command

var allUseresInclude = _userService.Include(r => r.Profession);

var model = allUseresInclude.Select(s => new ViewModel.User
{
    Profession = s.Profession.Name,
    Id = s.Id,
    ProviderUserId = s.ProviderUserId,
    PlatformId = s.PlatformId,
    Email = s.Email,
    Password = s.Password,
    RefreshToken = s.RefreshToken,
    Name = s.Name,
    Surname = s.Surname,
    Fullname = s.Name + " " + s.Surname,
    Gender = s.Gender,
    ProfessionId = s.ProfessionId,
    BirthDate = s.BirthDate.ToString(),
    IdentityNumber = s.IdentityNumber,
    Phone = s.Phone,
    IsActive = s.IsActive,
    CreatedDate =  _common.DateTimeToTimeZone(s.CreatedDate),//.ToString("dd.MM.yyyy HH:mm"),
    EmailConfirmed = s.EmailConfirmed
});

var tt = model.OrderByDescending(o => o.CreatedDate).Skip(20).Take(30).ToList();

Model is a IQueryable variable. First, I am selecting the properties for my ViewModel then I try to filter my IQueryable. Query execution takes a while and I see correct results in UI, but in SQL the generated query doesn’t have offset or limit

    SELECT [r].[CreatedDate], [r.Profession].[Name] AS [Profession], [r].[Id], [r].[ProviderUserId], [r].[PlatformId], [r].[Email], [r].[Password], [r].[RefreshToken], [r].[Name], [r].[Surname], ([r].[Name] + N' ') + [r].[Surname] AS [Fullname], [r].[Gender], [r].[ProfessionId], CONVERT(VARCHAR(100), [r].[BirthDate]) AS [BirthDate], [r].[IdentityNumber], [r].[Phone], [r].[IsActive], [r].[EmailConfirmed]
FROM [User] AS [r]
LEFT JOIN [Profession] AS [r.Profession] ON [r].[ProfessionId] = [r.Profession].[Id]

When I fire this query, it returns all the tables so I guess my skip and take doesn’t work as expected. Am I doing something wrong?

Stack Overflow Asked by coderaty on December 29, 2020

1 Answers

One Answer

One of the reasons for this is using .ToList() or .Select() to early. So, that means you should check if your query really producing IQueryable.

var model = allUseresInclude.Select(s => new ViewModel.User
{
    Profession = s.Profession.Name,
    Id = s.Id,
    ProviderUserId = s.ProviderUserId,
    PlatformId = s.PlatformId,
    Email = s.Email,
    Password = s.Password,
    RefreshToken = s.RefreshToken,
    Name = s.Name,
    Surname = s.Surname,
    Fullname = s.Name + " " + s.Surname,
    Gender = s.Gender,
    ProfessionId = s.ProfessionId,
    BirthDate = s.BirthDate.ToString(),
    IdentityNumber = s.IdentityNumber,
    Phone = s.Phone,
    IsActive = s.IsActive,
    CreatedDate =  _common.DateTimeToTimeZone(s.CreatedDate),//.ToString("dd.MM.yyyy HH:mm"),
    EmailConfirmed = s.EmailConfirmed
});

In your code pay attention to _common.DateTimeToTimeZone this will force Entity Framework to be executed in the memory otherwise this cannot be executed and translated to the SQL query.

If this part is executed in the application memory that means Skip and Take are executed after that in the memory as well.

You should try with some solution like this one:

var model = allUseresInclude.OrderByDescending(o => o.CreatedDate).Skip(20).Take(30).Select(s => new ViewModel.User
{
    Profession = s.Profession.Name,
    Id = s.Id,
    ProviderUserId = s.ProviderUserId,
    PlatformId = s.PlatformId,
    Email = s.Email,
    Password = s.Password,
    RefreshToken = s.RefreshToken,
    Name = s.Name,
    Surname = s.Surname,
    Fullname = s.Name + " " + s.Surname,
    Gender = s.Gender,
    ProfessionId = s.ProfessionId,
    BirthDate = s.BirthDate.ToString(),
    IdentityNumber = s.IdentityNumber,
    Phone = s.Phone,
    IsActive = s.IsActive,
    CreatedDate =  _common.DateTimeToTimeZone(s.CreatedDate),//.ToString("dd.MM.yyyy HH:mm"),
    EmailConfirmed = s.EmailConfirmed
}).ToList();

Correct answer by Emin Mesic on December 29, 2020

Add your own answers!

Related Questions

Pandas dataframe only select the columns that have all True

2  Asked on December 11, 2020 by learningisfun

     

I want to create fixed sidebar but scroll does not work

5  Asked on December 11, 2020 by nav

     

How can I extract all PDF Tags related to content with Python?

2  Asked on December 11, 2020 by martin-thoma

   

React Fizz-Buzz

2  Asked on December 11, 2020 by kamil-kos

   

Update Subform based on Form Combo Box

0  Asked on December 10, 2020 by hazat-bangurah

   

Determine if __getattr__ is method or attribute call

3  Asked on December 10, 2020 by robin-orheden

   

How to implement category axis in matplotlib?

0  Asked on December 10, 2020 by fgg1991

   

Pre-fill the checkbox with url

2  Asked on December 10, 2020 by sevenine

       

Shell=false generates OsError

1  Asked on December 10, 2020 by azza-abidi

       

LINQ query orderBy a subquery

2  Asked on December 9, 2020 by golfbravo

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.