0

I see that mostly everyone is recommending creating function inside SQL and then consume it in LINQ. Not sure if its possible to have full text search in LINQ without having to create a function inside SQL database.

The issue is, I have access to the code consuming entity model/edmx file, but have no access to model/edmx file itself. So, if I change anything in DB level, I wont be able to update model/edmx file.

I create full text catalog and associated it to the columns of table (Products), however it doesn't make any changes as expected.

In below code, I am trying to search products where name/description contains the provided keyword Current code:

var items = (from pr in objContext.Products
                             from prImg in objContext.ProductImages.Where(t => t.ProductId == pr.ProductId).DefaultIfEmpty()
                             from proD in objContext.PromotionDetails.Where(t => t.ProductId == pr.ProductId).DefaultIfEmpty()
                             from pro in objContext.Promotions.Where(t => t.PromotionId == proD.PromotionId).DefaultIfEmpty()
                             join m in objContext.SiteUsers on pr.MerchantId equals m.UserId
                             where m.IsActive == true && (pr.ProductName.Contains(keyword) ||
                             pr.Description.Contains(keyword)) &&
                             (categoryID != null && categoryID != 0 ? pr.CategoryId == categoryID : true)
                             && (merchantID != null && merchantID != 0 ? pr.MerchantId == merchantID : true)
                             && (brandID != null && brandID != 0 ? pr.BrandId == brandID : true)
                             && ((isPromotion != null && isPromotion != false) ? pr.IsPromotion == true : true)
                             && (prImg != null ? prImg.IsCoverImage == true : true)
                             && pr.IsActive == true && pr.IsDeleted == false
                             && (ZipCodes.Count > 0 ? ZipCodes.Contains(m.ZipCode) : true) // .Contains(ZipCodes) 
                             orderby pr.CreatedDate descending
                             select (new
                             {
                                 Name = pr.ProductName,
                                 productID = pr.ProductId,
                                 Description = pr.Description,
                                 IsPromotion = pr.IsPromotion,
                                 MerchantName = m.Store,
                                 IsVoucher = pr.IsVoucher,
                                 VoucherValue = pr.VoucherValue,
                                 StartDate = (pr.IsPromotion == true ? (pr.IsProductLevelPromotion == true ? pr.StartDate : pro.StartDate) : null),
                                 EndDate = (pr.IsPromotion == true ? (pr.IsProductLevelPromotion == true ? pr.EndDate : pro.EndDate) : null),
                                 RegularPrice = pr.RegularPrice,
                                 DiscountType = (pr.IsPromotion == true ? (pr.IsProductLevelPromotion == true ? pr.DiscountType : pro.PromotionTypeId == 1 ? 2 : proD.DiscountTypeId) : null), //pro.PromotionTypeId == 1 (group),DiscountTypeId = 2(percentage)
                                 DiscountValue = (pr.IsPromotion == true ? (pr.IsProductLevelPromotion == true ? pr.DiscountValue : pro.PromotionTypeId == 1 ? pro.Discount : proD.DiscountPrice) : null),
                                 NetPrice = (pr.IsPromotion == false ? pr.RegularPrice : (pr.IsProductLevelPromotion == true ? (pr.DiscountType == 1 ? (pr.RegularPrice - pr.DiscountValue) : ((pr.RegularPrice - ((pr.RegularPrice * pr.DiscountValue) / 100)))) : (pro.PromotionTypeId == 1 ? ((pr.RegularPrice - ((pr.RegularPrice * pro.Discount) / 100))) : (proD.DiscountTypeId == 1 ? (pr.RegularPrice - proD.DiscountPrice) : (pr.RegularPrice - ((pr.RegularPrice * proD.DiscountPrice) / 100)))))),
                                 URL = prImg != null ? prImg.ImagePath : string.Empty,
                                 isFeatured = pr.IsFeatured,
                                 DiscountSearch = (pr.IsPromotion == true ? (pr.IsProductLevelPromotion == true ? pr.DiscountPercentage.Value : proD.DiscountPercentage.Value) : 0),
                             })).Distinct().ToList();

What changes can I make to LINQ code or database, in order to improve the search results, considering the above situation.

More info, what if?

What if I use DataContext.ExecuteQuery to create a query and execute it, it looks like a good option. But, how do I get it to return data similar to below

select (new
                             {
                                 Name = pr.ProductName,
                                 productID = pr.ProductId,
                                 ...
                                 ..
                                 DiscountSearch = (pr.IsPromotion == true ? (pr.IsProductLevelPromotion == true ? pr.DiscountPercentage.Value : proD.DiscountPercentage.Value) : 0),
                             })
Hitin
  • 442
  • 7
  • 21
  • What's about indexes? – MJK Jul 31 '15 at 13:19
  • Why would you expect this to update your db? you've made a list into a variable.. and nothing else. – BugFinder Jul 31 '15 at 13:25
  • @BugFinder I am not expecting this to update DB. However, most of the post suggest that to have a full text search, one must create a database function or procedure that does full-text and return the result. Then add this function/procedure to model/edmx and use it. – Hitin Jul 31 '15 at 13:32
  • @Hitin Do you check the Tuning Advisor? are there any recommendations for indexes? – MJK Jul 31 '15 at 13:32
  • 2
    @MJK you are missing the point. Full text search has nothing to do with regular indexes. Full text search is dependent on full-text index and full-text catalog, which is already created. – Hitin Jul 31 '15 at 13:40
  • have a look at this question http://stackoverflow.com/questions/565617/full-text-search-in-linq, some suggestions are made for LINQ extensions without the need to create a new function or stored procedure – redrobot Aug 04 '15 at 15:02

0 Answers0