CODEWITHSUNDEEP
asp.netlinqsharepoint
Jorge Martins
Jorge Martins

Reputation: 11

Dynamic LINQ Query in SharePoint

I have a page with filters and accordingly to what the user selects, it has to generate a query. I'm using this code:

var riskitem = (from risk in context.RisksList
                        where risk.ProjectCode == sProjectCode &&
                                    (
                                        (search == "" && status == "" && ispublic == TriState.NA) ||
                                        (search != "" && (
                                            (!String.IsNullOrEmpty(risk.Description) && risk.Description.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
                                            (!String.IsNullOrEmpty(risk.Title) && risk.Title.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
                                            (risk.Probability.HasValue && risk.Probability.Value.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
                                            (!String.IsNullOrEmpty(risk.Mitigation) && risk.Mitigation.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0) ||
                                            (!String.IsNullOrEmpty(risk.Observations) && risk.Observations.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
                                        )) ||
                                        (
                                            (status != "" && risk.Status.Value.ToString() == status) ||
                                            (status == "" && search != "" && risk.Status.Value.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
                                        ) ||
                                        (
                                            (ispublic != TriState.NA && ((risk.IsPublic.Value && ispublic == TriState.True) || (!risk.IsPublic.Value && ispublic == TriState.False))) ||
                                            (ispublic == TriState.NA && search != "" && risk.IsPublic.HasValue && risk.IsPublic.Value.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0)
                                        )
                                    )
                        select risk).Take((pagesize * (pageindex + 1)) + 1);

However Linq-To-Sharepoint doesn't convert most of this to CAML and the list I'm querying has more than 50000 items. It takes about 4-8 seconds to retrieve the items which is not acceptable. I've been trying to generate a dynamic query but so far I haven't been able to get it to work. With AND and OR operations to generate the query I could put all those conditions on code and increase performance.

If someone could help I would be appreciated.

Upvotes: 1

Views: 660

Answers (1)

brian brinley
brian brinley

Reputation: 2364

I've scrapped using linq to sharepoint for large lists with complex predicate filters. I've had far better performance using unions and/or merging the data from the splistitemcollection results of an SPQuery results.

It looks as though you are duplicating the work of the sharepoint search engine, have you considered if it is possible to replace with something like the keyword or fulltext query classes?

Also, you should be able to eliminate the need to do duplicate evaluation such as checking for empty string AND indexof. i.e... just simply something like 

Risk.IsPublic.Value.ToString().IndexOf(search, StringComparison.OrdinalIgnoreCase) > -1

What is the datatype for IsPublic ? If this is a boolean field you can save yourself some time as well without having to additional conversions.

Upvotes: 1

Related Questions