title: LINQ query to get all entities with member of specified type
body: <p>I have following entities and DbContext:</p>
  
  <pre><code>public class Customer
  {
      [Key]
      public Guid Guid { get; set; }
  
      [Required]
      public string FullName { get; set; }
  
      public virtual ICollection&lt;Order&gt; Orders { get; set; }
  }
  
  public abstract class Product
  {
      [Key]
      public Guid Guid { get; set; }
  
      [Required]
      public String Title { get; set; }
  }
  
  public class Book : Product
  {
      public String Author { get; set; }
      public String Year { get; set; }
  }
  
  public class DVD : Product
  {
      public String Genre { get; set; }
      public String Country { get; set; }
      public String MPAA { get; set; }
      public String Year { get; set; }
  }
  
  public class Order
  {
      [Key, Column(Order = 0)]
      public Guid CustomerGuid { get; set; }
  
      [Key, Column(Order = 1)]
      public Guid ProductGuid { get; set; }
  
      public Int16 Quantity { get; set; }
  
      public Decimal Price { get; set; }
  
      public Decimal Total { get; set; }
  
      public virtual Customer Customer { get; set; }
  
      public virtual Product Product { get; set; }
  }
  
  public class StoreContext : DbContext
  {
      public DbSet&lt;Customer&gt; Customers { get; set; }
      public DbSet&lt;Book&gt; Books { get; set; }
      public DbSet&lt;DVD&gt; DVDs { get; set; }
      public DbSet&lt;Order&gt; Orders { get; set; }
  
      public StoreContext(string nameOrConnectionString)
          : base(nameOrConnectionString)
      {
  
      }
  }
  </code></pre>
  
  <p>What is the best way to query all orders where Product is Book? When I use following query I get a bunch of CASE in SQL.</p>
  
  <pre><code>var query = from order in context.Orders
              where order.Product is Book
              select order.Product as Book;
  </code></pre>
  
  <p><b>UPDATE</b>
  Aducci's solution is much better than my, but I still get overloaded SQL in more complex queries. </p>
  
  <p>For example, </p>
  
  <pre><code>var query = context.SaleOffers.Select(x =&gt; x.Realty).OfType&lt;Flat&gt;().Select(x =&gt; x.Planning.Name);
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>SELECT 
  [Extent10].[Name] AS [Name]
  FROM    [dbo].[SaleOffers] AS [Extent1]
  INNER JOIN [dbo].[Offers] AS [Extent2] ON [Extent1].[Guid] = [Extent2].[Guid]
  LEFT OUTER JOIN  (SELECT [UnionAll5].[C1] AS [C1], [UnionAll5].[C2] AS [C2], [UnionAll5].[C3] AS [C3], [UnionAll5].[C4] AS [C4], [UnionAll5].[C5] AS [C5], [UnionAll5].[C6] AS [C6], [UnionAll5].[C7] AS [C7]
      FROM   (SELECT 
          [UnionAll4].[C1] AS [C1], 
          [UnionAll4].[C2] AS [C2], 
          [UnionAll4].[C3] AS [C3], 
          [UnionAll4].[C4] AS [C4], 
          [UnionAll4].[C5] AS [C5], 
          [UnionAll4].[C6] AS [C6], 
          [UnionAll4].[C7] AS [C7]
          FROM  (SELECT 
              [UnionAll3].[C1] AS [C1], 
              [UnionAll3].[C2] AS [C2], 
              [UnionAll3].[C3] AS [C3], 
              [UnionAll3].[C4] AS [C4], 
              [UnionAll3].[C5] AS [C5], 
              [UnionAll3].[C6] AS [C6], 
              [UnionAll3].[C7] AS [C7]
              FROM  (SELECT 
                  [UnionAll2].[C1] AS [C1], 
                  [UnionAll2].[C2] AS [C2], 
                  [UnionAll2].[C3] AS [C3], 
                  [UnionAll2].[C4] AS [C4], 
                  [UnionAll2].[C5] AS [C5], 
                  [UnionAll2].[C6] AS [C6], 
                  [UnionAll2].[C7] AS [C7]
                  FROM  (SELECT 
                      [UnionAll1].[Guid] AS [C1], 
                      [UnionAll1].[C1] AS [C2], 
                      [UnionAll1].[C2] AS [C3], 
                      [UnionAll1].[C3] AS [C4], 
                      [UnionAll1].[C4] AS [C5], 
                      [UnionAll1].[C5] AS [C6], 
                      [UnionAll1].[C6] AS [C7]
                      FROM  (SELECT 
                          [Extent3].[Guid] AS [Guid], 
                          CAST(NULL AS uniqueidentifier) AS [C1], 
                          cast(0 as bit) AS [C2], 
                          cast(0 as bit) AS [C3], 
                          cast(0 as bit) AS [C4], 
                          cast(0 as bit) AS [C5], 
                          cast(0 as bit) AS [C6]
                          FROM [dbo].[Rooms] AS [Extent3]
                      UNION ALL
                          SELECT 
                          [Extent4].[Guid] AS [Guid], 
                          [Extent4].[PlanningGuid] AS [PlanningGuid], 
                          cast(1 as bit) AS [C1], 
                          cast(0 as bit) AS [C2], 
                          cast(0 as bit) AS [C3], 
                          cast(0 as bit) AS [C4], 
                          cast(0 as bit) AS [C5]
                          FROM [dbo].[Flats] AS [Extent4]) AS [UnionAll1]
                  UNION ALL
                      SELECT 
                      [Extent5].[Guid] AS [Guid], 
                      CAST(NULL AS uniqueidentifier) AS [C1], 
                      cast(0 as bit) AS [C2], 
                      cast(1 as bit) AS [C3], 
                      cast(0 as bit) AS [C4], 
                      cast(0 as bit) AS [C5], 
                      cast(0 as bit) AS [C6]
                      FROM [dbo].[CommercialProperties] AS [Extent5]) AS [UnionAll2]
              UNION ALL
                  SELECT 
                  [Extent6].[Guid] AS [Guid], 
                  CAST(NULL AS uniqueidentifier) AS [C1], 
                  cast(0 as bit) AS [C2], 
                  cast(0 as bit) AS [C3], 
                  cast(1 as bit) AS [C4], 
                  cast(0 as bit) AS [C5], 
                  cast(0 as bit) AS [C6]
                  FROM [dbo].[Houses] AS [Extent6]) AS [UnionAll3]
          UNION ALL
              SELECT 
              [Extent7].[Guid] AS [Guid], 
              CAST(NULL AS uniqueidentifier) AS [C1], 
              cast(0 as bit) AS [C2], 
              cast(0 as bit) AS [C3], 
              cast(0 as bit) AS [C4], 
              cast(1 as bit) AS [C5], 
              cast(0 as bit) AS [C6]
              FROM [dbo].[Garages] AS [Extent7]) AS [UnionAll4]
      UNION ALL
          SELECT 
          [Extent8].[Guid] AS [Guid], 
          CAST(NULL AS uniqueidentifier) AS [C1], 
          cast(0 as bit) AS [C2], 
          cast(0 as bit) AS [C3], 
          cast(0 as bit) AS [C4], 
          cast(0 as bit) AS [C5], 
          cast(1 as bit) AS [C6]
          FROM [dbo].[Lots] AS [Extent8]) AS [UnionAll5]
      INNER JOIN [dbo].[Realties] AS [Extent9] ON [UnionAll5].[C1] = [Extent9].[Guid] ) AS [Join2] ON [Extent2].[RealtyGuid] = [Join2].[C1]
  LEFT OUTER JOIN [dbo].[References] AS [Extent10] ON ([Extent10].[Discriminator] = 'FlatPlanning') AND ((CASE WHEN ([Join2].[C1] IS NULL) THEN CAST(NULL AS uniqueidentifier) WHEN ([Join2].[C4] = 1) THEN CAST(NULL AS uniqueidentifier) WHEN ([Join2].[C3] = 1) THEN [Join2].[C2] WHEN ([Join2].[C5] = 1) THEN CAST(NULL AS uniqueidentifier) WHEN ([Join2].[C6] = 1) THEN CAST(NULL AS uniqueidentifier) WHEN ([Join2].[C7] = 1) THEN CAST(NULL AS uniqueidentifier) END) = [Extent10].[Guid])
  WHERE CASE WHEN ([Join2].[C1] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([Join2].[C4] = 1) THEN '3X0X' WHEN ([Join2].[C3] = 1) THEN '3X1X' WHEN ([Join2].[C5] = 1) THEN '3X2X' WHEN ([Join2].[C6] = 1) THEN '3X3X' WHEN ([Join2].[C7] = 1) THEN '3X4X' ELSE '3X5X' END LIKE '3X1X%'
  </code></pre>
  
  <p>Thanks in advance!</p>