none
Querying multiple tables with L2E RRS feed

  • Question

  • I have seen a couple ways of doing this.  I wanted to use .Include, but it is not going as planned.  I think it may have something to do with the navigation properties in .edmx file.  I have 6 tables I am putting together in a query.  I have one case where I need to get a value from a table that is 4 tables away from my 'core' table.  The purpose of all this is to populate some embedded reports in an MVC project.

    My question is, what would be the most efficient way to do this.  Use includes like this:


                var clQuery =   from e in db.Table1.Include("Table2")
                                                     .Include("Table3")
                                                     .Include("Table4")
                                                     .Include("Table5")
                                                     .Include("Table6")
                                select new ReportViewModel
                                {
                                    Col1 = e.Table1.Table5.Value,
                                    Col2 = e.Value2,
                                    Col3 = e.FirstName,
                                    Col4 = e.LastName,
                                    Col5 = e.Table3.Value,
                                    Col6 = e.Items.Count > 0,
                                    Col7 = e.CurrentGrade,
                                    Col8 = e.Table2.Value,
                                    Col9 = e.Table2.Table3.Value,
                                    Col10 = e.Products.Count > 0,
                                    Col11 = e.Table4.Table5.Name,
                                    Col12 = e.Staff.Count > 0,
                                    Col13 = e.Value9,
                                    Col14 = false,
                                    Col15 = false
                                }  

    Or should I do a bunch of joins, like:

    from s in db.table1
        join d in db.table2 on s.id equals d.id
        join e in db.table3 on d.id equals e.id

    Thank you for looking at my post.


    Simon.

    Thursday, September 6, 2012 4:09 PM

Answers

  • Hi Simon,

    The use of the Include methods in a Linq to EF or Linq To SQL is to return all of the associated records, all columns for the returned main records. If all you need is selected values then the below is what you should be doing.

      
    var clQuery =   from e in db.Table1
                    select new ReportViewModel
                    {
                        Col1 = e.Table1.Table5.Value,
                        Col2 = e.Value2,
                        Col3 = e.FirstName,
                        Col4 = e.LastName,
                        Col5 = e.Table3.Value,
                        Col6 = e.Items.Count > 0,
                        Col7 = e.CurrentGrade,
                        Col8 = e.Table2.Value,
                        Col9 = e.Table2.Table3.Value,
                        Col10 = e.Products.Count > 0,
                        Col11 = e.Table4.Table5.Name,
                        Col12 = e.Staff.Count > 0,
                        Col13 = e.Value9,
                        Col14 = false,
                        Col15 = false
                    };


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, September 6, 2012 4:47 PM