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.
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:
ovation22
Member
130 Points
47 Posts
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());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?
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;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!
ovation22
Member
130 Points
47 Posts
Re: LINQ to EF Weighted Search Results
Nov 20, 2012 12:26 AM|LINK
SearchResultWeight is a nullable int.
Reviews is an ICollection. Products may or may not have reviews associated with them.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ to EF Weighted Search Results
Nov 20, 2012 12:29 AM|LINK
Please make sure that the fields that you wanna fetch out should be also included in your query statement.
ovation22
Member
130 Points
47 Posts
Re: LINQ to EF Weighted Search Results
Nov 20, 2012 12:53 AM|LINK
Thank you, yes, they're included through the mapped properties of the models through EF. "Blah" was included, and others excluded for brevity.
Thanks for your help.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ to EF Weighted Search Results
Nov 20, 2012 12:57 AM|LINK
But where's the Value?And where's the Reviews?You haven't included them yet in your block of anoymous codes. So you cannot fetch a correct answer.
ovation22
Member
130 Points
47 Posts
Re: LINQ to EF Weighted Search Results
Nov 20, 2012 01:07 AM|LINK
It's set within the query:
Reviews are returned as a property through EF. Seems to be working as desired.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ to EF Weighted Search Results
Nov 20, 2012 01:13 AM|LINK
Many thanks for your confirmation, welcome here again