I'm trying to convert this into an async method, but I don't think I'm querying the data properly to do it. What am I missing to:

1. make this cleaner
2. make it async

All the InventorySelection is, is a class with the various filter items.

private IEnumerable<T_IFS_Inventory> Getquery(T_IFS_InventorySelectDTO InventorySelection)
{
    IEnumerable<T_IFS_Inventory> query = _db.T_IFS_Inventory.AsQueryable();

    if (InventorySelection.SerialNumber != "" && InventorySelection.SerialNumber != null)
    {
        query = query.Where(q => q.SerialNumber == InventorySelection.SerialNumber);
    }
    else
    {
        if (InventorySelection.ScanDateStart != null)
        {
            query = query.Where(q => q.ScanDate >= InventorySelection.ScanDateStart);

            if (InventorySelection.ScanDateEnd != null)
                query = query.Where(q => q.ScanDate <= InventorySelection.ScanDateEnd);
        }

        if (InventorySelection.StatusDateStart != null)
        {
            query = query.Where(q => q.DateStatus >= InventorySelection.StatusDateStart);

            if (InventorySelection.StatusDateEnd != null)
                query = query.Where(q => q.DateStatus <= InventorySelection.StatusDateEnd);
        }

        if (InventorySelection.CategoryID != 0)
            query = query.Where(q => q.CategoryID == InventorySelection.CategoryID);

        if (InventorySelection.BrandID != 0)
            query = query.Where(q => q.BrandID == InventorySelection.BrandID);

        if (InventorySelection.ModelID != 0)
            query = query.Where(q => q.ModelID == InventorySelection.ModelID);

        if (InventorySelection.EmployeeID != 0)
            query = query.Where(q => q.EmployeeID == InventorySelection.EmployeeID);

        if (InventorySelection.StatusID != 0)
            query = query.Where(q => q.StatusID == InventorySelection.StatusID);

        if (InventorySelection.EmployeeStatusID != 0)
            query = query.Where(q => q.EmployeeStatusID == InventorySelection.EmployeeStatusID);

        if (InventorySelection.CurrentLocationID != 0)
            query = query.Where(q => q.CurrentLocationID == InventorySelection.CurrentLocationID);
    }

    return query;
}

CodePudding user response：

Your query is being executed at the first line, I hope this is a typo, but IEnumerable is wrong as the variable type. It explicitly forces ALL of the records to be loaded into memory before any of your filters are applied. What you want is to defer the execution until all of the criteria have been defined.

    IQueryable<T_IFS_Inventory> query = _db.T_IFS_Inventory.AsQueryable();

With only this change the query will be evaluated and the data will be returned as part of the return process. Because the method return type is IEnumberable the change from IQueryable to IEnumerable would force the evaluation as part of the return process.

To make this Async, deliberately resolve the query:

return query.ToListAsync();

Or you can await it if you need this method's line info in the stack trace:

return await query.ToListAsync();

Both of these changes will require the method prototype to be made Async too:

private async Task<IEnumerable<T_IFS_Inventory>> Getquery(T_IFS_InventorySelectDTO InventorySelection)

However now the method isn't getting a query it is now executing the entire query and bringing all of the data into memory.

A better approach that will allow you to compose on top of this query would be to change the method prototype to return IQueryable

private async IQueryable<T_IFS_Inventory> Getquery(T_IFS_InventorySelectDTO InventorySelection)

Ideally, your method should look like this:

private IQueryable<T_IFS_Inventory> Getquery(T_IFS_InventorySelectDTO InventorySelection)
{
    IQueryable<T_IFS_Inventory> query = _db.T_IFS_Inventory.AsQueryable();

    if (!String.IsNullOrEmpty(InventorySelection.SerialNumber))
    {
        query = query.Where(q => q.SerialNumber == InventorySelection.SerialNumber);
    }
    else
    {
        if (InventorySelection.ScanDateStart != null)
        {
            query = query.Where(q => q.ScanDate >= InventorySelection.ScanDateStart);

            if (InventorySelection.ScanDateEnd != null)
                query = query.Where(q => q.ScanDate <= InventorySelection.ScanDateEnd);
        }

        if (InventorySelection.StatusDateStart != null)
        {
            query = query.Where(q => q.DateStatus >= InventorySelection.StatusDateStart);

            if (InventorySelection.StatusDateEnd != null)
                query = query.Where(q => q.DateStatus <= InventorySelection.StatusDateEnd);
        }

        if (InventorySelection.CategoryID != 0)
            query = query.Where(q => q.CategoryID == InventorySelection.CategoryID);

        if (InventorySelection.BrandID != 0)
            query = query.Where(q => q.BrandID == InventorySelection.BrandID);

        if (InventorySelection.ModelID != 0)
            query = query.Where(q => q.ModelID == InventorySelection.ModelID);

        if (InventorySelection.EmployeeID != 0)
            query = query.Where(q => q.EmployeeID == InventorySelection.EmployeeID);

        if (InventorySelection.StatusID != 0)
            query = query.Where(q => q.StatusID == InventorySelection.StatusID);

        if (InventorySelection.EmployeeStatusID != 0)
            query = query.Where(q => q.EmployeeStatusID == InventorySelection.EmployeeStatusID);

        if (InventorySelection.CurrentLocationID != 0)
            query = query.Where(q => q.CurrentLocationID == InventorySelection.CurrentLocationID);
    }

    return query;
}

It is important when we use this query builder pattern that you keep the query as an IQueryable until all of the criteria have been added.

In terms of code style, I'd call this very clean, anything else is going to affect the readability, right now it's very easy to follow.