How to get DocumentAttachment field when limiting a query by Columns

S1T2 Developer asked on October 29, 2019 06:12

I am trying to get a page node that has a Document attachment but when I limit the query by columns the model comes back with a null for the Image field. The view model has an Image property of type DocumentAttachment and it is being set like this:


        public string DocumentName { get; }
        public string PeopleTitle { get; }
        public string PeopleHeading { get; }
        public string PeopleCopy { get; }
        public DocumentAttachment PeopleImage { get; set; }
        public string PeopleNodeAliasPath { get; }

        // Maps the data from the page type's fields to the view model properties
        public PeoplePageViewModel(PeoplePage peoplePage)
        {
            DocumentName = peoplePage.DocumentName;
            PeopleTitle = peoplePage.Fields.Title;
            PeopleHeading = peoplePage.Fields.Heading;
            PeopleCopy = peoplePage.Fields.Copy;
            PeopleImage = peoplePage.Fields.Image;
            PeopleNodeAliasPath = peoplePage.NodeAliasPath;
        }

And this is the code from the controller where I am trying to find the page/node and create the model.

PeoplePage peopleNode = PeoplePageProvider.GetPeoplePage("/ADIA-People", "en-us", "Adia")
                                           .Columns("NodeAliasPath", "DocumentName", "Title", "Heading", "Copy", "Image");

var peopleModel = new PeoplePageViewModel(peopleNode);

Any ideas how to improve the column query as including "Image" isn't working even though that is the exact field name in the page type.

Thanks

Correct Answer

S1T2 Developer answered on November 14, 2019 05:21

For anyone having the similar problems with attachments, we ended up using a media library for attachments.

Field was changed into Text with form control being Media selection, the value of the field is the link to the file, no joining table required.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Peter Mogilnitski answered on October 29, 2019 16:39

We've just discussed a similar problem here

0 votesVote for this answer Mark as a Correct answer

S1T2 Developer answered on November 6, 2019 06:32

Thanks Peter. I've read through that thread and I'm still having issues. If I understand correctly, the issue being discussed there is that the search is not being restricted to columns when searching multiple doc types.

The problem I'm having is that when using the generated document providers, if I limit my search using .Columns I get all fields specified with Columns except for any Document Attachments, which come back Null.

I read that it is best to limit the queries to certain columns for performance reasons. Is there not a way to query out Document Attachments in this way? Are they somehow linked through another column which I am missing in my query?

Thanks!

0 votesVote for this answer Mark as a Correct answer

S1T2 Developer answered on November 6, 2019 06:37

        var quotes = QuoteProvider.GetQuotes()
            //.Columns("Name", "Title", "Copy", "Image", "Avatar")
            .Path(parentPath, PathTypeEnum.Children)
            .OrderBy("NodeOrder");

        var pages = DocumentHelper.GetDocuments()
            .WhereIn("NodeGUID", quotes.Select(item => item.Name).ToList())
            .Columns("NodeGUID");

        var model = quotes.Select(item => new QuoteViewModel()
        {
            Name = item.Fields.Name,
            Title = item.Fields.Title,
            Copy = item.Fields.Copy,
            Image = item.Fields.Image,
            Avatar = item.Fields.Avatar
        });

For reference, the .Columns line breaks the query

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on November 7, 2019 02:59

Attachments are not stored at the document table anyway. Here is the attachment DB structure. They are in a separate table otherwise the Kentico content tree would be gigantic.

You have to use api: * Working with attachments in MVC. * Get attachments using portal .

  var quotes = QuoteProvider.GetQuotes()
        //.Columns("Name", "Title", "Copy", "Image", "Avatar")
        .Path(parentPath, PathTypeEnum.Children)
        .OrderBy("NodeOrder");

foreach(var quote in quotes){
   // Accesses a property of an attachment stored in Image
   var attachment =  quote.Fields.Image
}

You trying to get multiple for mulitple documents - i guess you need to get multiple attachments do per document.

0 votesVote for this answer Mark as a Correct answer

S1T2 Developer answered on November 7, 2019 09:52

Thanks Peter,
I'm using .Columns() to avoid the noticible performance impact of query, which is also mentioned in the Documentation

"Using Columns method you can restrict the amount of data loaded from the database and speed up the querying process."

Then my question becomes: How to query attachment of the TreeNode obtained from the following code, or what is the right columns reference the attachment?

TreeNode quote = DocumentHelper.GetDocuments()
    .Columns("Text", "Image")
    .Path("/", PathTypeEnum.Children)
    .NestingLevel(1)
    .FirstOrDefault();

Tried DocumentHelper.GetAttachments(quote, false); with a "Node site not found" exception.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on November 7, 2019 13:02

Good question, in database I think there are only a couple fields that link an attachment to a document.

select AttachmentSiteID, AttachmentDocumentID,*  from CMS_Attachment

The problem is Kentico has a few attachment types. I dont know which one you have use. I assume you have unsorted document attachments. So at least you need .Columns("DocumentId", "NodeSiteId", ....) so it can find match in CMS_Attachment. Do you have a field in your quote type that links to attachment? What form control you are using there?

1 votesVote for this answer Mark as a Correct answer