I am building a new application using EF Core 6. I have a table, tblAttendance

public partial class tblAttendance
{
    public long id { get; set; }
    public string? UserName { get; set; }
    public DateTime LoginDate { get; set; }
    public TimeSpan LoginTime { get; set; }
    public DateTime LogoutDate { get; set; }
    public TimeSpan LogoutTime { get; set; }
    public string? ComputerName { get; set; }
    public string? LogonServer { get; set; }
}

When I make Select All queryies

var attendanceData = dbContext.tblAttendances
                .Select(a => new AttendanceData
                {
                    UserName = a.UserName ?? "",
                    LoginDate = a.LoginDate,
                    LoginTime = a.LoginTime,
                    LogoutDate = a.LogoutDate,
                    LogoutTime = a.LogoutTime,
                    ComputerName = a.ComputerName ?? "",
                    LogonServer = a.LogonServer ?? ""
                });

the query returns correct results.

But when I try to filter the data I receive a "Data is Null" error.

I have modified my code to use a simple FromSqlRaw statement

string query = @$"Select * from tblAttendance ";

            if (!string.IsNullOrEmpty(userName))
            {
                if (query.IndexOf("where") < 0) query += "where ";
                query +=$"UserName = '{userName}' ";
            }

            if (!string.IsNullOrEmpty(computerName))
            {
                if (query.IndexOf("where") < 0) query += "where "; else query += "AND ";
                query += $"ComputerName = '{computerName}' ";
            }
            if (loginDate.Ticks>0)
            {
                var year=loginDate.Year; var month=loginDate.Month;
                if (query.IndexOf("where") < 0) query += "where "; else query += "AND ";
                query += $" DATEPART(year, loginDate) = {year} and DATEPART(month, loginDate) = {month}";
            }
            var results=dbContext.tblAttendances.FromSqlRaw(query).ToList();

which also gives the Data is Null error, although when run through server explorer it returns correct results.

My data context builder for this table is

modelBuilder.Entity<tblAttendance>(entity =>
        {
            entity.HasKey(e => e.id).HasName("PK_tblAttendanceScript");

            entity.ToTable("tblAttendance");

            entity.HasIndex(e => new { e.UserName, e.LoginDate }, "UserNames_tblAttendanceScript");

            entity.Property(e => e.ComputerName).HasMaxLength(50);
            entity.Property(e => e.LoginDate).HasColumnType("date");
            entity.Property(e => e.LogonServer).HasMaxLength(50);
            entity.Property(e => e.LogoutDate).HasColumnType("date");
            entity.Property(e => e.UserName).HasMaxLength(50);
        });

Being new to EF (and Core) I am uncertain now where to even look. A general (admittedly brief) search online has revealed nothing that I can apply.

******************** Edit adding sample data *************************** Here is a sample of the results I receive from the database running the query directly:

749405  aanderso    2023-06-29  08:58:48.0000000    2023-06-29  16:05:17.0000000    MCA-TS24VM  192.168.100.90
749423  aanderso    2023-06-29  15:00:20.0000000    2023-06-29  16:05:17.0000000    MCA-TS25VM  192.168.100.97
762067  aanderso    2023-06-29  08:58:48.0000000    2023-06-29  16:05:17.0000000    MCA-TS24VM  192.168.100.90
724075  abala   2023-06-01  07:34:42.0000000    2023-06-01  NULL    MCA-TS22VM  192.168.100.79
734462  abala   2023-06-01  07:34:42.0000000    2023-06-01  18:23:26.0000000    MCA-TS22VM  192.168.100.79
747249  abala   2023-06-01  07:34:42.0000000    2023-06-01  18:23:26.0000000    MCA-TS22VM  192.168.100.79
759911  abala   2023-06-01  07:34:42.0000000    2023-06-01  18:23:26.0000000    MCA-TS22VM  192.168.100.79

The query being used is Select * from tblAttendance where DATEPART(year, loginDate) = 2023 and DATEPART(month, loginDate) = 6