C# Pulling the Count of tables Associated with ID

Question

Hello I have two tables that look like the following:

PeriodValue

• Id (PK)
• Name
• Description
• StartDate
• EndDate
• ActiveFlg

AcademicTerm

• Id (PK)
• Year Id (FK)
• Name
• Start Date
• End Date

The objective is to pull the count of terms associated with every period value. This is my code to do so.

public async Task<PeriodValueDTO> GetSchoolYearPeriodValueDTOById (int periodValueId)
{
    var value = await Db.PeriodValues.FindAsync(periodValueId);

    return new PeriodValueDTO()
    {
        id = periodValueId,
        Name = value.Name,
        StartDate = value.Date.ToShortDateString(),
        EndDate = value.EndDate.ToShortDateString(),
        Description = value.Description
    };
}

This method calls the one above

public async Task<List<PeriodValueDTO>> GettAllPeriodValueDTOsByType(int periodTypeId)
{
    var toReturn = new List<PeriodValueDTO>();
    var pvs = await Db.PeriodValues.Where(x => x.PeriodTypeId == periodTypeId).ToListAsync();
    var pvIds = pvs.Select(x => x.Id).ToList();
    var periodPeriodVal = await Db.Period_PeriodValue.Where(x => pvIds.Contains(x.PeriodValueId)).ToListAsync();

    foreach (var ppv in periodPeriodVal)
    {
        var periodValue = pvs.FirstOrDefault(x => x.Id == ppv.PeriodValueId);
        var value = await GetSchoolYearPeriodValueDTOById(periodTypeId);
        var rightId = value.id; //Added this
        var terms = Db.AcademicTerms.Where(x => x.YearId == rightId).ToArray();  //Changed This
        var dto = new PeriodValueDTO()
        {
            id = periodValue.Id,
            Name = periodValue.Name,
            StartDate = periodValue.Date.ToShortDateString(),
            EndDate = periodValue.EndDate.ToShortDateString(),
            Description = periodValue.Description,
            Count = terms.Length //Changed this
        };
        toReturn.Add(dto);
    };
    return toReturn;
}

However I am getting this error:

• Failed to load resource: the server responded with a status of 500 (Internal Server Error)
• Error: Resolving failed with a reason [object Object], but no resolveFailed provided for segment SchoolYear

If I comment out the lines that include var terms, var value, and Count it runs. When they are included I get the error above.

Still a novice to this. Any help would be great.

Answer 1

var value = await GetSchoolYearPeriodValueDTOById(periodTypeId);

should be :

var value = await GetSchoolYearPeriodValueDTOById(periodValue.Id);

no ?

Answer 2

It's likely because of this line:

var terms = Db.AcademicTerms.Where(x => x.YearId == value.id);

Here, you are trying to generate an expression to be translated into SQL and executed on the database (because Db.AcademicTerms is IQueryable). You are not executing this query in memory. The query parser tries to convert value to a SQL parameter and fails because it is not a primitive object. So you have two choices:

1. Save value.id into a separate variable and then use that in your query, or better:
2. Save all AcademicTerms into memory before your loop (I assume there are not hundreds of them) by calling .ToArray() and then query against this. This also resolves the additional N+1 Selects antipattern you have, while allowing greater flexibility.