title: How to set relations between entities when parsing file?
body: <p>I succesfully parsed main entity (measure) from excel book, but I have no idea, how to set relations between measure and lookups (Cloudiness, WindDirection WeatherConditions). I also need to generate guids for lookup entities. How should I do this?</p>
  <pre><code>using DynamicSun.Common.Extensions;
  using DynamicSun.MoscowWeather.Models;
  using DynamicSun.MoscowWeather.Models.Constants;
  using DynamicSun.MoscowWeather.Models.DTO;
  using NPOI.SS.UserModel;
  
  namespace DynamicSun.MoscowWeather.Infrastructure.Services;
  
  public class WeatherExcelService
  {
      
      /// &lt;summary&gt;
      /// Parse excel document to set of entities.
      /// &lt;/summary&gt;
      public WeatherSheetDto ParseDocument(Stream file)
      {
          //_parsed = new WeatherSheetDto();
          var documentDto = new WeatherSheetDto();
  
          using var reader = new StreamReader(file);
          var workbook = WorkbookFactory.Create(file);
  
          for (int i = 0; i &lt; workbook.NumberOfSheets; i++)
          {
              var sheet = ParseSheet(workbook.GetSheetAt(i));
                  
              documentDto.Measures.UnionWith(sheet.Measures);
              documentDto.Cloudiness.UnionWith(sheet.Cloudiness);
              documentDto.WindDirections.UnionWith(sheet.WindDirections);
              documentDto.WeatherConditions.UnionWith(sheet.WeatherConditions);
          }
  
          return documentDto;
      }
  
      /// &lt;summary&gt;
      /// Parse excel list to set of entities.
      /// &lt;/summary&gt;
      public WeatherSheetDto ParseSheet(ISheet sheet)
      {
          var documentDto = new WeatherSheetDto();
  
          foreach (IRow row in sheet)
          {
              if (!TryParseRow(row, out var rowDto))
              {
                  continue;
              }
              
              documentDto.WindDirections.UnionWith(rowDto.WindDirections);
              documentDto.Cloudiness.Add(rowDto.Cloudiness);
              documentDto.WeatherConditions.Add(rowDto.WeatherCondition);
              documentDto.Measures.Add(rowDto.Measure);
          }
          
          return documentDto;
      }
  
      /// &lt;summary&gt;
      /// Parse excel row to set of entities.
      /// &lt;/summary&gt;
      public bool TryParseRow(IRow row, out WeatherRowDTO? result)
      {
          try
          {
              var cloudiness = ParseCloudiness(row);
                  
              result = new WeatherRowDTO
              {
                  WindDirections = ParseWindDirections(row),
                  Cloudiness = cloudiness,
                  WeatherCondition = ParseWeatherCondition(row),
                  Measure = ParseMeasure(row),
              };
  
              return true;
          }
          catch (Exception e) when (e is FormatException or InvalidOperationException)
          {
              result = null;
              return false;
          }
      }
  
      /// &lt;summary&gt;
      /// Parse excel row to Measure.
      /// &lt;/summary&gt;
      public Measure ParseMeasure(IRow row)
      {
          var timeSpan = TimeSpan.Parse(row.GetStringCell(MeasureCells.Time));
  
          return new Measure
          {
              Id = Guid.NewGuid(),
              DateTime = DateTime.Parse(row.GetStringCell(MeasureCells.Date)).Add(timeSpan),
              TemperatureCelsius = (decimal) row.GetNumericCell(MeasureCells.TemperatureCelsius),
              AirHumidity = (byte) row.GetNumericCell(MeasureCells.AirHumidity),
              DewPoint = (decimal) row.GetNumericCell(MeasureCells.DewPoint),
              AtmospherePressure = (ushort) row.GetNumericCell(MeasureCells.AtmospherePressure),
              WindSpeed = (byte?) row.GetNullableNumericCell(MeasureCells.WindSpeed),
              CloudHeight = (ushort?) row.GetNullableNumericCell(MeasureCells.CloudHeight),
              AtmosphericVisibility = (byte?) row.GetNullableNumericCell(MeasureCells.AtmosphericVisibility),
              // CloudinessId = null,
              // ConditionId = null,
          };
      }
  
      /// &lt;summary&gt;
      /// Parse excel row to set of WindDirection.
      /// &lt;/summary&gt;
      public HashSet&lt;WindDirection&gt; ParseWindDirections(IRow row) =&gt;
          row
              .GetStringCell(MeasureCells.WindDirection)
              .Split(',')
              .Distinct()
              .Select(v =&gt; 
                  new WindDirection
                  {
                      Name = v,
                  })
              .ToHashSet();
  
      /// &lt;summary&gt;
      /// Parse excel row to Cloudiness.
      /// &lt;/summary&gt;
      public Cloudiness ParseCloudiness(IRow row) =&gt;
          new()
          {
              Value = (byte) row.GetNumericCell(MeasureCells.Cloudiness),
          };
  
      /// &lt;summary&gt;
      /// Parse excel row to WeatherCondition.
      /// &lt;/summary&gt;
      public WeatherCondition ParseWeatherCondition(IRow row) =&gt;
          new()
          {
              Name = row.GetStringCell(MeasureCells.WeatherCondition),
          };
  }
  </code></pre>
  <p>Raw excel data
  <img src="https://i.stack.imgur.com/06VHy.png" alt="enter image description here" /></p>
  <p>Output
  <img src="https://i.stack.imgur.com/VhQfO.png" alt="enter image description here" /></p>
  <p>I tried to save already readed data in a variable and search for related items from there. But in this way we have more complicated code and O(n^2) complicity.</p>