I need some linq (nHibernate) help. I'm trying to produce a search query that yields sorted results by a custom property value. The scenario is like this: products have a custom field "SortDate" where editors can input a date to be used for sorting search results. The problem is that if I make reqular linq join I could be missing the products that don't have the property value saved, so I want to somehow simulate a left join. The starting query that produces an inner join - I guess looks like (simplified):
var q = (from prod inProduct.All()
join pp inProductProperty.All() on prod.ProductId equals pp.ProductId
where prod.ParentProductId == null && prod.Name.Contains(searchString) && pp.ProductDefinitionField.Name == "SortDate"
I can find the products, by the property, but I was kinda' hopping to get them already sorted by sql in the result set and also have the property in the result set. Otherwise I have to do it in 3 steps:
1. Find the products by that property
2. Load the products and the property in some lists
3. Programatically sort the products by that property
So you see how this is a lot of overhead instead of getting the results already sorted by SQL in one fast query. This is easily doable with raw sql code, but looks like it's not that simple with linq.
You're right that some products would be missing if you don't do the outer join and there are ways of doing the outer join, but you got me thinking on a different approach. You're right that sorting products in memory is an option and it's straightforward to sort there as well so here's what I came up with:
var q = from product in productRepository.Select() where product.ParentProductId == null select product;
var products = q.ToList(); // LINQ brings back the products from the database at this point.
The rahther long sort statement at the end takes into account missing values by using the DefaultItEmpty extension method which provides a default value whenever a null is encountered. I'm using 0 as my default, but you're free to add which ever value is appropriate for your scenario.
It doesn't really matter whether you're sorting on the as part of the database call or in memory as you're dealing with the same resultset regardless.
Isn't that doing a lazy load of properties for each product in the list? Because now the products are in memory, but properties aren't. So when OrderBy is called it will iterate products in the list and for each it will hit sql with a query to load the property used for sorting, resulting in as many hits to sql as products are in the list. If a search is yielding many results, that could be an issue for performance.
The LINQ provider is smart enough to figure out to do it all in a single batch. So you're right in the sense that you will get extra roundtrips to the database, but you'll just get the one so you should be fine.
Unfortunately the provider doesn't support doing it all in a single batch as the DefaultIfEmptyOperator is not implemented.
If you want to cut down from two queries to one there's a trick to doing an outer join, but it does make your expression a little more complex to read.
Search products and sort by custom property value
Hey all,
I need some linq (nHibernate) help. I'm trying to produce a search query that yields sorted results by a custom property value. The scenario is like this: products have a custom field "SortDate" where editors can input a date to be used for sorting search results. The problem is that if I make reqular linq join I could be missing the products that don't have the property value saved, so I want to somehow simulate a left join. The starting query that produces an inner join - I guess looks like (simplified):
var q = (from prod in Product.All()
join pp in ProductProperty.All() on prod.ProductId equals pp.ProductId
where prod.ParentProductId == null && prod.Name.Contains(searchString) && pp.ProductDefinitionField.Name == "SortDate"
select new { Sku = prod.Sku, Name = prod.Name, SortDate = pp.Value == null ? pp.Value : "zzz"}).OrderBy(x=>x.SortDate);
Do I have a chance to make the above as a sort of left join or I am heading the wrong way?
Hi Andrei,
You might find this article useful: Querying Products by Custom Properties in uCommerce. Basically I'm using am embedded statement in my where clause. It might do the trick for you.
Hi Søren,
I can find the products, by the property, but I was kinda' hopping to get them already sorted by sql in the result set and also have the property in the result set. Otherwise I have to do it in 3 steps:
1. Find the products by that property
2. Load the products and the property in some lists
3. Programatically sort the products by that property
So you see how this is a lot of overhead instead of getting the results already sorted by SQL in one fast query. This is easily doable with raw sql code, but looks like it's not that simple with linq.
You're right that some products would be missing if you don't do the outer join and there are ways of doing the outer join, but you got me thinking on a different approach. You're right that sorting products in memory is an option and it's straightforward to sort there as well so here's what I came up with:
The rahther long sort statement at the end takes into account missing values by using the DefaultItEmpty extension method which provides a default value whenever a null is encountered. I'm using 0 as my default, but you're free to add which ever value is appropriate for your scenario.
It doesn't really matter whether you're sorting on the as part of the database call or in memory as you're dealing with the same resultset regardless.
Let me know if it works for you.
Isn't that doing a lazy load of properties for each product in the list? Because now the products are in memory, but properties aren't. So when OrderBy is called it will iterate products in the list and for each it will hit sql with a query to load the property used for sorting, resulting in as many hits to sql as products are in the list. If a search is yielding many results, that could be an issue for performance.
Hi Andrei,
The LINQ provider is smart enough to figure out to do it all in a single batch. So you're right in the sense that you will get extra roundtrips to the database, but you'll just get the one so you should be fine.
Unfortunately the provider doesn't support doing it all in a single batch as the DefaultIfEmptyOperator is not implemented.
If you want to cut down from two queries to one there's a trick to doing an outer join, but it does make your expression a little more complex to read.
is working on a reply...