locked
Trying to Order by navigation property EF6, config issue or syntax? RRS feed

  • Question

  • User865464427 posted

    I have two related tables with relationships set up in SSMS and the edmx appears to have mapped the relationship correctly in EF.

    I am trying to perform this query:

    SELECT x.*
      FROM [stImages] x inner join lnkProductImage y on x.intID = y.intImageID order by y.intSequence

    In EF6/Linq I've tried to do this:

    var lst = db.stImages.Where(s => s.lnkProductImages.Any(x => x.intProductID == id)).OrderBy(o => o.lnkProductImages.intSequence).ToList();

    However o.lnkProductImages.intSequence can't actually be accessed. Intellisense finds the o.lnkProductImages navigation property of stImages but won't go any further. Have I configured something wrong or is there something wrong with my query syntax?

    For consistency I'd like to stick with Method syntax if possible.

    Monday, March 11, 2019 10:31 AM

Answers

  • User865464427 posted

    Thank you both for giving your time to look at this.

    I have eventually solved the issue with a nested query as @mgebhard suggested.

     List<stImage> lstImages = db.stImages.Where(s => s.lnkProductImages.Any(x => x.intProductID == id)).OrderBy(x => x.lnkProductImages.Select(y => y.intSequence).Max()).ToList();

    So to hopefully help others the above query returns results equivalent to:

    SELECT x.*
      FROM [stImages] x inner join lnkProductImage y on x.intID = y.intImageID order by y.intSequence

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 13, 2019 10:02 AM

All replies

  • User475983607 posted

    It is impossible to answer this question because you did not show us the object model.  Most likely the issue has to do with understanding collection syntax .  The following is a collection of stImages.  The collection does not have a lnkProdcutImages property. 

    db.stImages.Where(s => s.lnkProductImages.Any(x => x.intProductID == id))

    The first step is selecting an item from the collection.  That will allow you to drill into the properties of that item. 

    db.stImages.Where(s => s.lnkProductImages.Any(x => x.intProductID == id)).FirstOrDefault();

    If you are trying to order many navigation properties that exist in many stImages, then a nested query is required.

    Monday, March 11, 2019 12:50 PM
  • User865464427 posted

    Sorry, here are the models as auto generated by VS. It is a Database First project which is why I was wondering if there was anything I needed to manually configure in the EDMX or override in the Model classes. Or do I need to use Include somehow to return both the stImage data and the lnkProductImages data and then sort the list of entities after they've been returned? Many thanks for your help so far.

    stImages:

    namespace proj.Models
    {
        using System;
        using System.Collections.Generic;
        
        public partial class stImage
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public stImage()
            {
                this.stTopicImages = new HashSet<stTopicImage>();
                this.lnkProductImages = new HashSet<lnkProductImage>();
            }
        
            public int intID { get; set; }
            public string strFilename { get; set; }
            public string strFriendlyName { get; set; }
            public Nullable<int> intWidth { get; set; }
            public Nullable<int> intHeight { get; set; }
            public bool booIsHeader { get; set; }
            public Nullable<double> dblRatio { get; set; }
            public string strType { get; set; }
            public string strCategory { get; set; }
            public Nullable<int> intCategoryID { get; set; }
            public Nullable<int> intMediaTypeID { get; set; }
            public System.DateTime datUploaded { get; set; }
        
            public virtual luImageCategory luImageCategory { get; set; }
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<stTopicImage> stTopicImages { get; set; }
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<lnkProductImage> lnkProductImages { get; set; }
        }
    }

    lnkProductImages:

    namespace proj.Models
    {
        using System;
        using System.Collections.Generic;
        
        public partial class lnkProductImage
        {
            public int intID { get; set; }
            public int intProductID { get; set; }
            public int intImageID { get; set; }
            public string strAdditionalData { get; set; }
            public int intSequence { get; set; }
        
            public virtual dyProduct dyProduct { get; set; }
            public virtual stImage stImage { get; set; }
        }
    }

    Monday, March 11, 2019 1:40 PM
  • User1520731567 posted

    Hi withag,

    As I can see in your code,

    public virtual ICollection<lnkProductImage> lnkProductImages { get; set; }

    lnkProductImages is ICollection<> type.(with multiple lines of records)

    So,Intellisense can't find o.lnkProductImages.intSequence,because it can't know which line of intSequence do you want.

    As @mgebhard said,you could use .Any() to further query the data.

    Best Regards.

    Yuki Tao

    Tuesday, March 12, 2019 6:45 AM
  • User865464427 posted

    Thank you both for giving your time to look at this.

    I have eventually solved the issue with a nested query as @mgebhard suggested.

     List<stImage> lstImages = db.stImages.Where(s => s.lnkProductImages.Any(x => x.intProductID == id)).OrderBy(x => x.lnkProductImages.Select(y => y.intSequence).Max()).ToList();

    So to hopefully help others the above query returns results equivalent to:

    SELECT x.*
      FROM [stImages] x inner join lnkProductImage y on x.intID = y.intImageID order by y.intSequence

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 13, 2019 10:02 AM