LINQ连接Invoice与UserDetail表时FirstName等关联字段返回NULL求助

核心问题原因 #

导致FirstName、SurName字段全为NULL的原因如下：

• 关联字段格式不匹配：CreateByUser是Guid类型，默认ToString()输出为带横杠的字符串，若UserId存储的是无横杠、大小写不一致的字符串，会完全匹配失败。
• 错误的客户端评估逻辑：提前调用ToList()将两个表全量数据拉到内存再做关联，不仅性能极差，也放大了格式转换带来的匹配误差。
• 字段引用错误：原查询中Vehicle = invoice.engine不存在，Invoice类对应字段为Vehicle；同时原查询中给Id赋值，但InvoiceViewModel中对应主键属性为InvoiceId，类型为int而非字符串。

修复方案 #

最优方案：使用EF服务端查询 #

不要提前拉取全量数据，直接在IQueryable层面执行关联，由数据库处理匹配逻辑，规避格式转换问题：

var result = from invoice in _context.Invoices
             join usrDtl in _userManager.Users
             on invoice.CreateByUser equals Guid.Parse(usrDtl.Id) into temp
             from dx in temp.DefaultIfEmpty()
             select new InvoiceViewModel
             {
                 InvoiceId = invoice.InvoiceId,
                 CreateByUser = invoice.CreateByUser,
                 Date = invoice.Date,
                 TotalInvoice = invoice.TotalInvoice,
                 Vehicle = invoice.Vehicle,
                 FirstName = dx != null ? dx.FirstName : "NULL",
                 SurName = dx != null ? dx.SurName : "NULL"
             };
// 仅在需要使用结果时执行查询
var finalData = result.ToList();

临时兼容方案：内存关联统一格式 #

如果必须保留内存关联逻辑，统一两侧关联字段的格式后再匹配：

var invoices = _context.Invoices.ToList();
var users = _userManager.Users.Select(x => new ApplicationUser 
{ 
    Id = x.Id.Trim().ToLower(),
    FirstName = x.FirstName, 
    SurName = x.SurName 
}).ToList();

var result = from invoice in invoices
             join usr in users
             on invoice.CreateByUser.ToString("N").ToLower() equals usr.Id into temp
             from dx in temp.DefaultIfEmpty()
             select new InvoiceViewModel
             {
                 InvoiceId = invoice.InvoiceId,
                 CreateByUser = invoice.CreateByUser,
                 Date = invoice.Date,
                 TotalInvoice = invoice.TotalInvoice,
                 Vehicle = invoice.Vehicle,
                 FirstName = dx != null ? dx.FirstName : "NULL",
                 SurName = dx != null ? dx.SurName : "NULL"
             };

若调整后仍有匹配失败的情况，可分别打印输出两侧关联字段的值，确认格式是否完全一致。

内容的提问来源于stack exchange，提问作者User456