none
linq with 2 joined table

    Question

  • Since I am new to working with linq in visual studio.net 2010, I have the following questions:

    1. In linq, I would like to know how to join two tables and how to select specific columns from each of the two tables. The two tables would be joined on column values they  both contain. The columns they are joined on are not the primary keys. Both tables must have the same values.

    2. What is a good reference I can use in the future to look up how to write my own linq statements? I want to use linq instead of straight sql since that is the preferred method within the last couple of years.  

    Sunday, November 6, 2011 4:55 AM

Answers

  • Hi midnight_car,

    Using Linq to SQL to query two tables, an example we can use is using the Northwind database.  Let's assume we want to join the Customers and Orders tables together:

    We would write a query like this to retrieve the OrderID, and OrderDate from the Order table, and the CustomerID and ContactName from the Customer table:

     class Program
        {
            static void Main(string[] args)
            {
                using (DataClasses1DataContext db = new DataClasses1DataContext())
                {
    
                    var query_results = from d in db.Customers
                                        join o in db.Orders
                                        on d.CustomerID equals o.CustomerID
                                        select new { o.OrderID, o.OrderDate, d.CustomerID, d.ContactName };
                   
    
                }
            }
      }
    

    If you're using VB, the syntax would be almost identical.  You could now use query_results as a DataSource for a DataGridView and in other controls.

    Some resources to learn Linq to SQL are:

    http://msdn.microsoft.com/en-us/library/bb386976.aspx

    Here is a nice "cheat sheet" to have handy when you're learning linq to sql:

    http://damieng.com/blog/2009/08/12/linq-to-sql-cheat-sheet

     

    Also note that you should also try to learn Linq to Entities (Entity Framework).  It's going to be very similiar to Linq to SQL but more complex due to the extra complexity and features of Entity Framework.  But if you build a base of learning Linq to SQL moving to Linq to Entities will be fairly easy.

     


    Tom Overton
    Sunday, November 6, 2011 1:18 PM

All replies

  • Hi midnight_car,

    Using Linq to SQL to query two tables, an example we can use is using the Northwind database.  Let's assume we want to join the Customers and Orders tables together:

    We would write a query like this to retrieve the OrderID, and OrderDate from the Order table, and the CustomerID and ContactName from the Customer table:

     class Program
        {
            static void Main(string[] args)
            {
                using (DataClasses1DataContext db = new DataClasses1DataContext())
                {
    
                    var query_results = from d in db.Customers
                                        join o in db.Orders
                                        on d.CustomerID equals o.CustomerID
                                        select new { o.OrderID, o.OrderDate, d.CustomerID, d.ContactName };
                   
    
                }
            }
      }
    

    If you're using VB, the syntax would be almost identical.  You could now use query_results as a DataSource for a DataGridView and in other controls.

    Some resources to learn Linq to SQL are:

    http://msdn.microsoft.com/en-us/library/bb386976.aspx

    Here is a nice "cheat sheet" to have handy when you're learning linq to sql:

    http://damieng.com/blog/2009/08/12/linq-to-sql-cheat-sheet

     

    Also note that you should also try to learn Linq to Entities (Entity Framework).  It's going to be very similiar to Linq to SQL but more complex due to the extra complexity and features of Entity Framework.  But if you build a base of learning Linq to SQL moving to Linq to Entities will be fairly easy.

     


    Tom Overton
    Sunday, November 6, 2011 1:18 PM
  • Thank you very much especially for the cheat sheet! Do you have the cheat sheet for C# 2010 also? If so, can you tell me where the url is? If not, I know VB well enough to translate it to C#.
    Monday, November 7, 2011 2:20 AM