All geek questions in one place

Linq to Entity and contains?

Hello,

I have a linq to sql question:

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 launched, another filtering will be carried out (in context), but to do this as quickly as possible, I try to extract as few records as possible from the SQL server in the first question.

The problem is that I need to compare locationIdList, which is an int [] for the object. The exception is:

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

I have a problem with Google and this is a known issue, however I 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 does this cause the same exception? I also read that a stored procedure can solve the problem, but I did not find how it works?

Any suggestions?

Best regards

Edit1: The whole 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

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. Methods basically GetAd that start with the filter name will only work with the collection (no database actions)

+4
source share
2 answers

The last example works in the Entity 4 framework. If you get an exception, your application is most likely built as .NET 3.5 with the first version of the Entity framework that does not support Contains .

+2
source

There is a comparison in your request:

  ... locationIdList != null ...

Seeing that locationIdList is of type int[] , the request cannot be translated because it only supports simple comparisons (as indicated in the error message).

You must perform these checks outside of the request, and not inside them. However, since they are initialized inside the method, you just have to make sure that they are initialized and omit the check, since this is not necessary.

I would highly recommend refactoring the whole method and query. It is terribly long and hard to follow. Move the blocks of code into separate methods, doing a small part of what you need, and put them all together. This will make your code easier to maintain and fix errors like this a lot easier.

+7
source

Source: https://habr.com/ru/post/1346345/

More articles:


All Articles