title: EF Core 3. Defining a many-to-many relation with existing DB
body: <p>I have an existing SQL DB that is very old and the schema is not great, but I can't change it, I have to live with it.</p>
  
  <p>I'm new to Entity Framework and am using <strong>EF Core 3</strong> to try and build a basic CRUD application.  I'm having difficulty with the "many to many" relationships.</p>
  
  <p>In the example below, I'll provide a cut-down/fictitious repesentation of the scenario.</p>
  
  <p>So, in the DB I have two tables [People] and [The_Cars], and there's a 'linking' table that is [People__The_Cars].  Yes, horrible table names....</p>
  
  <p>The definition of each table is as follows:</p>
  
  <pre><code>CREATE TABLE [dbo].[People](
      [PeopleID] [varchar](10) NOT NULL),
      [Name] [varchar](255) NOT NULL)
  </code></pre>
  
  <p>This has a primary key of PeopleID</p>
  
  <pre><code>CREATE TABLE [dbo].[The_Cars](
      [car_id] [int] NOT NULL,
      [car_name] [varchar](255) NOT NULL)
  </code></pre>
  
  <p>This does NOT have a primary key....</p>
  
  <pre><code>CREATE TABLE [dbo].[People__The_Cars](
      [People__The_Cars_ID] [int] NOT NULL,
      [car_name] [nvarchar](255) NOT NULL,
      [PeopleID] [nvarchar](255) NOT NULL,
  </code></pre>
  
  <p>This does have a primay key, but no foreign keys.  The Car "FK" is to the name, not the ID.  And the data types don't match [PeopleID is varchar(10) in the [People] table, but nvarchar(255), in this linking table].  Also use of varchar vs nvarchar.</p>
  
  <p>In C#, I've created some classes to represent these with tidied up names:</p>
  
  <p>For [People]</p>
  
  <pre><code>public class People
      {
          [StringLength(10)]
          [Required]
          public string ID{ get; set; }
  
          [StringLength(255)]
          [Required]
          public string Name { get; set; }
  
      public ICollection&lt;People__The_Cars&gt; PeopleCars{ get; set; }
  }
  </code></pre>
  
  <p>for [The_Cars]</p>
  
  <pre><code>public class The_Cars
      {
          [Required]
          public int Id{ get; set; }
  
          [StringLength(255)]
          [Required]
          public string Name { get; set; }
  
      public ICollection&lt;People__The_Cars&gt; PeopleCars{ get; set; }
  }
  </code></pre>
  
  <p>for the linking table </p>
  
  <pre><code>public class People__The_Cars
      {
          [Required]
          public int Id{ get; set; }
  
          [StringLength(10)]
          [Required]
          public string PeopleId { get; set; }
  
          [StringLength(255)]
          [Required]
          public string CarName { get; set; }
  
  public People Person{ get; set; }
  public The_Cars Car{ get; set; }
  }
  </code></pre>
  
  <p>(The data annotations reflect the primary table's data types)</p>
  
  <p>Now onto the Data Context class</p>
  
  <pre><code>public class MyContext : DbContext
  {
      public OpdxDestinationsContext(DbContextOptions&lt;MyContext&gt; options)
          : base(options)
      {
      }
  
      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          modelBuilder
              .Entity&lt;People&gt;(e =&gt;
              {
                  e.HasKey(i =&gt; i.Id);
                  e.Property(i =&gt; i.Id).HasColumnName("PeopleID");
              });
  
          modelBuilder
              .Entity&lt;People__The_Cars&gt;(e =&gt;
              {
                  e.HasKey(i =&gt; i.Name);
                  e.Property(i =&gt; i.Id).HasColumnName("People__The_Cars_ID");
                  e.Property(i =&gt; i.CarName).HasColumnName("car_name");
              });
  
          modelBuilder
              .Entity&lt;Software_Vendor_Destinations&gt;(e =&gt;
              {
                  e.HasKey(i =&gt; new {i.PeopleId, i.CarName });
                  e.Property(i =&gt; i.Id).HasColumnName("Software_Vendor_Destinations_Id");
                  e.Property(i =&gt; i.CarName).HasColumnName("car_name");
                  e.Property(i =&gt; i.DestinationId).HasColumnName("PeopleID");
              });
  
          modelBuilder.Entity&lt;People__The_Cars&gt;()
              .HasOne(x =&gt; x.People)
              .WithMany(m =&gt; m.PeopleCars)
              .HasForeignKey(x =&gt; x.CarName);
  
          modelBuilder.Entity&lt;People__The_Cars&gt;()
              .HasOne(x =&gt; x.Car)
              .WithMany(m =&gt; m.PeopleCars)
              .HasForeignKey(x =&gt; x.PeopleId);
  
          base.OnModelCreating(modelBuilder);
      }
  
      public DbSet&lt;People&gt; People{ get; set; }
  
      public DbSet&lt;Cars&gt; Cars{ get; set; }
  }
  </code></pre>
  
  <p>So, few questions.</p>
  
  <ul>
  <li>I have used "HasKey" for tables that don't have keys.  They should do.  Is this bad practice, or should I be truthful and use the HasNoKey when one is missing.</li>
  <li>How do I then set up the "many to many" relationship with these entities?  The "linking table" is really to define the "many to one to many" relationship in the DB and doesn't seem to me something that I want exposed in my entities.</li>
  </ul>
  
  <p>So much to learn...</p>