produced sql #120 | Support Center | ASP.NET Zero Support

ipekuni created 9 years ago

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)

0
hikalkan created 9 years ago
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.

ipekuni created 9 years ago

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

0

ipekuni created 9 years ago

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.
0

hikalkan created 9 years ago
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?
0

ipekuni created 9 years ago

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
0

hikalkan created 9 years ago
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>). "
0

kythor created 8 years ago

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; }

??
0

ismcagdas created 8 years ago
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.
0

kythor created 8 years ago

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

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

ismcagdas created 8 years ago

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.

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);

0

kythor created 8 years ago

It works, thanks!

Have an answer to this question? Log in and write your answer.