title: Linq to Entity and Contains?
body: <p><strong>Hi,</strong></p>
  
  <p>I have a linq to sql question like this : </p>
  
  <pre><code>tmpAdList1 = (from p in context.Ads
                              join h in context.AdCategories on p.CategoryId equals h.Id
                              join l in context.Location on p.UserLocationId equals l.Id
                              where
                              (adList.S == null || adList.S.Length &lt; 1 || p.Title.Contains(adList.S) || p.Description.Contains(adList.S)) &amp;&amp;
                              (categorylevelOrder.Length &lt; 1 || h.LevelOrder.StartsWith(categorylevelOrder)) &amp;&amp;
  
                              ((locationIdList != null &amp;&amp; lList.Contains(l.Id)) ||
                              (locationLevelOrder.Length &lt; 1 || l.LevelOrder.StartsWith(locationLevelOrder))) &amp;&amp;
  
                              ((adTypeO1 == AdType.Unknown &amp;&amp; adTypeO2 == AdType.Unknown &amp;&amp; adTypeO3 == AdType.Unknown &amp;&amp; adTypeO4 == AdType.Unknown &amp;&amp; adTypeO5 == AdType.Unknown) ||
                              (p.TypeOfAd == (int)adTypeO1 || p.TypeOfAd == (int)adTypeO2 || p.TypeOfAd == (int)adTypeO3 || p.TypeOfAd == (int)adTypeO4 || p.TypeOfAd == (int)adTypeO5)) &amp;&amp; //Check for default filters
                              ((AdListShowType)adList.ALS.ST == AdListShowType.Both || adList.ALS.ST == p.OwnerType) &amp;&amp;
                              (p.PublishedDate.HasValue &amp;&amp; p.PublishedDate.Value.CompareTo(fetchAdsTo) &gt;= 1) &amp;&amp;
                              ((adOwnerType1.HasValue &amp;&amp; adOwnerType2.HasValue) || p.OwnerType == (int)adOwnerType1.Value) &amp;&amp;
                              p.InactivatedDate == null
                              orderby p.CreatedDate descending
                              select p).ToList();
  </code></pre>
  
  <p><strong>See Edit1 for the whole method</strong></p>
  
  <p>After this question is runned some more filtering will be done(within the context) but to make this as fast as possible I aim to fetch as little records as possible from the SQL server in the first question.</p>
  
  <p>The problem is that I need to compare locationIdList that is a int[] to the entity. The exception thrown is :</p>
  
  <p><strong>Cannot compare elements of type 'System.Int32[]'. Only primitive types (such as Int32, String, and Guid) and entity types are supported.</strong></p>
  
  <p>I have google the problem and this is a known problem, I have however found examples like this : </p>
  
  <pre><code>var list = new List&lt;int&gt; { 1, 2, 3, 5 };
  var result = from s in DB.Something
               where list.Contains(s.Id)
               select s;
  </code></pre>
  
  <p>But this throws the same exception? I have also read that a storedprocedure could solve the problem but I havent found how this work?</p>
  
  <p>Any suggestions?</p>
  
  <p><strong>BestRegards</strong></p>
  
  <p><strong>Edit1 :</strong> The entire method : </p>
  
  <pre><code>public List&lt;Ad&gt; GetAds(AdList adList, DateTime fetchAdsTo, out int totalAds)
  {
      AdType adTypeO1 = AdType.Unknown;
      AdType adTypeO2 = AdType.Unknown;
      AdType adTypeO3 = AdType.Unknown;
      AdType adTypeO4 = AdType.Unknown;
      AdType adTypeO5 = AdType.Unknown;
  
      int? adOwnerType1 = null;
      int? adOwnerType2 = null;
  
      FilterModel filterModel = new FilterModel();
  
      List&lt;AdCategoryFilter&gt; adCategoryFilterList;
      AdsFilterValues adsFilterValues;
      List&lt;AdsFilterValueWrapper&gt; seartchFilterValueList;
      AdsFilterValueWrapper seartchFilterValue = null;
      List&lt;Ad&gt; tmpAdList1;
      List&lt;Ad&gt; tmpAdList2 = new List&lt;Ad&gt;();
  
      int locationId = -1;
      int[] locationIdList = null;
      string locationLevelOrder = string.Empty;
  
      int categoryId = -1;
      string categorylevelOrder = string.Empty;
  
      AdCategoryFilter adCategoryFilter;
  
      AdListCompare adListCompare;
  
      Boolean firstDropDownMatch = false;
      Boolean secondDropDownMatch = false;
  
      totalAds = 0;
  
      int machedFilterCount;
  
      categoryId = AdHandler.Instance.ExtractCategoryId(adList.CS);
  
      //If there is multiple choises
      //This is the last level, that means that we can check against the ID dircly
      if (adList.LS.L3.Count &gt; 0)
          locationIdList = adList.LS.L3.ToArray();
      else
          locationId = AdHandler.Instance.ExtractLocationId(adList.LS);
  
  
      switch ((AdOwnerType)adList.ALS.ST)
      {
          case AdOwnerType.Both:
              adOwnerType1 = (int)AdOwnerType.Private;
              adOwnerType2 = (int)AdOwnerType.Company;
              break;
          case AdOwnerType.Company:
              adOwnerType1 = (int)AdOwnerType.Company;
              break;
          case AdOwnerType.Private:
              adOwnerType1 = (int)AdOwnerType.Private;
              break;
      }
  
      #region GetFilters
      adCategoryFilterList = filterModel.GetCategoryFilterByCategory(categoryId);
      seartchFilterValueList = FilterHandler.Instance.ConvertAdFilterToModel(adList.F, adCategoryFilterList, FilterType.Display);
      #endregion
  
      #region Set Default filters (Buy, Let, Sell, Swap, WishRent)
      foreach (AdsFilterValueWrapper filterWrapper in seartchFilterValueList)
      {
          if ((adCategoryFilter = adCategoryFilterList.Where(c =&gt; c.Id == filterWrapper.FilterId).FirstOrDefault()) != null)
          {
              switch ((PublicAdFilterKey)adCategoryFilter.PublicAdFilterKey)
              {
                  case PublicAdFilterKey.Buy:
                      {
                          if (filterWrapper.AdsFilterValues1.ValueNumber &gt; 0)
                              adTypeO1 = AdType.Buy;
                          break;
                      }
                  case PublicAdFilterKey.Let:
                      {
                          if (filterWrapper.AdsFilterValues1.ValueNumber &gt; 0)
                              adTypeO2 = AdType.Let;
                          break;
                      }
                  case PublicAdFilterKey.Sell:
                      {
                          if (filterWrapper.AdsFilterValues1.ValueNumber &gt; 0)
                              adTypeO3 = AdType.Sell;
                          break;
                      }
                  case PublicAdFilterKey.Swap:
                      {
                          if (filterWrapper.AdsFilterValues1.ValueNumber &gt; 0)
                              adTypeO4 = AdType.Swap;
                          break;
                      }
                  case PublicAdFilterKey.WishRent:
                      {
                          if (filterWrapper.AdsFilterValues1.ValueNumber &gt; 0)
                              adTypeO5 = AdType.WishRent;
                          break;
                      }
              }
          }
      }
  
      #region Remove default filters fom filterList
      adCategoryFilterList = adCategoryFilterList.Where(c =&gt; ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Buy &amp;&amp;
                                      ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Let &amp;&amp;
                                      ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Sell &amp;&amp;
                                      ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Swap &amp;&amp;
                                      ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.WishRent).ToList();
      #endregion
      #endregion
  
      var lList = adList.LS.L3.ToList&lt;int&gt;(); //new List&lt;int&gt; { 1, 2, 3, 5 };
  
  
      using (BissEntities context = new BissEntities())
      {
          if (categoryId &gt; 0)
              categorylevelOrder = context.AdCategories.Where(c =&gt; c.Id.Equals(categoryId)).FirstOrDefault().LevelOrder.Trim();
          if (locationId &gt; 0)
              locationLevelOrder = context.Location.Where(c =&gt; c.Id.Equals(locationId)).FirstOrDefault().LevelOrder.Trim();
  
          tmpAdList1 = (from p in context.Ads
                          join h in context.AdCategories on p.CategoryId equals h.Id
                          join l in context.Location on p.UserLocationId equals l.Id
                          where
                          (adList.S == null || adList.S.Length &lt; 1 || p.Title.Contains(adList.S) || p.Description.Contains(adList.S)) &amp;&amp;
                          (categorylevelOrder.Length &lt; 1 || h.LevelOrder.StartsWith(categorylevelOrder)) &amp;&amp;
  
                          ((locationIdList != null &amp;&amp; lList.Contains(l.Id)) ||
                          (locationLevelOrder.Length &lt; 1 || l.LevelOrder.StartsWith(locationLevelOrder))) &amp;&amp;
  
                          ((adTypeO1 == AdType.Unknown &amp;&amp; adTypeO2 == AdType.Unknown &amp;&amp; adTypeO3 == AdType.Unknown &amp;&amp; adTypeO4 == AdType.Unknown &amp;&amp; adTypeO5 == AdType.Unknown) ||
                          (p.TypeOfAd == (int)adTypeO1 || p.TypeOfAd == (int)adTypeO2 || p.TypeOfAd == (int)adTypeO3 || p.TypeOfAd == (int)adTypeO4 || p.TypeOfAd == (int)adTypeO5)) &amp;&amp; //Check for default filters
                          ((AdListShowType)adList.ALS.ST == AdListShowType.Both || adList.ALS.ST == p.OwnerType) &amp;&amp;
                          (p.PublishedDate.HasValue &amp;&amp; p.PublishedDate.Value.CompareTo(fetchAdsTo) &gt;= 1) &amp;&amp;
                          ((adOwnerType1.HasValue &amp;&amp; adOwnerType2.HasValue) || p.OwnerType == (int)adOwnerType1.Value) &amp;&amp;
                          p.InactivatedDate == null
                          orderby p.CreatedDate descending
                          select p).ToList();
  
          #region Filter collection
          foreach (Ad ad in tmpAdList1)
          {
              machedFilterCount = 0;
              adListCompare = AdListCompare.NotCompered;
  
              if (adCategoryFilterList.Count &gt; 0)
              {                     
                  //Loop the filters that belongs to the choosen category
                  foreach (AdCategoryFilter existingFilter in adCategoryFilterList)
                  {
                      //Se if the ad has the proper filter If not return it
                      if ((adsFilterValues = ad.AdsFilterValues.Where(c =&gt; c.CategoryFilterId == existingFilter.Id).FirstOrDefault()) != null || existingFilter.PublicAdFilterKey &gt; 0)
                      {
  
                          //If the filter is not a regular value filter but a filter pointed to a property on the ad
                          //Then extract the correct value and use it
                          if (existingFilter.PublicAdFilterKey &gt; 0)
                          {
                              adsFilterValues = new AdsFilterValues();
                              adsFilterValues.CategoryFilterId = existingFilter.Id;
  
                              switch ((PublicAdFilterKey)existingFilter.PublicAdFilterKey)
                              {
                                  case PublicAdFilterKey.Price:
                                      {
                                          adsFilterValues.ValueNumber = ad.Price;
                                          break;
                                      }
                              }
  
                          }
  
                          if ((seartchFilterValue = seartchFilterValueList.Where(c =&gt; c.AdsFilterValues1.CategoryFilterId == adsFilterValues.CategoryFilterId).FirstOrDefault()) != null)
                          {
                              firstDropDownMatch = false;
                              secondDropDownMatch = false;
                              adListCompare = AdListCompare.Compared;
  
                              switch ((FilterControlType)existingFilter.DisplayFilterControlType)
                              {
                                  case FilterControlType.TwoDropDown:
  
                                      //Check so the first dropdown value compare
                                      //If the index is the first then any value will do
                                      if (seartchFilterValue.FilterIndexPosition1 == FilterIndexPosition.First)
                                          firstDropDownMatch = true;
                                      else
                                      {
                                          if (adsFilterValues.ValueNumber.Value &gt;= seartchFilterValue.AdsFilterValues1.ValueNumber.Value)
                                              firstDropDownMatch = true;
                                      }
  
                                      if (firstDropDownMatch)
                                      {
                                          //Check so the second dropdown value compare
                                          //If the index is the last then any value will do
                                          if (seartchFilterValue.FilterIndexPosition2 == FilterIndexPosition.Last)
                                              secondDropDownMatch = true;
                                          else
                                          {
                                              if (adsFilterValues.ValueNumber.Value &lt;= seartchFilterValue.AdsFilterValues2.ValueNumber.Value)
                                                  secondDropDownMatch = true;
                                          }
  
                                          if (secondDropDownMatch)
                                              adListCompare = AdListCompare.Approved;
                                      }
  
                                      break;
                                  case FilterControlType.DropDown:
  
                                      //Check so the first dropdown value compare
                                      //If the index is the first then any value will do
                                      if (seartchFilterValue.FilterIndexPosition1 == FilterIndexPosition.First)
                                      {
                                          if (adsFilterValues.ValueNumber.Value &lt;= seartchFilterValue.AdsFilterValues1.ValueNumber.Value)
                                              firstDropDownMatch = true;
                                      }
                                      if (seartchFilterValue.FilterIndexPosition1 == FilterIndexPosition.Last)
                                      {
                                          if (adsFilterValues.ValueNumber.Value &gt;= seartchFilterValue.AdsFilterValues1.ValueNumber.Value)
                                              firstDropDownMatch = true;
                                      }
                                      else
                                      {
                                          if (adsFilterValues.ValueNumber.Value == seartchFilterValue.AdsFilterValues1.ValueNumber.Value)
                                              firstDropDownMatch = true;
                                      }
  
                                      if (firstDropDownMatch)
                                          adListCompare = AdListCompare.Approved;
  
                                      break;
                                  case FilterControlType.TextBox:
                                      if (adsFilterValues.ValueString.Equals(seartchFilterValue.AdsFilterValues1.ValueString))
                                          adListCompare = AdListCompare.Approved;
                                      break;
                                  case FilterControlType.CheckBox:
                                      if (adsFilterValues.ValueNumber != null &amp;&amp; adsFilterValues.ValueNumber.Value == seartchFilterValue.AdsFilterValues1.ValueNumber.Value)
                                          adListCompare = AdListCompare.Approved;
                                      break;
                                  default:
                                      adListCompare = AdListCompare.NotCompered;
                                      break;
                              }
  
                              //If no value is set, then break;
                              if (adListCompare != AdListCompare.Approved)
                                  break;
  
                              machedFilterCount++;
                          }
                      }
                      else
                      {
                          //If the ad is missing the filter then return it anyway, it might as well be correct
                          adListCompare = AdListCompare.Approved;
                          machedFilterCount = adCategoryFilterList.Count();
                      }
                  }
              }
              else
              {
                  adListCompare = AdListCompare.Approved;
                  machedFilterCount = adCategoryFilterList.Count();
              }
  
  
              if (adListCompare == AdListCompare.Approved &amp;&amp; machedFilterCount == adCategoryFilterList.Count())
                  tmpAdList2.Add(ad);
          }
          #endregion
  
          if (adList.ALS.OB == (int)AdListOrderBy.Price)
              tmpAdList2 = tmpAdList2.OrderBy(c =&gt; c.Price).ToList();
  
  
          totalAds = tmpAdList2.Count();
  
          return tmpAdList2.Skip((adList.ALS.P - 1) * adList.ALS.CP).Take(adList.ALS.CP).ToList();
      }
  }
  </code></pre>
  
  <p><strong>Edit 2: update</strong></p>
  
  <p>The <strong>main GetAd</strong> Method :</p>
  
  <pre><code>public List&lt;Ad&gt; GetAds(AdList adList, DateTime fetchAdsTo, out int totalAds)
          {
              LocationModel locationModel = new LocationModel();
              FilterModel filterModel = new FilterModel();
  
              List&lt;AdCategoryFilter&gt; adCategoryFilterList;
              List&lt;AdsFilterValueWrapper&gt; seartchFilterValueList;
  
              int categoryId = -1;
              List&lt;Ad&gt; outputList;
  
              totalAds = 0;
  
              #region Fetch the first ads by location
              outputList = GetAdsByLocations(locationModel.GetLocationOrderList(adList.GetLocationIds()), fetchAdsTo, false);
              if(outputList.Count &lt; 1)
                  return outputList;
              #endregion
  
              #region GetFilters
              categoryId = AdHandler.Instance.ExtractCategoryId(adList.CS);
              adCategoryFilterList = filterModel.GetCategoryFilterByCategory(categoryId);
              seartchFilterValueList = FilterHandler.Instance.ConvertAdFilterToModel(adList.F, adCategoryFilterList, FilterType.Display);
              #endregion
  
              #region Filter Default filters (Buy, Let, Sell, Swap, WishRent)
              FilterDefaultCustomFilters(outputList, adCategoryFilterList, seartchFilterValueList);
              if (outputList.Count == 0)
                  return outputList;
              else
              {
                  #region Remove default filters fom filterList
                  adCategoryFilterList = adCategoryFilterList.Where(c =&gt; ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Buy &amp;&amp;
                                                  ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Let &amp;&amp;
                                                  ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Sell &amp;&amp;
                                                  ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Swap &amp;&amp;
                                                  ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.WishRent).ToList();
                  #endregion
              }
              #endregion
  
              #region Filter Custom filters
              this.FilterCustomFilters(outputList, adCategoryFilterList, seartchFilterValueList);
              #endregion
  
              #region Order
              switch ((AdListOrderBy)adList.ALS.OB)
              {
                  case AdListOrderBy.Price:
                      outputList = outputList.OrderBy(c =&gt; c.Price).ToList(); break;
                  case AdListOrderBy.Latest:
                      outputList = outputList.OrderByDescending(c =&gt; c.PublishedDate).ToList(); break;
              }
              #endregion
  
              #region Total Ad Count
              totalAds = outputList.Count();
              #endregion
  
              #region Paging
              outputList = outputList.Skip((adList.ALS.P - 1) * adList.ALS.CP).Take(adList.ALS.CP).ToList();
              #endregion
  
              return outputList;
          }
  </code></pre>
  
  <p><strong>GetAdByLocation</strong></p>
  
  <pre><code>public List&lt;Ad&gt; GetAdsByLocations(string[] locationLevelOrderList, DateTime? fetchAdsTo, Boolean inactive) //, List&lt;Ad&gt; adList = null)
  {
      List&lt;Ad&gt; output;
  
      using (BissEntities context = new BissEntities())
      {
          if (fetchAdsTo.HasValue)
          {
              if (locationLevelOrderList.Count() == 0)
              {
                  output = (from a in context.Ads
                              join l in context.Location on a.UserLocationId equals l.Id
                              where a.InactivatedDate.HasValue == inactive &amp;&amp;
                              (a.PublishedDate.HasValue &amp;&amp; a.PublishedDate.Value.CompareTo(fetchAdsTo.Value) &gt;= 1)
                              select a).ToList();
              }
              else
              {
                  output = (from a in context.Ads
                              join l in context.Location on a.UserLocationId equals l.Id
                              where a.InactivatedDate.HasValue == inactive &amp;&amp;
                              (a.PublishedDate.HasValue &amp;&amp; a.PublishedDate.Value.CompareTo(fetchAdsTo.Value) &gt;= 1) &amp;&amp;
                              (locationLevelOrderList.Where(c =&gt; l.LevelOrder.StartsWith(c)).FirstOrDefault() != null) 
                              select a).ToList();
              }
  
          }
          else
          {
              if (locationLevelOrderList.Count() == 0)
              {
                  output = (from a in context.Ads
                              join l in context.Location on a.UserLocationId equals l.Id
                              where a.InactivatedDate.HasValue == inactive
                              select a).ToList();
              }
              else
              {
                  output = (from a in context.Ads
                              join l in context.Location on a.UserLocationId equals l.Id
                              where a.InactivatedDate.HasValue == inactive &amp;&amp;
                              (locationLevelOrderList.Count() == 0 || locationLevelOrderList.Where(c =&gt; l.LevelOrder.StartsWith(c)).FirstOrDefault() != null)
                              select a).ToList();
              }
          }
      }
  
      return output;
  }
  </code></pre>
  
  <p><strong>Note :</strong> The methods in main GetAd that begins with Filter in name will only work with the collection (no database actions)</p>