Joining two tables displays NULL

Question

I want to get all data from Invoice table added with Firstname and Lastname from table usersDetail.

My query is returning data from Invoice table but with NULL value on Firstname and Lastname.

Can you please assist ?

Table Fields

Invoice table = InvoiceId, Date, CreateByUser(Guid), Vehicule

UserDetail table = UserId(string), FirstName, Lastname

CreateByUser = UserId

var invoiceContext = _context.Invoices.ToList();

var usersDetail = _userManager.Users.Select(x => new ApplicationUser { Id = x.Id, FirstName = x.FirstName, SurName = x.SurName }).ToList();



var result = from      invoice in invoiceContext
                       join
                       usrDtl in usersDetail
                       on invoice.CreateByUser.ToString() equals usrDtl.Id into tempstorage
                       from dx in tempstorage.DefaultIfEmpty()
                       select new InvoiceViewModel
                       {
                           Id = invoice.InvoiceId.ToString(),
                           Date = invoice.Date,
                           Vehicle = invoice.engine,
                           
                           FirstName = (dx != null) ? dx.FirstName : "NULL",
                           SurName = (dx != null) ? dx.SurName : "NULL"
                       };

Class Model

public class Invoice
{
    public int InvoiceId { get; set; }

    public DateTime Date { get; set; }

    public string Vehicle { get; set; }

    public Guid CreateByUser { get; set; }

    public int TotalInvoice { get; set; }

    public IList ProductInvoices { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public string FirstName { get; set; }

    public string SurName { get; set; }
}



public class InvoiceViewModel
{
    public int InvoiceId { get; set; }

    public Guid CreateByUser { get; set; }

    public DateTime Date { get; set; }

    public int TotalInvoice { get; set; }

    public string FirstName { get; set; }

    public string SurName { get; set; }
    
    public string Vehicle { get; set; }

}

user_11392290 · Answer

Try this

var result = (from invoice in invoiceContext
            join usrDtl in usersDetail
             on invoice.CreateByUser.ToString() equals usrDtl.Id into usrDtlj 
             from usrDtl in usrDtlj.DefaultIfEmpty()
             select new InvoiceViewModel
              {
              Id = invoice.InvoiceId.ToString(),
              Date = invoice.Date,
              Vehicule = invoice.engine,
              FirstName = usrDtl.FirstName==null? "NULL":usrDtl.FirstName,
              SurName = usrDtl.SurName==null? "NULL":usrDtl.SurName
              }).ToList();

user_14890169 · Answer

var result = (from invoice in invoiceContext
         join usrDtl in usersDetail
         on invoice.CreateByUser.ToString() equals usrDtl.Id into usrDtlj 
         from usrDtl in usrDtlj.DefaultIfEmpty()
         select new InvoiceViewModel
          {
          Id = invoice.InvoiceId.ToString(),
          Date = invoice.Date,
          Vehicule = invoice.engine,
          FirstName = usrDtl==null?"":usrDtl.FirstName??"" ,
          SurName = usrDtl==null?"":usrDtl.SurName??""
          }).ToList();

try this. it will work without error.

Joining two tables displays NULL

2 Answers