2023年7月13日 09:37:28go评论99阅读模式

英文:

Why am I seeing "Data is Null" on query in EF Core 6 on a new build?

问题

我正在使用EF Core 6构建一个新的应用程序。我有一个表格，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; }
}

当我执行Select All查询时：

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 ?? ""
    });

查询返回正确的结果。

但是当我尝试过滤数据时，我收到一个"Data is Null"错误。

我已经修改了我的代码，使用了一个简单的FromSqlRaw语句：

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();

但是这也会导致"Data is Null"错误，尽管在服务器资源管理器中运行时它返回正确的结果。

我的数据上下文生成器（data context builder）如下：

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);
});

作为EF（和Core）的新手，我现在不确定该从哪里开始查找问题。简短的在线搜索没有找到我可以应用的解决方法。

******************** 编辑添加示例数据 ***************************

这是我直接从数据库中获取的查询结果示例：

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

所使用的查询是：

Select * from tblAttendance where DATEPART(year, loginDate) = 2023 and DATEPART(month, loginDate) = 6

英文:

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 =&gt; new AttendanceData
                {
                    UserName = a.UserName ?? &quot;&quot;,
                    LoginDate = a.LoginDate,
                    LoginTime = a.LoginTime,
                    LogoutDate = a.LogoutDate,
                    LogoutTime = a.LogoutTime,
                    ComputerName = a.ComputerName ?? &quot;&quot;,
                    LogonServer = a.LogonServer ?? &quot;&quot;
                });

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 = @$&quot;Select * from tblAttendance &quot;;
            if (!string.IsNullOrEmpty(userName))
            {
                if (query.IndexOf(&quot;where&quot;) &lt; 0) query += &quot;where &quot;;
                query +=$&quot;UserName = &#39;{userName}&#39; &quot;;
            }
            if (!string.IsNullOrEmpty(computerName))
            {
                if (query.IndexOf(&quot;where&quot;) &lt; 0) query += &quot;where &quot;; else query += &quot;AND &quot;;
                query += $&quot;ComputerName = &#39;{computerName}&#39; &quot;;
            }
            if (loginDate.Ticks&gt;0)
            {
                var year=loginDate.Year; var month=loginDate.Month;
                if (query.IndexOf(&quot;where&quot;) &lt; 0) query += &quot;where &quot;; else query += &quot;AND &quot;;
                query += $&quot; DATEPART(year, loginDate) = {year} and DATEPART(month, loginDate) = {month}&quot;;
            }
            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&lt;tblAttendance&gt;(entity =&gt;
        {
            entity.HasKey(e =&gt; e.id).HasName(&quot;PK_tblAttendanceScript&quot;);
            entity.ToTable(&quot;tblAttendance&quot;);
            entity.HasIndex(e =&gt; new { e.UserName, e.LoginDate }, &quot;UserNames_tblAttendanceScript&quot;);
            entity.Property(e =&gt; e.ComputerName).HasMaxLength(50);
            entity.Property(e =&gt; e.LoginDate).HasColumnType(&quot;date&quot;);
            entity.Property(e =&gt; e.LogonServer).HasMaxLength(50);
            entity.Property(e =&gt; e.LogoutDate).HasColumnType(&quot;date&quot;);
            entity.Property(e =&gt; 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

答案1

得分: 1

在EF表模型定义中：

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; }
}

LogoutTime 的定义为必需（没有 '?')。这告诉 EF 不能有空值。然而，在数据库中，允许有空的注销时间。这是导致查询出现问题的原因。修改为 public TimeSpan? LogoutTime { get; set; } 后，一切都按设计工作了。

英文:

In the EF table model definition

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; }
}

LogoutTime public TimeSpan LogoutTime { get; set; } is defined as required (no '?'). This is telling EF that there cannot be a Null value. In the database, however, it is possible to have a Null logout time. This is what was breaking the query. Modified this to public TimeSpan? LogoutTime { get; set; } and everything worked as it was designed to.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

为什么我在EF Core 6的新构建中看到“数据为空”的查询？

问题

答案1

Interlocked.CompareExchange不提供正确的内存排序。

ASP.NET文本框的字符计数与服务器端文本框的字符计数不同。

ASP.NET Core – 使用自动映射和Dapper实现的DTO未按预期工作

如何正确地对查询进行参数化

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。