locked
Querying by subcollection properties RRS feed

  • Question

  • I'm trying to order a list of "parent" items based on a value in its sub-collection's sub-collection. Here's the specifics...

    I have a Film entity (mapped to the Films table) that has a one-to-many collection of Release entities (mapped to the Releases table). Each Release has one or more ReleaseDate entities (mapped to the ReleaseDates table).

    public class Film {

        public int Id {get;set;}

        public string Title {get;set;}

    }

    public class Release {

        public int Id {get;set;}

        public int FilmId {get;set;}

        public virtual Film Film {get;set;}

        public ICollection<ReleaseDate> ReleaseDates { get; set; }

    }

    public class ReleaseDate {

        public int Id {get;set;}

        public DateTime Date {get;set;}

        public int ReleaseId {get;set;}

        public virtual Release Release {get;set;}

    }

    Now, I want to order the Films by the earliest release date, but obviously a film could have no releases, and a release could have no release dates (again, 1-* relationships). The SQL equivalent would be...

    SELECT * /* or whatever columns...*/
    FROM dbo.Films F
    LEFT OUTER JOIN dbo.Releases R ON R.FilmId = F.Id
    LEFT OUTER JOIN dbo.ReleaseDates RD ON RD.ReleaseId = R.Id
    ORDER BY RD.[Date] ASC /* or DESC */

    How can I go about doing this?

    Thanks, Henry.

    Tuesday, August 13, 2013 10:13 PM

Answers

  • Hello HenryCC14,

    Welcome toMSDN Support Forum.

    From your description, I notice the issue you are experiencing is that you want to know to search date by EF.

    If I have misunderstand you, please let me know.

    Since I do not know which model do you choice.

    DB-First, Model-First or Code-First?

    So I made a sample using DB-First and please see the sample code below:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    namespace EFAPP
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                DateTime dt = new DateTime(2013, 08, 14);
                using (DBForWeek3Entities3 db = new DBForWeek3Entities3())
                {
                    var ds = (from f in db.Films
                              join r in db.Releases on f.FilmID equals r.FilmID into f_join_r
                              from fr in f_join_r
                              join rd in db.ReleaseDates on fr.ReleaseId equals rd.ReleaseID into fr_join_rd
                              from frrd in fr_join_rd
                              orderby frrd.Date descending
                              select new { f.FilmID, f.Title, fr.ReleaseId, frrd.Date }).ToList();
                    this.dataGridView1.DataSource = ds;
                }
            }
        }
    }

    My Table structure:

    The Result:

    If this does not work for you, could you please share your table structure, app codes so that we can help you better.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Fred Bao Monday, August 19, 2013 10:03 AM
    Wednesday, August 14, 2013 9:45 AM

All replies

  • Hello HenryCC14,

    Welcome toMSDN Support Forum.

    From your description, I notice the issue you are experiencing is that you want to know to search date by EF.

    If I have misunderstand you, please let me know.

    Since I do not know which model do you choice.

    DB-First, Model-First or Code-First?

    So I made a sample using DB-First and please see the sample code below:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    namespace EFAPP
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                DateTime dt = new DateTime(2013, 08, 14);
                using (DBForWeek3Entities3 db = new DBForWeek3Entities3())
                {
                    var ds = (from f in db.Films
                              join r in db.Releases on f.FilmID equals r.FilmID into f_join_r
                              from fr in f_join_r
                              join rd in db.ReleaseDates on fr.ReleaseId equals rd.ReleaseID into fr_join_rd
                              from frrd in fr_join_rd
                              orderby frrd.Date descending
                              select new { f.FilmID, f.Title, fr.ReleaseId, frrd.Date }).ToList();
                    this.dataGridView1.DataSource = ds;
                }
            }
        }
    }

    My Table structure:

    The Result:

    If this does not work for you, could you please share your table structure, app codes so that we can help you better.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Fred Bao Monday, August 19, 2013 10:03 AM
    Wednesday, August 14, 2013 9:45 AM
  • Try this:

    var results = (from f in db.Films
             join r in db.Releases on r.FilmId equals f.Id
             join rd in db.ReleaseDates on rd.ReleaseId equals r.Id
             select new { f.Id, f.Title, rd.ReleaseDate })
             .OrderBy(a => a.ReleaseDate)
             //.OrderByDescending(a => a.ReleaseDate)
             .ToList();
    Wednesday, August 14, 2013 9:57 AM
  • This is basically what I've ended up doing and pushing the logic down to the repository layer. I think what I should have been asking was how to do this with LINQ to Entities, not LINQ to SQL.

    Thanks for both of your responses.

    Wednesday, August 21, 2013 2:54 PM