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

Question

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.

score 1 · Answer 1 · answered Aug 06 '20 at 19:17

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

MesutAtasoy · Accepted Answer · 2020-08-06T20:32:48.943

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

Farhad Zamani · Answer 3 · 2020-08-06T20:03:47.383

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

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

3 Answers3