Linq to Entity and Contains?

Question

Hi,

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();

See Edit1 for the whole method

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.

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

Cannot compare elements of type 'System.Int32[]'. Only primitive types (such as Int32, String, and Guid) and entity types are supported.

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;

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

Any suggestions?

BestRegards

Edit1 : The entire method :

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();
    }
}

Edit 2: update

The main GetAd Method :

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;
}

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

Answer 1

Within your query, you have the comparison:

 ... locationIdList != null ...

Seeing as locationIdList is of type int[], the query cannot be translated since it only supports simple comparisons (as the error states).

You should be performing these checks outside the query, not within them. However since they are initialized within the method, you should just ensure that they are initialized and omit the check as it is unnecessary.

---

I would strongly recommend refactoring the whole method and the query. That is terribly long and hard to follow. Move the blocks of code into separate methods performing a small part of what you need then put them all together. It will make your code easier to maintain and correcting errors such such as this much easier.

Answer 2

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.