How to use linq join between unrelated models? (one of them is IdentityUser)

Question

I use asp.net core EF.
I have two models.
[1] the first one is employee which inherits IdentityUser and extending it.
[2] the second one is DocumentIn which has employee's key

note: they are not related

the problem:
I need to view all DocumentIn but with employee's names, not keys

next are my two models respectively (with comments included to demonstrate Primary and Foreign keys)

Employee inheriting from IdentityUser

public class Employee: IdentityUser
{
    // this primary key (Id) is identified as a foreign key twice in the next model (Document In) 
    // but with different names 
    // the names are [EmpFrom] and [EmpTo]
    [Key] 
    public override string Id { get => base.Id; set => base.Id = value; }
    //-------------------------------------------------------------------------------------------

    [EmailAddress]
    public override string Email { get => base.Email; set => base.Email = value; }

    public string HomePhone { get; set; }

    public string EmpName { get; set; }

    public int? DivID { get; set; }
    [ForeignKey("DivID")]
    public Division division { get; set; }

    public string Password { get; set; }

    [NotMapped]
    public string ConfirmPassword { get; set; }

}

DocumentIn

public class DocumentIn
{
    [Key]
    public int DocInID { get; set; }

    public string DocName { get; set; } 
    public string Notes { get; set; }
    public string BarCode { get; set; }

    public DateTime ActionDate { get; set; } 
    public DateTime DueDate { get; set; } 

    // here I use these names to contains employees Id which is GUID of IdentityUser
    public string EmpFrom { get; set; } 
    public string EmpTo { get; set; } 

}

Important:

I use EF concept, DbContext to get DocumentIn
however, I use UserManager on the other hand to get employees

like the following

gitting DocumentIn using _context

// _TableView is a ViewModel which has this attripute 
// public List<DocumentIn> DocumentsInList { get; set; }

_TableView.DocumentsInList = await _context.DocumentIn.Where(bla bla bla).ToListAsync();

gitting employees using UserManager

 List<Employee> Emps = await userManager.Users.ToListAsync();

Now, I need to include Emp with DocumentsInList's query to read all documents with the names of employees, not with their Id.
In other words, I need to view EmpName from Employee rather than EmpFrom and EmpTo in the above LINQ query.

I already made the next LINQ query but I don't know how to replace EmpFrom and EmpTo from DocumentIn with EmpName from Employee

  // -------- all users 
  List<Employee> Emps = await userManager.Users.ToListAsync();

  _TableView.DocumentsInList = await _context.DocumentIn.Include(e => Emps).ToListAsync();

That's exactly why you shouldn't extend the ASP.Net Identity model. Leave it alone. Its only task is authentication/authorization, it shouldn't know anything of the rest of your business. `Employee` should be part of your business model (and `DbContext`) so you can use it in navigation properties. In the database it *may* have an FK to the identity user, showing as a plain property in the C# class. — Gert Arnold, Aug 06 '20 at 12:19

score 0 · Accepted Answer · answered Aug 06 '20 at 13:29

I already made the next LINQ query but I don't know how to replace EmpFrom and EmpTo from DocumentIn with EmpName from Employee

According to your Model code, since the DecumentIn model doesn't contain the reference navigation property for the Employee, if you want to display the Employee when query the DecumentIn table, you have to use the Join clause.

And, you could create a view Model (such as: DocumentInViewModel) which contains a EmpName property, then in the LINQ select clause, you could create the DocumentInViewModel instance and display the Employee Name.

Code as below (Suppose the DocumentIn's EmpFrom value equals the Emplyee's ID value):

public class DocumentInViewModel
{ 
    public int DocInID { get; set; }

    public string DocName { get; set; } 
    public string Notes { get; set; }
    public string BarCode { get; set; }

    public DateTime ActionDate { get; set; } 
    public DateTime DueDate { get; set; } 

    // used to display the Employee Name.
    public string EmpName { get; set; }  

}

Then, you could try to use the following code to query related data.

        List<Employee> Emps = await userManager.Users.ToListAsync();
        var resultee = _context.DocumentIn.ToList()
            .Join(Emps, d => d.EmpFrom, e => ((Employee)e).Id, (d, e)=> new { DocumentIn= d, Employee = e })
            .Select(c=> new DocumentInViewModel { DocName = c.DocumentIn.DocName, EmpName = c.Employee.EmpName })
            .ToList();

More detail information about the Linq Join statement, you could check this thread.

score -1 · Answer 2 · answered Aug 06 '20 at 12:24

-1

Why dont you create a view in the db and use that to show both models as per your requirement

answered Aug 06 '20 at 12:24

Umair Zafar

80
8

How to use linq join between unrelated models? (one of them is IdentityUser)

2 Answers2