Get subset of rows from table and filter after the query is executed-CodePudding

I am using entity framework core with models for all the tables in the SQL database.

I have a linq query that pulls rows from a table - let's call it facilities. Then, I iterate the results of the query using fornext() (don't ask) :) Within the loop we pull data from various other tables that are related to facilities. Obviously this is a huge performance issue since there can be up to 100 rows in facilities which means the DB gets queried every time the loop iterates for every additional table we are pulling from. Note that some of the tables are from another database and you cannot join between contexts. Already tried that.

So, I thought to myself, let's pull all the rows from the related tables before we process the loop. That way, we only make those db calls one time for each associated table.

    var pracloc = _ODSContext.AllPractitionerLocations.Where(l => l.AllPractitionerLocationID != 0);

And, that works just fine.

Next step, let's simplify the code and pull some of those db calls out into private methods within the class.

For example:

Here's where I call the method (this replaces the line above).

var pracloc = GetAllPractitionerLocationsDTO();

Here's the method.

    private AllPractitionerLocationsDTO GetAllPractitionerLocationsDTO()
    {
        AllPractitionerLocationsDTO dto = new();
        dto.MyList = new List<AllPractitionerLocationDTO>();
        var myo = _ODSContext.AllPractitionerLocations.Where(s => s.AllPractitionerLocationID != 0).Select(g => new AllPractitionerLocationDTO()
        {
            AllPractitionerLocationID = g.AllPractitionerLocationID
        });
        dto.MyList = myo.ToList();
        return dto;
    }

Here's the subsequent filter (which is unchanged between the two data queries above):

var PracLocation = pracloc.Where(a => a.LocationID = provider.LocationID).FirstOrDefault();

And, this works fine as long as I pull the data by querying the DB directly as in the first line above.

When I try to pull the data in the method, the line above throws:

'AllPractitionerLocationsDTO' does not contain a definition for 'Where' and no accessible extension method 'Where' accepting a first argument of type 'AllPractitionerLocationsDTO' could be found (are you missing a using directive or an assembly reference?)

AllPractitionerLocationsDTO is a model class with a subset of the rows in the "real" model:

public class AllPractitionerLocationDTO
{
    public int SRCAllPractitionerLocationID { get; set; }
    public int AllPractitionerLocationID { get; set; }
}
public class AllPractitionerLocationsDTO
{
    public List<AllPractitionerLocationDTO> MyList;
}

Since that is identical in structure to the actual DB table, why won't the where clause work? Or, how can I implement my own where within the model class?

I even tried adding the dbset<> to the context. Still didn't work

    public virtual DbSet<AllPractitionerLocationDTO> AllPractitionerLocationDTOs { get; set; }

Help me please.

CodePudding user response：

You have to return IQueryable from your method. Only in this case you can reuse it later and filter effectively:

private IQueryable<AllPractitionerLocationDTO> GetAllPractitionerLocationsDTO()
{
    var query = _ODSContext.AllPractitionerLocations
        .Where(s => s.AllPractitionerLocationID != 0)
        .Select(g => new AllPractitionerLocationDTO
        {
            AllPractitionerLocationID = g.AllPractitionerLocationID
        });

    return query;
}