Fluent API limitations
Hi,
I keep hitting fluent API limitations, as there are many types of queries it doesn't seem to support. I just got "Current LINQ provider does not support 'Max' method.". In the past, I also had problems when trying to match strings in various ways. (Edit: no ThenBy method following OrderBy, either)
Is there a PITS numbers for such issues?
Thanks.
Hi Thomas,
Most of the limitations to the queries are due to the OpenAccess LINQ parser, not the Fluent API. However, if you have any features that are in particular connected to the Fluent API (like public methods or properties that are not implemented or new feature that you want to see) and you do not find them in PITS, you can always send us feature requests and we will log them into the system.
Kind regards,Hi,
I just hit one more limitation: "Execution of 'System.Linq.Enumerable:Intersect(IEnumerable`1,IEnumerable`1)' on the database server side currently not implemented."
These problems force us to fetch collections early, before filtering them, which kills our site performances. As we currently face critical performance issues that we have to fix ASAP, we're going to have to complicate our code to speed it up, due to such issues. I'm currently thinking of implementing our own cache, can't think of anything else... It's not ideal, though.
Are we doing it wrong? If you can think of a way to work around these problems, please advise! Thanks.
For the record, we are currently in a situation which may end up making us drop Sitefinity, due to pressure from higher-ups in the company to get something working.
Hi Thomas,
I am really sorry to hear this. I am sure we can help you solve the issues, but can you tell me what is the specific case that you have? Exactly what kind of filtering are you trying to perform. Can you give me a piece of code, so that I can see if I can offer you an optimization option for it? Thanks in advance.
Kind regards,Hi Svetoslav,
Thanks for your answer.
Let's focus on a specific example: a custom widget that filters news items based on 3 classifications (Tags, Categories, and a custom classification).
We could move some simple operations to the DB side (using the NewsPluralFacade<BaseFacade> class, ie: before calling the Get() method), such as PublicationDate > something, and OrderBy operations, so they execute fast (much faster than if we do it after calling Get()). However, we couldn't make the IEnumerable<Guid>.Intersect() method work this way, so we had to do that after calling Get().
Relevant pieces of code:
IEnumerable<NewsItem> newsItems = App.WorkWith().NewsItems().Publihed() .Where(n => n.PublicationDate >= this.startDate) .OrderByDescending(i => i.PublicationDate).Get(); if (this.categoryTaxa != null) newsItems = TaxonomyHelper.Instance.FilterItems<NewsItem>(newsItems, this.categoryTaxa, CategoryHelper.FieldName); if (this.tagTaxa != null) newsItems = TaxonomyHelper.Instance.FilterItems<NewsItem>(newsItems, this.tagTaxa); if (this.newsTypeTaxa != null) newsItems = TaxonomyHelper.Instance.FilterItems<NewsItem>(newsItems, this.newsTypeTaxa); if (this.Limit >= 0) newsItems = newsItems.Take(this.Limit); // ... in TaxonomyHelper: public IEnumerable<T> FilterItems<T>(IEnumerable<T> items, IEnumerable<Taxon> taxa) where T : Content if (!taxa.Any()) return new T[0]; string taxonomyName = taxa.First().Taxonomy.Name; return this.FilterItems<T>(items, taxa, taxonomyName); public IEnumerable<T> FilterItems<T>(IEnumerable<T> items, IEnumerable<Taxon> taxa, string fieldName) where T : Content var ids = from t in taxa select t.Id; return items.Where(d => d.GetValue<TrackedList<Guid>>(fieldName).Intersect(ids).Any());Hi Thomas,
Can you give me a little more information on what is the end purpose of this code. If it is filtering items by taxonomies, here's a code that I can offer and that works with both fluent and native API (also if something isn't working with fluent API, be sure to try it with the Native API, as well). The example is for news:
TaxonomyManager manager = TaxonomyManager.GetManager(); var name = "test"; var taxa = manager.GetTaxa<HierarchicalTaxon>(); var taxon = taxa.Where(t => t.Title == name).SingleOrDefault().Id; NewsManager mng = NewsManager.GetManager(); var newsNative = mng.GetNewsItems().Where(i => i.GetValue<TrackedList<Guid>>("Category").Contains(taxon)); var newsFluent = App.WorkWith().NewsItems().Where(i => i.GetValue<TrackedList<Guid>>("Category").Contains(taxon));Hi Svetolsav,
Yes, as I said, I want to filter items by taxonomies / classifications. Like in your example, except I handle 3 collections of taxa, and not just one taxon. Could you post the equivalent code for handling a collection of taxa, rather than a single taxon? Thanks.
Also, there is no problem calling the Get() method. It returns an
IQueryable collection, so it doesn't matter if the Where clause is
before or after the Get() method - it won't execute until after the
IQueryable collection has been cast to an IEnumerable.
Yes, that's what I thought, but as I benchmarked performances, I noticed that on one or two occasions, it wasn't the case. Maybe I hit a weird case where IQueryable items were implicitly fetched, or something.
Hi Thomas,
Multiple collections of taxons can be used for filtering by just adding more Where clauses to the IQueryable (this won't increase the load). As long as you keep the collection to be an IQueryable, adding more filtering clauses wouldn't cause any problems.
On the IQueryable cast to IEnumerable - If you find a pattern for such a behaviour (and it is a bug coming from Sitefinity), please make sure to get back to me with steps to reproduce.
Hi Svetoslav,
If I'm not mistaken, more Where clauses wouldn't do what I want, because I want an "or"-based query, not "and"-based. The more taxon filters I get as parameter, the more results I want to return, not the other way around. That is to say that if I have a collection of 3 taxons as parameter, I want to build a query like "select news where news.taxa contains taxon1 or news.taxa contains taxon2 or news.taxa contains taxon3". That's why in the code I showed before, I used the Intersect method.
If you have a better (faster) way to do that kind of filtering than using the Intersect method like I did, I'm interested.
Hi Thomas,
What you can do is create a filter expression with a StringBuilder (for each guid in your collection of guids you should append a new "OR ID " to the expression) and in the end pass this expression to a Where clause. In order to have a Where clause with a string expression, you need to use our Dynamic LINQ extensions, that can be accessed by using the Telerik.Sitefinity.Data.Linq.Dynamic namespace.
Greetings,Hi Svetoslav,
Thanks for the info, I didn't know about this possibility. Could you give me a full concrete example? I can't get it to work.
This is my method:
public NewsPluralFacade<BaseFacade> FilterItems(NewsPluralFacade<BaseFacade> items, IEnumerable<Taxon> taxa, string fieldName) var ids = from t in taxa select t.Id; StringBuilder sb = new StringBuilder(); // ?? string query = sb.ToString(); return items.Where(query);Hello Thomas,
What you need to do is create a loop and append the IDs one by one to the String builder, like that:
for (int i = 0; i < taxa.Count; i++) if (i == 0) sb.AppendFormat("Id = 0", taxa[i].ToString()); else sb.AppendFormat(" OR Id = 0", taxa[i].ToString()); Hi Svetoslav,
Thanks, but it still doesn't work.
First, doing taxa[i].ToString() will give me its type: "Telerik.Sitefinity.Taxonomies.Model.HierarchicalTaxon"
... which doesn't look something I'd like to insert in a query.
Also, do I not need to specify somewhere that I'd like to filter based on classifications, or even a certain classification? How can the system guess that when I say "Id", I mean a certain classification Id?
Here's what my code currently looks like:
public NewsPluralFacade<BaseFacade> FilterItems(NewsPluralFacade<BaseFacade> items, IEnumerable<Taxon> taxa) if (!taxa.Any()) return items.Where(i => false); string query = "Id = " + string.Join(" OR Id = ", taxa.Select(t => t.Id.ToString())); var test = items.Where(query).Get().ToArray(); // Returns an empty array return items.Where(query);Hello Thomas,
Actually I forgot to add that you need to add Property.Contains((id)) to the filter expression for each of the IDs, where Property is the name of the relevant classification field.
Regards,Hi Svetoslav,
Sorry, I still don't get it. I don't know where or how I'm supposed to handle the property (maybe in the query string, replacing the equal sign?). Also, I could be wrong, but as far as I know, the data I'm interested in is not available as a property. That is to say that taxon objects do not have a property that specifies which taxonomies they belong to, which is why I do d.GetValue<TrackedList<Guid>>(fieldName) in my original code.
Could you please post a full, working code sample? That'd help a lot, and clear all the confusion. Thanks.
Hi Thomas,
Here's an example of the expression that you need to create with the string builder:
NewsManager mng = NewsManager.GetManager(); string expression = "Category.Contains((9A187790-BE97-414F-BE53-03739E8D191F)) OR Category.Contains((3D387012-5BA2-41A8-8732-D870F6BFB12F))"; var result = mng.GetNewsItems().Where(expression);Hi Svetoslav,
At last, it works! I was just missing the double brackets.
This took a while, but was worth investigating, because performances are significantly better than with the Intersect method. Quick benchmarking shows it's now 2 to 5 times faster, depending on cases.
Thanks for your help.
PS: the Max() method now works with Sitefinity 5.0, it seems.