CODEWITHSUNDEEP
c#sql.netsql-serverentity-framework
pedrodotnet
pedrodotnet

Reputation: 818

.Net Entity Framework Check if value exists in different tables Optimization

I have the following function that checks if an Id exists in 4 different tables and returns a boolean value:

 public bool CheckIfUsed(int targetId)
        {
            bool isUsedOnTable1 = false;
            bool isUsedOnTable2 = false;
            bool isUsedOnTable3 = false;
            bool isUsedOnTable4 = false;

            isUsedOnTable1 = this.DbContext.table1.Select(target => target.TargetID).Where(TargetID => TargetID == targetId).Count() > 0;
            isUsedOnTable2 = this.DbContext.table2.Select(target => target.TargetID).Where(TargetID => TargetID == targetId).Count() > 0;
            isUsedOnTable3 = this.DbContext.table3.Select(target => target.TargetId).Where(targetID => targetID == targetId).Count() > 0;
            isUsedOnTable4 = this.DbContext.table4.Select(target => target.TargetID).Where(targetID => targetID == targetId).Count() > 0;

            return (isUsedOnTable1 || isUsedOnTable2 || isUsedOnTable3 || isUsedOnTable4);
        }

This approach technically works, the problem here is the performance issues of having 4 different queries each time this function is executed. Is there any way to check the four tables simultaneously or any other way to improve the performance?

Thanks in advance.

Upvotes: 0

Views: 1121

Answers (3)

MesutAtasoy
MesutAtasoy

Reputation: 822

We assume that the first query's result returns true, you don't have to query another tables. In addition, you don't need count of data. That's way, It should be

  public async Task<bool> CheckIfUsed(int targetId)
        {
            var isUsed = false;
            isUsed = await this.DbContext.table1.AsNoTracking().AnyAsync(TargetID => TargetID == targetId);
            if (isUsed)
                return isUsed;
            
            isUsed = await this.DbContext.table2.AsNoTracking().AnyAsync(TargetID => TargetID == targetId);
            if (isUsed)
                return isUsed;
            
            isUsed = await this.DbContext.table3.AsNoTracking().AnyAsync(TargetID => TargetID == targetId);
            if (isUsed)
                return isUsed;
            
            isUsed = await this.DbContext.table4.AsNoTracking().AnyAsync(TargetID => TargetID == targetId);
            return isUsed;
        }

Upvotes: 1

Farhad Zamani
Farhad Zamani

Reputation: 5861

You can use .AsNoTracking() and async to disable track entities and asynchronous execution to improve performance like this

public async Task<bool> CheckIfUsed(int targetId)
{
    Task<int> isUsedOnTable1Task = false;
    Task<int> isUsedOnTable2Task = false;
    Task<int> isUsedOnTable3Task = false;
    Task<int> isUsedOnTable4Task = false;

    bool isUsedOnTable1 = false;
    bool isUsedOnTable2 = false;
    bool isUsedOnTable3 = false;
    bool isUsedOnTable4 = false;

    isUsedOnTable1Task = this.DbContext.table1.AsNoTracking().Select(target => target.TargetID).Where(TargetID => TargetID == targetId).CountAsync();
    isUsedOnTable2Task = this.DbContext.table2.AsNoTracking().Select(target => target.TargetID).Where(TargetID => TargetID == targetId).CountAsync();
    isUsedOnTable3Task = this.DbContext.table3.AsNoTracking().Select(target => target.TargetId).Where(targetID => targetID == targetId).CountAsync();
    isUsedOnTable4Task = this.DbContext.table4.AsNoTracking().Select(target => target.TargetID).Where(targetID => targetID == targetId).CountAsync();

    await Task.WhenAll(isUsedOnTable1Task, isUsedOnTable2Task, isUsedOnTable3Task, isUsedOnTable4Task)
    
    isUsedOnTable1 = isUsedOnTable1Task.Result > 0;
    isUsedOnTable2 = isUsedOnTable2Task.Result > 0;
    isUsedOnTable3 = isUsedOnTable3Task.Result > 0;
    isUsedOnTable4 = isUsedOnTable4Task.Result > 0;

    return (isUsedOnTable1 || isUsedOnTable2 || isUsedOnTable3 || isUsedOnTable4);
}

Remember that EntityFramework is not Thread safe

Upvotes: 0

sep7696
sep7696

Reputation: 575

i think u should use where first to improve performance

this.DbContext.table1.Where(TargetID => TargetID == targetId).Select(target => target.TargetID).Count()

Because you fetch the data first, then you process it

otherway

you generat a Generic class that get Object query then get commands out of these and one huge batch hope this helps you

GoodLuck

Upvotes: 1

Related Questions