[HELP] [Custom - Blog Posts Queries]

Topics: Customizing Orchard, Writing modules
Feb 10, 2015 at 2:04 AM
Hi every one I'm from Brasil,
I'm a big fan of orchard and i use it few years ago,
I read a lot of about orchard in the forums and blogs around de world,
But at this time I have a project that consume some xmls and include it in Orchard,
For that, I created a modulo that import this xmls in a custom content part in the Blog Post content type,
So I have this structure:
Some Blogs (Blog content type) (ex: En-Us, Pt-Br, ES-ES, etc) and respective posts about them (blog post content type with my custom content part),
And in this posts i have my big custom content part (this is big with some relationships and a lot of fields)
So i need to do some queries to filter my posts with some categories in my custom part relationship like some tags in the posts.

For this a use contentmanger query like this:

    public ActionResult ReleasesFilter(string language, string category, string page, int pagesize = 20)
            int pageNumber = 0;
            PostFilterViewModel viewModel = new PostFilterViewModel();
            var list = Shape.List();

                int.TryParse(page, out pageNumber);
                PagerParameters pagerParameters = new PagerParameters { Page = pageNumber, PageSize = pagesize };
                Pager pager = new Pager(_siteService.GetSiteSettings(), pagerParameters);
                viewModel.Pager = pager;

                var blogPart = _xmlToBlogImportingService.GetBlogPart("News Releases", language);

                if (blogPart == null)
                    return HttpNotFound();

                if (!_services.Authorizer.Authorize(Orchard.Core.Contents.Permissions.ViewContent, blogPart, T("Cannot view content")))
                    return new HttpUnauthorizedResult();

                var tagPai = _descriptiveMetadataSubjectCodeSubjectCategoryRepository.Table.Where(x =>
                       x.DescriptiveMetadataSubjectCodeSubjectCategory.Trim().ToLower().Replace('-', ' ') == category.Trim().ToLower().Replace('-', ' ')
                    || x.DescriptiveMetadataSubjectCodeSubjectCategoryPT.Trim().ToLower().Replace('-', ' ') == category.Trim().ToLower().Replace('-', ' ')
                    || x.DescriptiveMetadataSubjectCodeSubjectCategoryES.Trim().ToLower().Replace('-', ' ') == category.Trim().ToLower().Replace('-', ' ')).FirstOrDefault();

                var searchlist = _services.ContentManager
                    .Query<BlogPostPart>(VersionOptions.Published, "BlogPost")
                        .Where(x => x.DescriptiveMetadataLanguageFormalName == language.ToLower().Trim()).OrderByDescending(o => o.EnvelopeTransmissionId)
                        .Where(n => n.DescriptiveMetadataSubjectCodeSubject.Any(
                            code => code.DescriptiveMetadataSubjectCodeSubjectRecord.DescriptiveMetadataSubjectCodeSubjectCategoryCode == tagPai.DescriptiveMetadataSubjectCodeSubjectCategoryCode))
                           .WithQueryHints(new QueryHints().ExpandRecords<NewsMLPartRecord>().ExpandParts<NewsMLPart>());

                var totalItemCount = searchlist.Count();

                var blogPosts = searchlist
                         .Slice(pager.GetStartIndex(), pager.PageSize)
                         .Select(ci => ci.As<BlogPostPart>());

                dynamic blog = _services.ContentManager.BuildDisplay(blogPart);
                blog.Content.Add(Shape.Parts_Blogs_BlogPost_List(ContentItems: list), "5");
                blog.Content.Add(Shape.Pager(pager).TotalItemCount(totalItemCount), "Content:after");

                return new ShapeResult(this, blog);

            catch (Exception ex)
                throw ex;

    public BlogPart GetBlogPart(string BlogName, string Language)
            string idioma = string.Empty;

            if (Language.Trim().ToLower().Contains("pt"))
                idioma = "Português";
            else if (Language.Trim().ToLower().Contains("es"))
                idioma = "Español";
            else if (Language.Trim().ToLower().Contains("en"))
                idioma = "English";
                idioma = Language;

            string blogNameLogical = BlogName + " " + idioma + " │PR Newswire‏";

            var blogPart = _contentManager.Query<BlogPart>().List().Where(t => t.Name.Trim().ToLower() == blogNameLogical.Trim().ToLower()).FirstOrDefault();

            return blogPart;

My orchard is in IIS on a Azure VM machine and use Azure Sql Service.

In alot of time I have sql time out problems, or other erros about Sql and the time that the queries need to execute or about the size of the querie and i its is very very very slow,

how I can optimize or how it the correct way to do this query, or what the problem with this combination Azure VM + Azure Sql Server? Because in my machine it works not better but works with no erros even this is very slow.

Sorry for my english :0,

My orchard source version is 1.8 and I'm thinking about update to 1.8.1 now.