How to use Linq for foreign key

Question

I am trying to create a table that shows all users based on the selected program.

Programs:

• Id (key),
• program (string)

ProgramUsers:

• Id (key),
• username,
• program (foreign key),
• programaccess

Client input: program (string)

var programUsers = context.ProgramUsers
                     .Include("Program")
                     .Where(b => b.Program == clientInput.Program)
                     .ToList();

Of course, since b.Program is an int and clientInput.Program is a string, this does not work. Can someone please help me?

Answer 1

1. The ProgramUsers class needs a ProgramId property and a Program reference property.

2. The Program needs a ProgramUsers collection navigation property.

using System.ComponentModel.DataAnnotations.Schema;

public class ProgramUsers
{
    public int Id { get; set; }
    public string Username { get; set; }

    [ForeignKey("Program")]
    public int ProgramId { get; set; }

    public Program Program { get; set; }
}

public class Program
{
    public int Id { get; set; }

    [Column("Program")]
    public string ProgramName { get; set; }

    public ICollection<ProgramUser> ProgramUsers { get; set; }
}

To configure in Fluent API if you are using Entity Framework Core:

modelBuilder.Entity<ProgramUser>()
            .HasRequired<Program>(x => x.Program)
            .WithMany(g => g.ProgramUsers)
            .HasForeignKey<int>(x => x.ProgramId); 

And to get the related entity for ProgramUsers in your query, assume that you are trying to get the program users by program name:

var programUsers = context.ProgramUsers
                     .Include(x => x.Program)
                     .Where(b => b.Program.ProgramName == clientInput.Program)
                     .ToList();

Reference: Data Annotations - ForeignKey Attribute in EF 6 & EF Core