Trying to Order by navigation property EF6, config issue or syntax? [Answered]RSS

• • Reply
  withag

  Member

  6 Points

  57 Posts

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

  Mar 11, 2019 10:31 AM|withag|LINK

  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.

• • Reply
  mgebhard

  All-Star

  48321 Points

  21260 Posts

  Re: Trying to Order by navigation property EF6, config issue or syntax?

  Mar 11, 2019 12:50 PM|mgebhard|LINK

  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.

• • Reply
  withag

  Member

  6 Points

  57 Posts

  Re: Trying to Order by navigation property EF6, config issue or syntax?

  Mar 11, 2019 01:40 PM|withag|LINK

  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; }
      }
  }

• • Reply
  Yuki Tao

  Contributor

  3690 Points

  1431 Posts

  Re: Trying to Order by navigation property EF6, config issue or syntax?

  Mar 12, 2019 06:45 AM|Yuki Tao|LINK

  Hi withag,

  As I can see in your code,

  withag

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  withag

  Member

  6 Points

  57 Posts

  Re: Trying to Order by navigation property EF6, config issue or syntax?

  Mar 13, 2019 10:02 AM|withag|LINK

  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