title: How would I get a filtered result from 2 joined tables in a model
body: <p>I have 2 tables in SQL linked with a foreign key. I want to get the result of this join filtered (where) on a value in the child table into the model in ASP.NET Core MVC. How would I do this.</p>
  
  <p>I have these 2 tables in SQL with the foreign key. I did a database first. So, I have the models in my project. </p>
  
  <p>Also, I created interfaces to these models.</p>
  
  <p>Let's say, table 1 is Order and table 2 is OrderLine.
  On a SQL level I would do something like:</p>
  
  <pre><code>SELECT 
    Order.CustomerName
    , OrderLine.Product 
  FROM Order 
  JOIN OrderLine 
    ON Order.OrderID = OrderLine.OrderID 
  WHERE OrderLine.NotYetDelivered = True
  </code></pre>
  
  <p>Thus, I want to see a list of customers with the products that they ordered but that they didn't receive yet.</p>
  
  <p>How would I do this in ASP.NET Core with MVC?</p>
  
  <p>I tried a lot with Linq. and had the following piece of code:</p>
  
  <pre><code>var classification = classificationRepository.getAllClassifications();
  var classificationLanguage = classificationLanguageRepository.getAllClassificationLanguages();
  
  var ClassificationOne1Language = (from c in classification
                                           join l in classificationLanguage
                                             on c.SuClassificationId equals l.SuClassificationId
                                           //where l.SuLanguageId == 1
                                           select new { c.SuClassificationId, l.SuClassificationName });
          //var ClassificationOne1Language = (from c in classificationLanguage
          //                                 select new { c.SuClassificationId, c.SuClassificationName });
  
          List&lt;ClassificationWithLanguageViewModel&gt; x = new List&lt;ClassificationWithLanguageViewModel&gt;();
          foreach (var c in ClassificationOne1Language)
  </code></pre>
  
  <p>It says on the "foreach" loop the following:
  System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'</p>
  
  <p>But when I use SQL profiler I get the following SQL and that has 2 rows of result.</p>
  
  <pre><code>SELECT [s].[SuClassificationID], [s].[SuClassificationColor], [s].[SuClassificationStatusID], [s].[SuCreateDate], [s].[SuCreatorID], [s].[SuDefaultClassficiationPageID], [s].[SuDropDownSequence], [s].[SuHasDropDown], [s].[SuModifierID], [s].[SuModifyDate]
  FROM [SuClassification] AS [s]
  </code></pre>
  
  <p>Any suggestions why on a SQL level I have results but the code thinks different?</p>