Base solution for your next web application
Open Closed

produced sql #120


User avatar
0
ipekuni created

Hi, i have 3 entities: StudentRequest, FormType, and User (abp default) StudentRequest is related to FormType through FormTypeId, and related to User through UserId.

i have one record in db and i was expecting the following linq statement to get me what i want but it returns nothing. var toReturn = this._studentRequestRepository.GetAll() .Include(p => p.FormType) .Include(p => p.User) .WhereIf(input.UserId > 0, t => t.UserId == input.UserId) .WhereIf(!string.IsNullOrEmpty(input.Manager), t => t.FormType.Manager == input.Manager) .ToList();

i looked at the produced sql and there are a lot of tenantid related extra AND OR statements. Not sure what is causing the issue. Do you see anything wrong with the statement?

thanks -ulker


11 Answer(s)
  • User Avatar
    0
    hikalkan created
    Support Team

    As I know, you're not using multi-tenancy. So, these extra parameters will not effect you normally. Your query also seems correct. It seems related to EF. Problem may be related to this condition:

    .WhereIf(!string.IsNullOrEmpty(input.Manager), t => t.FormType.Manager == input.Manager)
    

    Can you try without it? Also, you can share generated SQL to check it. In such situations, I advice to go from generated SQL and suspected remove conditions step by step and run again.

  • User Avatar
    0
    ipekuni created

    no, no multi tenancy.

    Manager is a string field and in my example input.Manager is null so that statement is not even included in generated sql. here it is: i hate the sql ef generates but it is what it is. i dont see anything wrong with it unless an unnecessary statement is added and its causing the null result.

    exec sp_executesql N'SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[UserId] AS [UserId], 
        [Extent1].[FormTypeId] AS [FormTypeId], 
        [Extent1].[Notes] AS [Notes], 
        [Extent1].[EnglishCount] AS [EnglishCount], 
        [Extent1].[TurkishCount] AS [TurkishCount], 
        [Extent1].[IsReady] AS [IsReady], 
        [Extent1].[IsDeleted] AS [IsDeleted], 
        [Extent1].[DeleterUserId] AS [DeleterUserId], 
        [Extent1].[DeletionTime] AS [DeletionTime], 
        [Extent1].[LastModificationTime] AS [LastModificationTime], 
        [Extent1].[LastModifierUserId] AS [LastModifierUserId], 
        [Extent1].[CreationTime] AS [CreationTime], 
        [Extent1].[CreatorUserId] AS [CreatorUserId], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[Name] AS [Name], 
        [Extent2].[Manager] AS [Manager], 
        [Extent2].[PeopleToNotify] AS [PeopleToNotify], 
        [Extent2].[IsForStudent] AS [IsForStudent], 
        [Extent2].[IsForStaff] AS [IsForStaff], 
        [Extent3].[Id] AS [Id2], 
        [Extent3].[ProfilePictureId] AS [ProfilePictureId], 
        [Extent3].[ShouldChangePasswordOnNextLogin] AS [ShouldChangePasswordOnNextLogin], 
        [Extent3].[TenantId] AS [TenantId], 
        [Extent3].[Name] AS [Name1], 
        [Extent3].[Surname] AS [Surname], 
        [Extent3].[UserName] AS [UserName], 
        [Extent3].[Password] AS [Password], 
        [Extent3].[EmailAddress] AS [EmailAddress], 
        [Extent3].[IsEmailConfirmed] AS [IsEmailConfirmed], 
        [Extent3].[EmailConfirmationCode] AS [EmailConfirmationCode], 
        [Extent3].[PasswordResetCode] AS [PasswordResetCode], 
        [Extent3].[LastLoginTime] AS [LastLoginTime], 
        [Extent3].[IsActive] AS [IsActive], 
        [Extent3].[IsDeleted] AS [IsDeleted1], 
        [Extent3].[DeleterUserId] AS [DeleterUserId1], 
        [Extent3].[DeletionTime] AS [DeletionTime1], 
        [Extent3].[LastModificationTime] AS [LastModificationTime1], 
        [Extent3].[LastModifierUserId] AS [LastModifierUserId1], 
        [Extent3].[CreationTime] AS [CreationTime1], 
        [Extent3].[CreatorUserId] AS [CreatorUserId1]
        FROM    (SELECT [Var_4].[Id] AS [Id], [Var_4].[UserId] AS [UserId], [Var_4].[FormTypeId] AS [FormTypeId], [Var_4].[Notes] AS [Notes], [Var_4].[EnglishCount] AS [EnglishCount], [Var_4].[TurkishCount] AS [TurkishCount], [Var_4].[IsReady] AS [IsReady], [Var_4].[IsDeleted] AS [IsDeleted], [Var_4].[DeleterUserId] AS [DeleterUserId], [Var_4].[DeletionTime] AS [DeletionTime], [Var_4].[LastModificationTime] AS [LastModificationTime], [Var_4].[LastModifierUserId] AS [LastModifierUserId], [Var_4].[CreationTime] AS [CreationTime], [Var_4].[CreatorUserId] AS [CreatorUserId]
            FROM [dbo].[StudentRequests] AS [Var_4]
            WHERE ([Var_4].[IsDeleted] = @DynamicFilterParam_SoftDelete_IsDeleted) OR (@DynamicFilterParam_SoftDelete_DynamicFilterIsDisabled IS NOT NULL) ) AS [Extent1]
        INNER JOIN [dbo].[FormTypes] AS [Extent2] ON [Extent1].[FormTypeId] = [Extent2].[Id]
        INNER JOIN  (SELECT [Var_5].[Id] AS [Id], [Var_5].[ProfilePictureId] AS [ProfilePictureId], [Var_5].[ShouldChangePasswordOnNextLogin] AS [ShouldChangePasswordOnNextLogin], [Var_5].[TenantId] AS [TenantId], [Var_5].[Name] AS [Name], [Var_5].[Surname] AS [Surname], [Var_5].[UserName] AS [UserName], [Var_5].[Password] AS [Password], [Var_5].[EmailAddress] AS [EmailAddress], [Var_5].[IsEmailConfirmed] AS [IsEmailConfirmed], [Var_5].[EmailConfirmationCode] AS [EmailConfirmationCode], [Var_5].[PasswordResetCode] AS [PasswordResetCode], [Var_5].[LastLoginTime] AS [LastLoginTime], [Var_5].[IsActive] AS [IsActive], [Var_5].[IsDeleted] AS [IsDeleted], [Var_5].[DeleterUserId] AS [DeleterUserId], [Var_5].[DeletionTime] AS [DeletionTime], [Var_5].[LastModificationTime] AS [LastModificationTime], [Var_5].[LastModifierUserId] AS [LastModifierUserId], [Var_5].[CreationTime] AS [CreationTime], [Var_5].[CreatorUserId] AS [CreatorUserId]
            FROM [dbo].[AbpUsers] AS [Var_5]
            WHERE ((([Var_5].[TenantId] IS NULL) AND (@DynamicFilterParam_MayHaveTenant_tenantId IS NULL)) OR (([Var_5].[TenantId] IS NOT NULL) AND ([Var_5].[TenantId] = @DynamicFilterParam_MayHaveTenant_tenantId)) OR (@DynamicFilterParam_MayHaveTenant_DynamicFilterIsDisabled IS NOT NULL)) AND (([Var_5].[IsDeleted] = @DynamicFilterParam_SoftDelete_IsDeleted) OR (@DynamicFilterParam_SoftDelete_DynamicFilterIsDisabled IS NOT NULL)) ) AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id]',N'@DynamicFilterParam_SoftDelete_IsDeleted bit,@DynamicFilterParam_SoftDelete_DynamicFilterIsDisabled bit,@DynamicFilterParam_MayHaveTenant_tenantId int,@DynamicFilterParam_MayHaveTenant_DynamicFilterIsDisabled bit',@DynamicFilterParam_SoftDelete_IsDeleted=0,@DynamicFilterParam_SoftDelete_DynamicFilterIsDisabled=NULL,@DynamicFilterParam_MayHaveTenant_tenantId=1,@DynamicFilterParam_MayHaveTenant_DynamicFilterIsDisabled=NULL
    
  • User Avatar
    0
    ipekuni created

    SELECT * FROM StudentRequests AS sr INNER JOIN FormTypes AS ft ON ft.Id = sr.FormTypeId INNER JOIN AbpUsers AS au ON au.Id = sr.UserId WHERE sr.UserId=1

    this is the sql i need and it returns one record.

  • User Avatar
    0
    hikalkan created
    Support Team

    Hi

    EF generated queries are like that, I also don't like it but it's EF.

    It seems that your user's TenantId is not 1. Can your check it? It's normally set to 1 for single tenant apps. But, maybe you inserted it into database manually?

  • User Avatar
    0
    ipekuni created

    you are right, userid 1 is the host admin and its tenantid was null.

    one more related question. my entity is fully audited. so creator id is coming by default. so i dont think i need to keep another field to keep track of the owner of the entity, right? can i just use it and relate it to the user table over CreatorUserId field?

    thanks -ulker

  • User Avatar
    0
    hikalkan created
    Support Team

    Hi,

    Yes, purpose of CreatorUserId is that. If you want to add a CreatorUser navigation property on an entity, derive it from FullAuditedEntity<int, User> (int may change based on your PK). Then it will include CreatorUser, LastModifierUser and DeleterUser properties (in addition to CreatorUserId, LastModifierUserId and DeleterUserId). Surely, you could add these properties yourself, but this is a shortcut.

    See documentation for more: <a class="postlink" href="http://www.aspnetboilerplate.com/Pages/Documents/Entities#DocAuditing">http://www.aspnetboilerplate.com/Pages/ ... ocAuditing</a>

    "All audit interfaces and classes have a generic version for defining navigation property to your User entity (like ICreationAudited<TUser> and FullAuditedEntity<TPrimaryKey, TUser>). "

  • User Avatar
    0
    kythor created

    I'm a bit confused by this.

    I have a custom entity Article inheriting FullAuditedEntity. So the fields CreatorUserId,... are created in the db. Now I want to get a list of articles, showing the Creator User Name in the list.

    So do I add this to ArticleListDto:

    public FullAuditedEntity<int, User> CreationUser { get; set; }

    ??

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    Don't put it in you DTO, because it represents an Entity and putting Entities in DTOs is not good. Just use LinQ join to get CreatorUserName from Users.

  • User Avatar
    0
    kythor created

    can you please show me how to add this into this code:

    var articles = _articleRepository .GetAll() .OrderByDescending(a => a.ViewCount) .Take(howMany) .ToList();

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    You can do it in two ways,

    1 ) Include CreatoruUser to your query

    var articles = _articleRepository
                        .GetAll()
                        .Include(e => e.CreatorUser)
                        .OrderByDescending(a => a.ViewCount)
                        .Take(howMany)
                        .ToList();
    

    Then map result of this query to your Dto.

    1. You can join to user table manually and get only the fields you want
    var articles = (from article in _articleRepository.GetAll()
                    join creatorUser in _userRepository.GetAll() on article.CreatorUserId equals creatorUser.Id
                    orderby NameOfField
                    select new NameOfYourDto
                    {
                        CreatorUserName = creatorUser.UserName,
                        SomeField = article.SomeField,
                        ...
                    }).Take(howMany);
    
  • User Avatar
    0
    kythor created

    It works, thanks!