Query: Unable to cast object of type when using Include in projection

[dvdobrovolskiy]

code

_context.Persons.Include(i => i.Level)
.Select(s => new {
PersonId = s.PersonId,
FullName = s.FullName,
DOB = s.DOB,
cnt = _context.CorporatePersons.Include(i => i.Person).Where(a => a.Person.PersonId == s.PersonId).Count(),
});

Generates error

System.InvalidCastException: 'Unable to cast object of type '<_GroupJoin>d__264[CETDB.Data.CorporatePerson,CETDB.Data.Person,System.Nullable1[System.Int32],Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[CETDB.Data.CorporatePerson,System.Collections.Generic.IEnumerable1[CETDB.Data.Person]]]' to type 'System.Int32'.'

also

_context.Persons.Include(i => i.Level)
.Select(s => new {
PersonId = s.PersonId,
FullName = s.FullName,
DOB = s.DOB,
isCorporate = _context.CorporatePersons.Include(i=>i.Person).Any(a=>a.Person.PersonId == s.PersonId),
});

generates
InvalidOperationException: Sequence contains no elements

Models

    public class Person
    {
        [Key]
        [Display(Name = "Id")]
        public int PersonId { get; set; }

        [Required(ErrorMessage = "Specify name")]
        [Display(Name = "First Name")]
        public string FirstName { get; set; }

        [Required(ErrorMessage = "Specify Surname")]
        [Display(Name = "Second Name")]
        public string SecondName { get; set; }

        [Display(Name = "Patronymic")]
        public string Patronymic { get; set; }

        [Display(Name = "Full Name")]
        public string FullName
        {
            get { return SecondName + " " + FirstName + (string.IsNullOrEmpty(Patronymic) ? string.Concat(" " + Patronymic) : ""); }
        }

        [Display(Name = "Full Name")]
        public string FullNameAge
        {
            get { return SecondName + " " + FirstName + (string.IsNullOrEmpty(Patronymic) ? string.Concat(" " + Patronymic) : "") + (DOB.HasValue ? "(" + (DOB > DateTime.Today.AddYears(-(DateTime.Today.Year - ((DateTime)DOB).Year)) ? (DateTime.Today.Year - ((DateTime)DOB).Year) : (DateTime.Today.Year - ((DateTime)DOB).Year) - 1).ToString() + ")" : ""); }
        }

        [Required(ErrorMessage = "Specify date of birth")]
        [DisplayFormat(DataFormatString = "{0:dd.MM.yy}", ApplyFormatInEditMode = true)]
        [Display(Name = "Date of Birth")]
        [DataType(DataType.Date)]
        public DateTime? DOB { get; set; }

        [Display(Name = "Возраст")]
        public int Age        
        {
            get { return (DOB.HasValue ? DOB > DateTime.Today.AddYears(-(DateTime.Today.Year - ((DateTime)DOB).Year)) ? (DateTime.Today.Year - ((DateTime)DOB).Year) : (DateTime.Today.Year - ((DateTime)DOB).Year) - 1 : -1); }
        }

        [Display(Name = "Main Phone")]
        public string MainPhone { get; set; }

        [Display(Name = "Secondary Phone")]
        public string SecondaryPhone { get; set; }

        [Display(Name = "Tertiary Phone")]
        public string TertiaryPhone { get; set; }

        [Display(Name = "Quaternary Phone")]
        public string QuaternaryPhone { get; set; }

        [Display(Name = "Quinary Phone")]
        public string QuinaryPhone { get; set; }

        [DisplayFormat(DataFormatString = "{0:dd.MM.yy}", ApplyFormatInEditMode = true)]
        [Display(Name = "Date tested")]
        [DataType(DataType.Date)]
        public DateTime? DateTested { get; set; }

        [Display(Name = "Level tested temp")]
        public string LevelTestedTemp { get; set; }

        public string LatestNewsTemp { get; set; }

        public string DOBTemp { get; set; }

        public string NumberTemp { get; set; }

        public string DateTestedTemp { get; set; }

        [Display(Name = "Book temp")]
        public string BookTemp { get; set; }

        public string TempId { get; set; }

        public string DayandTimePreferencesTemp { get; set; }

        public string Number { get; internal set; }

        [Display(Name = "Email")]
        [EmailAddress(ErrorMessage = "Invalid Email Address")]
        [DataType(DataType.EmailAddress, ErrorMessage = "Invalid Email Address")]
        public string Email { get; set; }

        [Display(Name = "Level tested")]
        public int? LevelId { get; set; }

        [Display(Name = "Level tested")]
        public Level Level { get; set; }

        [Display(Name = "Type of user")]
        public PersonType PersonType { get; set; }

        public int? ParentId { get; set; }

        [Display(Name = "Parent")]
        public virtual Person Parent { get; set; }

        public virtual ICollection<Person> Children { get; set; }

        public string Avatar { get; set; }

        public int? GroupId { get; set; }

        public Group Group { get; set; }

        public List<RegisterGroup> RegisterGroups { get; set; }

        public virtual Level CurrentLevel { get; }

        [ScaffoldColumn(false)]
        public ApplicationUser CreatedBy { get; set; }

        [ScaffoldColumn(false)]
        public DateTime? CreatedByDateTime { get; set; }

        [ScaffoldColumn(false)]
        public ApplicationUser ModifiedBy { get; set; }

        [ScaffoldColumn(false)]
        public DateTime? ModifiedByDateTime { get; set; }

        [Display(Name = "Passport series")]
        public string PassportSeries { get; set; }

        [Display(Name = "Passport Number")]
        public string PassportNumber { get; set; }

        [Display(Name = "Passport IssuedBy")]
        public string PassportIssuedBy { get; set; }

        [Display(Name = "Passport Registration")]
        public string PassportRegistration { get; set; }

        public int CorporatePersonId { get; set; }

        public CorporatePerson CorporatePerson { get; set; }

        [Display(Name = "Corporate")]
        public virtual bool isCorporate { get; set; }
    }


    public class CorporatePerson
    {
        [Key]
        [Display(Name = "Id")]
        public int CorporatePersonId { get; set; }

        public Corporate Corporate { get; set; }

        public Person Person { get; set; }

        [ScaffoldColumn(false)]
        public ApplicationUser CreatedBy { get; set; }

        [ScaffoldColumn(false)]
        public DateTime? CreatedByDateTime { get; set; }
    }


public class Level
    {
        [Key]
        [ScaffoldColumn(false)]
        public int LevelId { get; set; }

        [Display(Name = "Level")]
        public string Title { get; set; }

        [Display(Name = "Age")]
        public string Age { get; set; }

        [Display(Name = "Age")]
        public LevelAge LevelAge { get; set; }
    }

[ajcvickers]

May already be fixed. @maumar to investigate.

[maumar]

This is indeed fixed in current bits. @dvdobrovolskiy you don't actually need to add Include statements in the queries from the repro. Persons.Include(i => i.Level) won't take effect because Person is not there in the final projection and CorporatePersons.Include(i => i.Person) is not needed (assuming that you added it because of navigation access - EF will automatically add joins where needed to access navigations)

[dvdobrovolskiy]

Sorry @maumar for being dumb here but can you please explain last thig
For ex I have

public class Location
    {
        [Key]
        [ScaffoldColumn(false)]
        public int LocationId { get; set; }

        [Display(Name = "Title")]
        public string Title { get; set; }
    }

and

public class Classroom
    {
        [Key]
        [ScaffoldColumn(false)]
        public int ClassroomId { get; set; }

        [Display(Name = "Title")]
        public string Title { get; set; }

        public int Seats { get; set; }

        public int LocationId { get; set; }

        public Location Location { get; set; }
    }

so
_context.Classrooms will not give me location mean I get in as null

{"Data":[{"ClassroomId":1,"Title":"dsdf","Seats":2,"LocationId":2,"Location":null,"CreatedBy":null,"CreatedByDateTime":null,"ModifiedBy":null,"ModifiedByDateTime":null}],"Total":1,"AggregateResults":null,"Errors":null}

only in I explicitly include them like
_context.Classrooms.Include(i=>i.Location)

[maumar]

@dvdobrovolskiy That's correct - you need to add Include statements if you are projecting entities in the final result. However, in the initial example, the projection was:

new {
PersonId = s.PersonId,
FullName = s.FullName,
DOB = s.DOB,
cnt = _context.CorporatePersons.Include(i => i.Person).Where(a => a.Person.PersonId == s.PersonId).Count(),
})

it doesn't return the entire entity, just individual properties, and hence Include is not needed. Basically, the way we apply include is we look at the final projection and try to find entities that have include statements defined for them. If we don't find those entities in the final projections, Include is ignored completely.

[dvdobrovolskiy]

got it. Much appreciate @maumar