title: Efficient way of checking if many-to-many relationship exists in EF4.1
body: <p>I have a many-to-many relationship between two entities - Media and MediaCollection.  I want to check if a certain Media already exists in a collection.  I can do this as follows:</p>
  
  <pre><code>mediaCollection.Media.Any(m =&gt; m.id == mediaId)
  </code></pre>
  
  <p>However, mediaCollection.Media is an ICollection, so to me this looks like it will have to retrieve every Media in the collection from the database just to make this check.  As there could be many media in a collection, this seems very inefficient.  I'n thinking that I should use a method of IQueryable, but I can't see how to do this for many-to-many relationships.</p>
  
  <p>How can I check for the existence of the relationship without retrieving the whole collection?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I am generating the EF data model from my database, then using the built in VS POCO T4 templates to generate my data context and entity classes.  I think the problem is that the generated code does not return EntityCollection for the navigation properties, but instead ObjectSet.  ObjectSet implements IQueryable, but does not expose a CreateSourceQuery() method.</p>
  
  <p>Here is a stripped down version of the relevant lines from the context:</p>
  
  <pre><code>    public partial class Entities : ObjectContext
      {
          public const string ConnectionString = "name=Entities";
          public const string ContainerName = "Entities";
  
          #region Constructors
  
          public Entities()
              : base(ConnectionString, ContainerName)
          {
              this.ContextOptions.LazyLoadingEnabled = true;
          }
  
          public Entities(string connectionString)
              : base(connectionString, ContainerName)
          {
              this.ContextOptions.LazyLoadingEnabled = true;
          }
  
          public Entities(EntityConnection connection)
              : base(connection, ContainerName)
          {
              this.ContextOptions.LazyLoadingEnabled = true;
          }
  
          #endregion
  
          #region ObjectSet Properties
  
          public ObjectSet&lt;MediaCollection&gt; MediaCollections
          {
              get { return _mediaCollections  ?? (_mediaCollections = CreateObjectSet&lt;MediaCollection&gt;("MediaCollections")); }
          }
          private ObjectSet&lt;MediaCollection&gt; _mediaCollections;
  
          // snipped many more
  
          #endregion
      }
  </code></pre>
  
  <p>And here is a stripped down version of the class for the MediaCollection entity:</p>
  
  <pre><code>    public partial class MediaCollection
      {
          #region Primitive Properties
  
          // snipped
  
          #endregion
  
          #region Navigation Properties    
  
          public virtual ICollection&lt;Medium&gt; Media
          {
              get
              {
                  if (_media == null)
                  {
                      var newCollection = new FixupCollection&lt;Medium&gt;();
                      newCollection.CollectionChanged += FixupMedia;
                      _media = newCollection;
                  }
                  return _media;
              }
              set
              {
                  if (!ReferenceEquals(_media, value))
                  {
                      var previousValue = _media as FixupCollection&lt;Medium&gt;;
                      if (previousValue != null)
                      {
                          previousValue.CollectionChanged -= FixupMedia;
                      }
                      _media = value;
                      var newValue = value as FixupCollection&lt;Medium&gt;;
                      if (newValue != null)
                      {
                          newValue.CollectionChanged += FixupMedia;
                      }
                  }
              }
          }
          private ICollection&lt;Medium&gt; _media;
  
          private void FixupMedia(object sender, NotifyCollectionChangedEventArgs e)
          {
              if (e.NewItems != null)
              {
                  foreach (Medium item in e.NewItems)
                  {
                      if (!item.MediaCollections.Contains(this))
                      {
                          item.MediaCollections.Add(this);
                      }
                  }
              }
  
              if (e.OldItems != null)
              {
                  foreach (Medium item in e.OldItems)
                  {
                      if (item.MediaCollections.Contains(this))
                      {
                          item.MediaCollections.Remove(this);
                      }
                  }
              }
          }
  
          // snip
  
          #endregion
      }
  </code></pre>
  
  <p>And finally, here is the FixupCollection that the template also generates:</p>
  
  <pre><code>    public class FixupCollection&lt;T&gt; : ObservableCollection&lt;T&gt;
      {
          protected override void ClearItems()
          {
              new List&lt;T&gt;(this).ForEach(t =&gt; Remove(t));
          }
  
          protected override void InsertItem(int index, T item)
          {
              if (!this.Contains(item))
              {
                  base.InsertItem(index, item);
              }
          }
      }
  </code></pre>