SQL lambda output not correct using nested select

Question

I have 2 tables, Terms and Site_Program_Term:

Terms columns: Id, SiteId, Name, IsHoliday, DateStart, DateEnd, Year, IsActive
Site_Program_Term columns: Id, ProgName, SiteId, TermId, Year

Using the above 2 tables, I need to display output with primarily below columns

Name, StartDate, EndDate, NumberOfPrograms

I am getting the desired output but the 'NumberOfPrograms' is not showing the correct values. It should give me a count of all the Programs for a set of particular Term, Year and Site.

Thanks in advance.

Below is the base code.

var terms = db.Terms.Where(t => t.SiteId == id && t.IsActive).ToList().Select(t => new
            {
                Id = t.Id,
                SiteId = t.SiteId,
                Name = t.Name,
                IsHoliday = t.IsHoliday,
                DateStart = t.DateStart,
                DateEnd = t.DateEnd,
                Year = t.Year,

                PubProgram = ( 
                    db.Site_Program_Term
                    .Where (spt => spt.SiteId == id && spt.Year == t.Year)
                    .Select(s => s).Count()
                ),
            }).OrderBy(n => n.DateStart).ToList();

What language and/or framework is this in? It certainly isn't plain SQL... — sticky bit, Sep 15 '18 at 00:22

Ňuf · Answer 1 · 2018-09-15T08:15:16.613

It should give me a count of all the Programs for a set of particular Term, Year and Site.

But your Where condition only includes Year and Site, but not the Term. So

.Where(spt => spt.SiteId == id && spt.Year == t.Year)

should actually be:

.Where(spt => spt.SiteId == id && spt.Year == t.Year && spt.TermId == t.Id)

Note that if you are by any chance using Entity Framework (Core) and you have properly configured navigation property on the Term class such as

public class Term
{
    // ...
    public List<Site_Program_Term> SiteProgramTerms { get; set; }
}

then you can rewrite your query as

var terms = db.Terms
    .Where(t => t.SiteId == id && t.IsActive)
    .Select(t => new
    {
        Id = t.Id,
        SiteId = t.SiteId,
        Name = t.Name,
        IsHoliday = t.IsHoliday,
        DateStart = t.DateStart,
        DateEnd = t.DateEnd,
        Year = t.Year,

        PubProgram = t.SiteProgramTerms.Where(spt => spt.SiteId == id && spt.Year == t.Year).Count()
    })
    .OrderBy(n => n.DateStart)
    .ToList();

Note that this time you don't need to include spt.TermId == t.Id condition, because SiteProgramTerms property contains only site program terms with TermId that matches the Id of the Term. Also note that the first .ToList() should be omitted, because it causes query to be sent to the SQL server as multiple SELECT queries (as opposed to just one SELECT when .ToList() is not present).

SQL lambda output not correct using nested select

1 Answers1