title: Understanding the Entity Framework when updating record from specific select query
body: <p>Hope some one can help me understand more on how the .net core entity framework works in the scenario below.  </p>
  
  <p>I have an update function and it works perfectly fine.<br>
  But when I tried optimizing my select query, that is when I'm hitting an error.
  To give a better understanding, here are 3 classes and its relationship that is involved:</p>
  
  <pre><code>public class Dealer
  {
      public int Id { get; set; }
      public string Name { get; set; }
      ...
      public virtual ICollection&lt;Vehicle&gt; Vehicles { get; set; }
  }
  
  public class Vehicle
  {
      public string Id { get; set; }
      public string Brand { get; set; }
      public string Model { get; set; }
      ...
      public int DealerId { get; set; }
      public Dealer Dealer { get; set; }
      public virtual ICollection&lt;Accessory&gt; Accessories { get; set; }
  }
  
  public class Accessory
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public int Quantity { get; set; }
      public string VehicleId { get; set; }
      public Vehicle Vehicle { get; set; }
  }
  </code></pre>
  
  <p>And this is the function where I update the data.  </p>
  
  <pre><code>public void DoSomething()
  {
      var dealer = _dealerService.Get(123);
      ... // some dealer field value changes
  
      var vehicle = new Vehicle { ... };
      vehicle.Id = Guid.NewGuid().ToString();
      vehicle.Accessories.Add(new Accessory { ... });
      dealer.Vehicles.Add(new List&lt;Vehicle&gt; { vehicle });
  
      _dealerService.Update(dealer);
  }
  </code></pre>
  
  <p>The working version is just a simple select method that takes every field for the Dealer by default:</p>
  
  <pre><code>public Dealer Get(int id)
  {
      return _db.Where(x =&gt; x.Id == id).FirstOrDefault();
  }
  </code></pre>
  
  <p>Based on the logs it can be seen that the magic happens in the following sequence:</p>
  
  <ol>
  <li>INSERT INTO [Vehicles]</li>
  <li>UPDATE [Dealers]</li>
  <li>INSERT INTO [Accessories]</li>
  </ol>
  
  <p>Now I'm trying to optimize the speed and because Dealer has plenty of fields, I decided to select only fields that are needed for the Dealer so that the query takes a shorter time. Hence, I updated the method like so</p>
  
  <pre><code>public Dealer Get(int id)
  {
      return _db.Where(x =&gt; x.Id == id)
          .Select(x =&gt; new Dealer 
          {
              Id = x.Id,
              Name = x.Name,
              ...
          })
          .FirstOrDefault();
  }
  </code></pre>
  
  <p>That's when I'm hit with this error 
  SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Accessories_Vehicles_Id". The conflict occurred in database, table "dbo.Vehicles", column 'Id'.   </p>
  
  <pre><code>INSERT INTO [Accessories] ([Name], [Quantity], [VehicleId])  
  VALUES (@p0, @p1, @p2);  
  SELECT [Id] FROM [Accessories] WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); 
  </code></pre>
  
  <p>And it is obvious why from the logs as it tries to INSERT INTO [Accessories] first instead of the working version that is the third step.</p>
  
  <p>So here I am hoping someone can explain why by specifying specific fields for my select query can result in such a behavior where .net inserts Accessories first instead of Vehicles first when there is a FK involved.  </p>
  
  <p>Thanks in advance.</p>