时间戳

首页 > 解决方案 > 可空对象在连接表查询中必须有值

c# - 可空对象在连接表查询中必须有值

问题描述

我有三张桌子TableTeam，TableUser和TableLink。我想加入他们以获取列表。

var teams = _db.TableTeam;
var users = _db.TableUser;
var managerIds = teams.Select(x=>x.ManagerId).ToList();
var list = (from user in _db.TableUser
        where user.EndDate == null && user.ManagerId != null && managerIds.Contains(user.ManagerId.Value)
        let r = _db.TableLink.FirstOrDefault(l => l.ProjectId == projectId && l.UserId  == user.UserId)
        let t = _db.TableTeam.FirstOrDefault(m => m.ManagerId == user.ManagerId)
        select new MyDto
        {
            ProjectId = projectId,
            UserId = user.UserId,
            IsAssigned = r.AssignedId > 0 ?true : false,
            AssignedId = r.AssignedId,
            Percent = r.Percent.HasValue ? (r.Percent.Value).ToString() + "%": ""
        }).GroupBy(d => d.UserId).ToList();

对于表TableLink，实体框架中对应的dataSet类为：

public LinkDto
{
   public int AssignedId {get;set;}
   public short ProjectId {get;set;}
   public short UserId {get;set;}
   public decimal? Percent {get;set;}
   // ...
}

但是我得到了错误：

可空对象必须有一个值

我认为这可能是由decimal? Percent. 但不确定如何解决。我也可以使用 store d 过程，这意味着如果你可以通过 SQL 查询来解决它，那也很好。

标签： c#sql-serverentity-framework-core-2.1

解决方案

事实证明，我必须在子句let之前移动代码，where然后将条件添加到where.

如

var list = (from user in _db.TableUser
    let r = _db.TableLink.FirstOrDefault(l => l.ProjectId == projectId && l.UserId  == user.UserId)
    let t = _db.TableTeam.FirstOrDefault(m => m.ManagerId == user.ManagerId)
    where user.EndDate == null && user.ManagerId != null && managerIds.Contains(user.ManagerId.Value) 
    && r != null && t != null
    select new MyDto
    {
        ProjectId = projectId,
        UserId = user.UserId,
        IsAssigned = r.AssignedId > 0 ?true : false,
        AssignedId = r.AssignedId,
        Percent = r.Percent.HasValue ? (r.Percent.Value).ToString() + "%": ""
    }).GroupBy(d => d.UserId).ToList();

推荐阅读