title: How do I get the entity framework to lookup whether related data EXISTS in a related table?
body: <p>I am working on making an entity framework project more scalable to suit our requirements. The project uses an EDMX file, Entity Framework, and .NET 4.0. The project has globally setup lazy loading.</p>
  
  <p>Basically, I have 2 tables:</p>
  
  <pre><code>Product
    ProductId
  
  ProductVariant
    ProductVariantId
    ProductId
  </code></pre>
  
  <p>I have a situation where there will be hundreds of thousands of related rows in ProductVariant for a given Product. So I would like to remove the navigation property for ProductVariant and make a query to lookup only the most pertainant data, rather than all of the related data.</p>
  
  <p>However, most of the code does something like:</p>
  
  <pre><code>if (Product.HasVariants) { [Loop Through Variants And Do Something] }
  </code></pre>
  
  <p>Which I will change to:</p>
  
  <pre><code>if (Product.HasVariants) { [Lookup Subset of Variants from DB And Do Something] }
  </code></pre>
  
  <p>HasVariants is a method that currently looks like this:</p>
  
  <pre><code>public bool HasVariants()
  {
      return this.Variants.Count &gt; 0;
  }
  </code></pre>
  
  <p>Variants is a navigation property. What I would like to do is create a lookup query with a field indicating whether any related data exists in the variants table rather than calling the database every time the code does this check.</p>
  
  <p>This is how I would accomplish this in a stored procedure:</p>
  
  <pre><code>CREATE Procedure GetProduct
    @ProductId
  AS
  
  Declare @HasVariants bit
  
  Set @HasVariants = EXISTS(Select ProductVariantId 
                            From ProductVariant 
                            Where ProductId = @ProductId)
  
  SELECT ProductId, &lt;Other Fields&gt;, @HasVariants AS HasVariants
  FROM Product
  WHERE ProductID = @ProductID
  
  Return
  </code></pre>
  
  <p>Note the presence of the HasVariants field, which I would use in the HasVariants method instead of the collection.</p>
  
  <p>My question is how do I accomplish the same thing (a lookup field from a related table) using an EDMX based model? I want to do this without using SQL statements in the code, but if the solution involves making HasVariants into a navigation property (possible?) it is acceptable. I already understand how to write a query to execute on demand, what I am looking for is a way to make the entity framework cache the HasVariants property the same way it does for the rest of the product data.</p>
  
  <p>Forgive me if I didn't provide enough information. I am new to the Entity Framework and I am really not sure what is needed to answer this question. </p>