LINQ to EF Weighted Search Results : The Official Microsoft ASP.NET Forums

Reply

ovation22

Member

130 Points

LINQ to EF Weighted Search Results

Nov 17, 2012 03:48 PM|LINK

I'm trying to write a query in a such a way that only one db call is made. I have 3 columns that could be searched, which should yield different weights (Name, Description, Type). Exact match on Name yields the highest match weight, Name contains next highest, Description equals, Description contains, Type equals, Type contains.

My issue is that I cannot specify an additional "weight" column in the query, and OrderBy is not maintained through my unions.

Any ideas, thoughts, suggestions?

IQueryable<VendorDataCenterProduct> products = null;

            if (field.HasFlag(VendorDataCenterProductSearchField.ProductName))
            {
                var query = GetAll()
                    .Where(x => x.Name == searchString)
                    .OrderBy(c => (c.ProductTypeID + 1 - c.ProductTypeID) * (int)Models.Enums.VendorDataCenterProductSearchResultWeight.ProductNameEqual)
                    .Union(
                    GetAll()
                    .Where(x => x.Name.Contains(searchString))
                    .OrderBy(c => (c.ProductTypeID + 1 - c.ProductTypeID) * (int)Models.Enums.VendorDataCenterProductSearchResultWeight.ProductNameLike)
                    );

                if (products == null)
                    products = query;
                else
                    products = products.Union(query);
            }

            if (field.HasFlag(VendorDataCenterProductSearchField.VendorName))
            {
                var query = GetAll()
                    .Where(x => x.VendorDataCenter.Vendor.Name == searchString)
                    .OrderBy(c => (c.ProductTypeID + 1 - c.ProductTypeID) * (int)Models.Enums.VendorDataCenterProductSearchResultWeight.VendorNameEqual)
                    .Union(
                    GetAll()
                    .Where(x => x.VendorDataCenter.Vendor.Name.Contains(searchString))
                    .OrderBy(c => (c.ProductTypeID + 1 - c.ProductTypeID) * (int)Models.Enums.VendorDataCenterProductSearchResultWeight.VendorNameLike)
                    );

                if (products == null)
                    products = query;
                else
                    products = products.Union(query);
            }

            if (field.HasFlag(VendorDataCenterProductSearchField.ProductTypeDescription))
            {
                var query = GetAll()
                    .Where(x => x.ProductType.Description == searchString)
                    .OrderBy(c => (c.ProductTypeID + 1 - c.ProductTypeID) * (int)Models.Enums.VendorDataCenterProductSearchResultWeight.ProductTypeDescriptionEqual)
                    .Union(
                    GetAll()
                    .Where(x => x.ProductType.Description.Contains(searchString))
                    .OrderBy(c => (c.ProductTypeID + 1 - c.ProductTypeID) * (int)Models.Enums.VendorDataCenterProductSearchResultWeight.ProductTypeDescriptionLike)
                    );

                if (products == null)
                    products = query;
                else
                    products = products.Union(query);
            }

            return products.Distinct().OrderByDescending(c => c.VendorDataCenterProductReviewsAsProduct.Sum(r => r.Rating) / c.VendorDataCenterProductReviewsAsProduct.Count());

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: LINQ to EF Weighted Search Results

Nov 18, 2012 07:05 AM|LINK

Hello，

I'm not very clear about your meaning——So what actually you want to do? And your table's diagram? strcutures?

Reply

ovation22

Member

130 Points

47 Posts

Re: LINQ to EF Weighted Search Results

Nov 19, 2012 05:53 PM|LINK

Thanks for the reply.

Decided to take a step back. I'm now returning the data in my service layer and performing the sort in the controller. Seems to be a little cleaner this way.

Return the data through the service layer:

IQueryable<VendorDataCenterProduct> results = null;

            if (field.HasFlag(VendorDataCenterProductSearchField.ProductName))
            {
                var query = GetAll()
                    .Where(x => x.Name.Contains(searchString));

                if (results == null)
                    results = query;
                else
                    results = results.Union(query);
            }

            if (field.HasFlag(VendorDataCenterProductSearchField.VendorName))
            {
                var query = GetAll()
                    .Where(x => x.VendorDataCenter.Vendor.Name.Contains(searchString));

                if (results == null)
                    results = query;
                else
                    results = results.Union(query);
            }

            if (field.HasFlag(VendorDataCenterProductSearchField.ProductTypeDescription))
            {
                var query = GetAll()
                    .Where(x => x.ProductType.Description.Contains(searchString));

                if (results == null)
                    results = query;
                else
                    results = results.Union(query);
            }

            return results;

Abstracted a "SearchResult" ViewModel that contains one property: SearchResultWeight, and implemented on my ViewModel.

Then perform the sort in the controller, weighted by enum value:

var results = Products.SimpleSearch(searchString).Select(product => new ViewModels.Vendor.VendorDataCenterProduct()
{
Blah = product.Blah
SearchResultWeight =
                    (product.Name.Equals(searchString, StringComparison.CurrentCultureIgnoreCase) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductNameEqual : 0) +
                    (product.Name.ToLower().Contains(searchString.ToLower()) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductNameLike : 0) +
                    (product.VendorDataCenter.Vendor.Name.Equals(searchString, StringComparison.CurrentCultureIgnoreCase) ? (int)Enums.VendorDataCenterProductSearchResultWeight.VendorNameEqual : 0) +
                    (product.VendorDataCenter.Vendor.Name.ToLower().Contains(searchString.ToLower()) ? (int)Enums.VendorDataCenterProductSearchResultWeight.VendorNameLike : 0) +
                    (product.ProductType.Description.Equals(searchString, StringComparison.CurrentCultureIgnoreCase) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductTypeDescriptionEqual : 0) +
                    (product.ProductType.Description.ToLower().Contains(searchString.ToLower()) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductTypeDescriptionLike : 0)
            }).OrderByDescending(x => x.SearchResultWeight.Value).ThenByDescending(x => x.Reviews.Sum(y => y.Rating) / (x.Reviews.Count() >= 0 ? x.Reviews.Count() : 1));

return results;

Edited by ovation22 on Nov 19, 2012 05:54 PM
Edited by ovation22 on Nov 19, 2012 05:55 PM
Edited by ovation22 on Nov 19, 2012 06:52 PM
Marked as answer by ovation22 on Nov 20, 2012 01:09 AM

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: LINQ to EF Weighted Search Results

Nov 20, 2012 12:08 AM|LINK

Hi again,

Since your "SearchResultWeight" is an anoymous property whose type is of string, why does it come to a Value property？Please remove that：

var results = Products.SimpleSearch(searchString).Select(product => new ViewModels.Vendor.VendorDataCenterProduct()
{
Blah = product.Blah
SearchResultWeight =
                    (product.Name.Equals(searchString, StringComparison.CurrentCultureIgnoreCase) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductNameEqual : 0) +
                    (product.Name.ToLower().Contains(searchString.ToLower()) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductNameLike : 0) +
                    (product.VendorDataCenter.Vendor.Name.Equals(searchString, StringComparison.CurrentCultureIgnoreCase) ? (int)Enums.VendorDataCenterProductSearchResultWeight.VendorNameEqual : 0) +
                    (product.VendorDataCenter.Vendor.Name.ToLower().Contains(searchString.ToLower()) ? (int)Enums.VendorDataCenterProductSearchResultWeight.VendorNameLike : 0) +
                    (product.ProductType.Description.Equals(searchString, StringComparison.CurrentCultureIgnoreCase) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductTypeDescriptionEqual : 0) +
                    (product.ProductType.Description.ToLower().Contains(searchString.ToLower()) ? (int)Enums.VendorDataCenterProductSearchResultWeight.ProductTypeDescriptionLike : 0)
            }).OrderByDescending(x => x.SearchResultWeight).ThenByDescending(x => x.Reviews.Sum(y => y.Rating) / (x.Reviews.Count() >= 0 ? x.Reviews.Count() : 1));
 
return results;

And please make sure that there should be a "Reviews" property in your "return anoymous block".
Reguards!