我有一个LINQ to SQL的问题是这样的：

I have a linq to sql question like this :

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 < 1 || p.Title.Contains(adList.S) || p.Description.Contains(adList.S)) &&
                            (categorylevelOrder.Length < 1 || h.LevelOrder.StartsWith(categorylevelOrder)) &&

                            ((locationIdList != null && lList.Contains(l.Id)) ||
                            (locationLevelOrder.Length < 1 || l.LevelOrder.StartsWith(locationLevelOrder))) &&

                            ((adTypeO1 == AdType.Unknown && adTypeO2 == AdType.Unknown && adTypeO3 == AdType.Unknown && adTypeO4 == AdType.Unknown && adTypeO5 == AdType.Unknown) ||
                            (p.TypeOfAd == (int)adTypeO1 || p.TypeOfAd == (int)adTypeO2 || p.TypeOfAd == (int)adTypeO3 || p.TypeOfAd == (int)adTypeO4 || p.TypeOfAd == (int)adTypeO5)) && //Check for default filters
                            ((AdListShowType)adList.ALS.ST == AdListShowType.Both || adList.ALS.ST == p.OwnerType) &&
                            (p.PublishedDate.HasValue && p.PublishedDate.Value.CompareTo(fetchAdsTo) >= 1) &&
                            ((adOwnerType1.HasValue && adOwnerType2.HasValue) || p.OwnerType == (int)adOwnerType1.Value) &&
                            p.InactivatedDate == null
                            orderby p.CreatedDate descending
                            select p).ToList();

查看EDIT1整个方法

在这个问题是拼命地跑一些更多的过滤将完成（上下文中），但是使这个尽可能快我的目标是获取尽可能少的记录尽可能从在第一个问题SQL服务器。

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.

的问题是，我需要比较locationIdList即一个INT []到实体。引发的异常是：

The problem is that I need to compare locationIdList that is a int[] to the entity. The exception thrown is :

无法比拟类型的元素'System.Int32的[]'。只有原始类型（如的Int32，String和GUID）和实体类型的支持。

我有谷歌这个问题，这是一个已知的问题，我也不过发现这样的例子：

I have google the problem and this is a known problem, I have however found examples like this :

var list = new List<int> { 1, 2, 3, 5 };
var result = from s in DB.Something
             where list.Contains(s.Id)
             select s;

不过，这将引发相同的异常？我也看过，一个StoredProcedure的可以解决这个问题，但我还没有发现这是如何工作的？

But this throws the same exception? I have also read that a storedprocedure could solve the problem but I havent found how this work?

有什么建议？

BestRegards

EDIT1：的整个方法：

public List<Ad> 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<AdCategoryFilter> adCategoryFilterList;
    AdsFilterValues adsFilterValues;
    List<AdsFilterValueWrapper> seartchFilterValueList;
    AdsFilterValueWrapper seartchFilterValue = null;
    List<Ad> tmpAdList1;
    List<Ad> tmpAdList2 = new List<Ad>();

    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 > 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 => c.Id == filterWrapper.FilterId).FirstOrDefault()) != null)
        {
            switch ((PublicAdFilterKey)adCategoryFilter.PublicAdFilterKey)
            {
                case PublicAdFilterKey.Buy:
                    {
                        if (filterWrapper.AdsFilterValues1.ValueNumber > 0)
                            adTypeO1 = AdType.Buy;
                        break;
                    }
                case PublicAdFilterKey.Let:
                    {
                        if (filterWrapper.AdsFilterValues1.ValueNumber > 0)
                            adTypeO2 = AdType.Let;
                        break;
                    }
                case PublicAdFilterKey.Sell:
                    {
                        if (filterWrapper.AdsFilterValues1.ValueNumber > 0)
                            adTypeO3 = AdType.Sell;
                        break;
                    }
                case PublicAdFilterKey.Swap:
                    {
                        if (filterWrapper.AdsFilterValues1.ValueNumber > 0)
                            adTypeO4 = AdType.Swap;
                        break;
                    }
                case PublicAdFilterKey.WishRent:
                    {
                        if (filterWrapper.AdsFilterValues1.ValueNumber > 0)
                            adTypeO5 = AdType.WishRent;
                        break;
                    }
            }
        }
    }

    #region Remove default filters fom filterList
    adCategoryFilterList = adCategoryFilterList.Where(c => ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Buy &&
                                    ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Let &&
                                    ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Sell &&
                                    ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Swap &&
                                    ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.WishRent).ToList();
    #endregion
    #endregion

    var lList = adList.LS.L3.ToList<int>(); //new List<int> { 1, 2, 3, 5 };


    using (BissEntities context = new BissEntities())
    {
        if (categoryId > 0)
            categorylevelOrder = context.AdCategories.Where(c => c.Id.Equals(categoryId)).FirstOrDefault().LevelOrder.Trim();
        if (locationId > 0)
            locationLevelOrder = context.Location.Where(c => 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 < 1 || p.Title.Contains(adList.S) || p.Description.Contains(adList.S)) &&
                        (categorylevelOrder.Length < 1 || h.LevelOrder.StartsWith(categorylevelOrder)) &&

                        ((locationIdList != null && lList.Contains(l.Id)) ||
                        (locationLevelOrder.Length < 1 || l.LevelOrder.StartsWith(locationLevelOrder))) &&

                        ((adTypeO1 == AdType.Unknown && adTypeO2 == AdType.Unknown && adTypeO3 == AdType.Unknown && adTypeO4 == AdType.Unknown && adTypeO5 == AdType.Unknown) ||
                        (p.TypeOfAd == (int)adTypeO1 || p.TypeOfAd == (int)adTypeO2 || p.TypeOfAd == (int)adTypeO3 || p.TypeOfAd == (int)adTypeO4 || p.TypeOfAd == (int)adTypeO5)) && //Check for default filters
                        ((AdListShowType)adList.ALS.ST == AdListShowType.Both || adList.ALS.ST == p.OwnerType) &&
                        (p.PublishedDate.HasValue && p.PublishedDate.Value.CompareTo(fetchAdsTo) >= 1) &&
                        ((adOwnerType1.HasValue && adOwnerType2.HasValue) || p.OwnerType == (int)adOwnerType1.Value) &&
                        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 > 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 => c.CategoryFilterId == existingFilter.Id).FirstOrDefault()) != null || existingFilter.PublicAdFilterKey > 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 > 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 => 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 >= 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 <= 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 <= seartchFilterValue.AdsFilterValues1.ValueNumber.Value)
                                            firstDropDownMatch = true;
                                    }
                                    if (seartchFilterValue.FilterIndexPosition1 == FilterIndexPosition.Last)
                                    {
                                        if (adsFilterValues.ValueNumber.Value >= 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 && 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 && machedFilterCount == adCategoryFilterList.Count())
                tmpAdList2.Add(ad);
        }
        #endregion

        if (adList.ALS.OB == (int)AdListOrderBy.Price)
            tmpAdList2 = tmpAdList2.OrderBy(c => c.Price).ToList();


        totalAds = tmpAdList2.Count();

        return tmpAdList2.Skip((adList.ALS.P - 1) * adList.ALS.CP).Take(adList.ALS.CP).ToList();
    }
}

修改2：更新

在主要GetAd 方式：

public List<Ad> GetAds(AdList adList, DateTime fetchAdsTo, out int totalAds)
        {
            LocationModel locationModel = new LocationModel();
            FilterModel filterModel = new FilterModel();

            List<AdCategoryFilter> adCategoryFilterList;
            List<AdsFilterValueWrapper> seartchFilterValueList;

            int categoryId = -1;
            List<Ad> outputList;

            totalAds = 0;

            #region Fetch the first ads by location
            outputList = GetAdsByLocations(locationModel.GetLocationOrderList(adList.GetLocationIds()), fetchAdsTo, false);
            if(outputList.Count < 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 => ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Buy &&
                                                ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Let &&
                                                ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Sell &&
                                                ((PublicAdFilterKey)c.PublicAdFilterKey) != PublicAdFilterKey.Swap &&
                                                ((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 => c.Price).ToList(); break;
                case AdListOrderBy.Latest:
                    outputList = outputList.OrderByDescending(c => 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;
        }

GetAdByLocation

public List<Ad> GetAdsByLocations(string[] locationLevelOrderList, DateTime? fetchAdsTo, Boolean inactive) //, List<Ad> adList = null)
{
    List<Ad> 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 &&
                            (a.PublishedDate.HasValue && a.PublishedDate.Value.CompareTo(fetchAdsTo.Value) >= 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 &&
                            (a.PublishedDate.HasValue && a.PublishedDate.Value.CompareTo(fetchAdsTo.Value) >= 1) &&
                            (locationLevelOrderList.Where(c => 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 &&
                            (locationLevelOrderList.Count() == 0 || locationLevelOrderList.Where(c => l.LevelOrder.StartsWith(c)).FirstOrDefault() != null)
                            select a).ToList();
            }
        }
    }

    return output;
}

注意：与筛选始于名主要GetAd方法将只与收集工作（没有数据库操作）

Note : The methods in main GetAd that begins with Filter in name will only work with the collection (no database actions)

推荐答案

最后一个例子中的实体框架4.工作。如果你得到例外您的应用程序是最有可能建成.NET 3.5与实体框架的第一个版本，这没'科技支撑包含。

The last example works in Entity framework 4. If you get exception your application is the most probably built as .NET 3.5 with first version of Entity framework which didn't support Contains.