locked
LINQ Join select *

    Question

  • Hello

    I wonder if it's possible to translate the next join query into LINQ:

    select a.*, b.*
    from a inner join b on a.id = b.id

    Thanks
    Wednesday, March 18, 2009 4:04 PM

Answers

  • The truth is LINQ never selects *.

    Doing this is bad practice, in my own experience developers who have left select *'s behind in code have caused major problems. Say one day another developer is hired to create a new website that uses your table, he decides to add an image column for customer photographs. What happens next your application is rendered useless due to the massive delays the select * is suffering due to it selecting an image column it doesnt even use.

    LINQ never does select *. This is an illusion when you do a:
    var query = from table in db.SomeTable
                       select table;

    It does a select table.prop1, table.prop2, based on the generated type for SomeTable. Since it knows the table layout and has generated the corresponding properties you can write code which appears similar to select *. But LINQ will convert that into a SQL statement like:

    SELECT "prop1", "prop2" FROM SomeTable;

    The LINQ query above is simply eye candy.

    When you do a join the remaining properties are not part of any type generated by LINQ. If you want to select them all you will need to implement your own methods perhaps using reflection to achieve what you are asking.
    Thursday, March 19, 2009 1:03 AM
  •  

    Hello,

    Welcome to MSDN Forums!

    Thanks to Allan for your enlightening ideas.   You have given us a wonderful detailed analysis for this question.  

    FaithRaven, if you still want to realize the dynamic query “SELECT *” in LINQ, you can refer to the following code snippet.  I create a Person class and a Course class to hold the data.  Then I join the data in List<Person> and List<Course> and create an anonymous type class which store a Person object and a Course object.  Finally, to retrieve the inside properties of the objects, I use the .NET Reflection. 

            private void button1_Click(object sender, EventArgs e)

            {

                List<Person> a = new List<Person>() { new Person() { pID = 1, pName = "Michael" }, new Person() { pID = 2, pName = "Lingzhi" } };

                List<Course> b = new List<Course>() { new Course() { cID = 1, cName = "Math"}, new Course() { cID = 2, cName = "English"}};

     

                var query = a.Join(b, p => p.pID, c => c.cID, (p, c) => new { Person = p, Course = c });

     

                foreach (var q in query)

                {

                    foreach (var pi in q.Person.GetType().GetProperties())

                    {

                        MessageBox.Show(pi.Name + " = " + pi.GetValue(q.Person, null));

                    }

     

                    foreach (var pi in q.Course.GetType().GetProperties())

                    {

                        MessageBox.Show(pi.Name + " = " + pi.GetValue(q.Course, null));

                    }

     

                }

                   

            }

     

       =========================

      

        public class Person

        {

            public int pID { get; set; }

            public string pName { get; set; }

        }

     

        public class Course

        {

            public int cID { get; set; }

            public string cName { get; set; }

        }

     

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 23, 2009 6:46 AM
    Moderator

All replies

  • The truth is LINQ never selects *.

    Doing this is bad practice, in my own experience developers who have left select *'s behind in code have caused major problems. Say one day another developer is hired to create a new website that uses your table, he decides to add an image column for customer photographs. What happens next your application is rendered useless due to the massive delays the select * is suffering due to it selecting an image column it doesnt even use.

    LINQ never does select *. This is an illusion when you do a:
    var query = from table in db.SomeTable
                       select table;

    It does a select table.prop1, table.prop2, based on the generated type for SomeTable. Since it knows the table layout and has generated the corresponding properties you can write code which appears similar to select *. But LINQ will convert that into a SQL statement like:

    SELECT "prop1", "prop2" FROM SomeTable;

    The LINQ query above is simply eye candy.

    When you do a join the remaining properties are not part of any type generated by LINQ. If you want to select them all you will need to implement your own methods perhaps using reflection to achieve what you are asking.
    Thursday, March 19, 2009 1:03 AM
  • Hello Allan

    Thanks for your help but this efficiency discussion doesn't help me much.

    I need Select * because I have no other option. The dynamic selects that happens in the dynamic tables I use requires it and I'm stuck to it.
    Thursday, March 19, 2009 9:44 AM
  •  

    Hello,

    Welcome to MSDN Forums!

    Thanks to Allan for your enlightening ideas.   You have given us a wonderful detailed analysis for this question.  

    FaithRaven, if you still want to realize the dynamic query “SELECT *” in LINQ, you can refer to the following code snippet.  I create a Person class and a Course class to hold the data.  Then I join the data in List<Person> and List<Course> and create an anonymous type class which store a Person object and a Course object.  Finally, to retrieve the inside properties of the objects, I use the .NET Reflection. 

            private void button1_Click(object sender, EventArgs e)

            {

                List<Person> a = new List<Person>() { new Person() { pID = 1, pName = "Michael" }, new Person() { pID = 2, pName = "Lingzhi" } };

                List<Course> b = new List<Course>() { new Course() { cID = 1, cName = "Math"}, new Course() { cID = 2, cName = "English"}};

     

                var query = a.Join(b, p => p.pID, c => c.cID, (p, c) => new { Person = p, Course = c });

     

                foreach (var q in query)

                {

                    foreach (var pi in q.Person.GetType().GetProperties())

                    {

                        MessageBox.Show(pi.Name + " = " + pi.GetValue(q.Person, null));

                    }

     

                    foreach (var pi in q.Course.GetType().GetProperties())

                    {

                        MessageBox.Show(pi.Name + " = " + pi.GetValue(q.Course, null));

                    }

     

                }

                   

            }

     

       =========================

      

        public class Person

        {

            public int pID { get; set; }

            public string pName { get; set; }

        }

     

        public class Course

        {

            public int cID { get; set; }

            public string cName { get; set; }

        }

     

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 23, 2009 6:46 AM
    Moderator
  • FaithRaven said:


    I need Select * because I have no other option. The dynamic selects that happens in the dynamic tables I use requires it and I'm stuck to it.



    Can you elaborate more on what you mean by dynamic tables? Is your table layout not fixed, i.e. you need runtime constructed entity classes?
    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Monday, March 23, 2009 7:19 AM