none
how to get the last order for every customer using LINQ RRS feed

  • Question

  • Hi

    i have Customers and orders tables from northwind database

    in the dataset the tables are related parent->child

    i want to get the last order for every customer and show them using linq

    im new to linq but i read about GetChildRows() function and Last() function but i coundn't use them

    any help

    thanks in advance.

     


    nothing beat the human brain
    • Moved by Alex LiangModerator Tuesday, May 11, 2010 10:44 AM Move to a more related forum (From:ADO.NET DataSet)
    Saturday, May 8, 2010 3:51 AM

Answers

  • Hi seco,

    Then you can make it on DataSet like this:

    DataSet ds = new DataSet();        
    
    ds.Tables.Add("Orders");
    ds.Tables.Add("Customers");
    
    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from Customers; Select * from Orders";
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL ,strConn);
    da.TableMappings.Add("Table","Customers");
    da.TableMappings.Add("Table1", "Orders");
    
    da.Fill(ds);      
    
    DataRelation rel = ds.Relations.Add("Customers_Orders", 
                      ds.Tables["Customers"].Columns["CustomerID"],
                      ds.Tables["Orders"].Columns["CustomerID"]);      
    
    var results = from p in ds.Tables["Customers"].AsEnumerable()
           //where p.GetChildRows(rel).Count() > 0
           select p.GetChildRows(rel).LastOrDefault();
    
    
    DataTable dt = results.CopyToDataTable<DataRow>();
    
    this.dataGridView2.DataSource = dt;
    

    Best regards,
    Alex Liang


    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.
    Thursday, May 13, 2010 6:29 AM
    Moderator
  • Hi seco,

    Please refer to the following code:

    DataSet ds = new DataSet();        
    
    ds.Tables.Add("Orders");
    ds.Tables.Add("Customers");
    
    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from Customers; Select * from Orders";
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL ,strConn);
    da.TableMappings.Add("Table","Customers");
    da.TableMappings.Add("Table1", "Orders");
    
    da.Fill(ds);      
    
    DataRelation rel = ds.Relations.Add("Customers_Orders", 
                      ds.Tables["Customers"].Columns["CustomerID"],
                      ds.Tables["Orders"].Columns["CustomerID"]);
    
    var results = from p in ds.Tables["Customers"].AsEnumerable()
           where p.GetChildRows(rel).Count() > 0
           select new 
           {
             CustomerName = p["CustomerID"], //Customer Name
             USA_Order = p.GetChildRows(rel).Count(u => u["ShipCountry"].ToString() == "USA"), // how may order that has country USA
             Total_Order = p.GetChildRows(rel).Count(), // how many total orders               
           };
    
          
    this.dataGridView2.DataSource = results.ToList ();
    

    Best regards,
    Alex Liang


    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.
    • Marked as answer by seco Monday, May 17, 2010 7:08 AM
    Monday, May 17, 2010 3:18 AM
    Moderator

All replies

  • Hi seco,

    I think you'd better ask this question in LINQ TO SQL forum, but do not worry, I will move the thread to that forum for you.

    As for your question, please try this:

    NorthwindDataContext db = new NorthwindDataContext ();
    var results = from p in db.Customers
           where p.Orders.Count > 0
           select p.Orders[p.Orders .Count - 1];
    
    this.dataGridView1.DataSource = results.ToList();
    

    Best regards,
    Alex Liang


    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.
    Tuesday, May 11, 2010 10:42 AM
    Moderator
  • thanks for reply

    i cannot find the child table in my code p.orders like above because im using dataset that contains the tables not O/R M like your code

    any idea how to make this code work over dataset

    thanks in advance.

     


    nothing beat the human brain
    Thursday, May 13, 2010 4:13 AM
  • Hi seco,

    Then you can make it on DataSet like this:

    DataSet ds = new DataSet();        
    
    ds.Tables.Add("Orders");
    ds.Tables.Add("Customers");
    
    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from Customers; Select * from Orders";
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL ,strConn);
    da.TableMappings.Add("Table","Customers");
    da.TableMappings.Add("Table1", "Orders");
    
    da.Fill(ds);      
    
    DataRelation rel = ds.Relations.Add("Customers_Orders", 
                      ds.Tables["Customers"].Columns["CustomerID"],
                      ds.Tables["Orders"].Columns["CustomerID"]);      
    
    var results = from p in ds.Tables["Customers"].AsEnumerable()
           //where p.GetChildRows(rel).Count() > 0
           select p.GetChildRows(rel).LastOrDefault();
    
    
    DataTable dt = results.CopyToDataTable<DataRow>();
    
    this.dataGridView2.DataSource = dt;
    

    Best regards,
    Alex Liang


    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.
    Thursday, May 13, 2010 6:29 AM
    Moderator
  • it works 

    thanks

    but if i want to get only a value from a specific column how? like order id only

    i try to get item and put it in string but i can't !!

     

     


    nothing beat the human brain
    Thursday, May 13, 2010 10:24 AM
  • Hi seco,

    Then you can just retrieve the OrderID column value like :

    DataSet ds = new DataSet();        
    
    ds.Tables.Add("Orders");
    ds.Tables.Add("Customers");
    
    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from Customers; Select * from Orders";
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL ,strConn);
    da.TableMappings.Add("Table","Customers");
    da.TableMappings.Add("Table1", "Orders");
    
    da.Fill(ds);      
    
    DataRelation rel = ds.Relations.Add("Customers_Orders", 
                      ds.Tables["Customers"].Columns["CustomerID"],
                      ds.Tables["Orders"].Columns["CustomerID"]);
    
    var results = from p in ds.Tables["Customers"].AsEnumerable()
           where p.GetChildRows(rel).Count() > 0
           select new 
           {
             OrderID = p.GetChildRows(rel).LastOrDefault()["OrderId"]
           };
    
          
    this.dataGridView2.DataSource = results.ToList ();
    

    Best regards,
    Alex Liang


    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.
    Thursday, May 13, 2010 11:27 AM
    Moderator
  • thanks for reply

    ok one last question

    i want to get customer name from customers table and how may order that has country USA and how many total orders from orders table and that for all customers

    thanks in advance.

     


    nothing beat the human brain
    Saturday, May 15, 2010 3:47 AM
  • Hi seco,

    Please refer to the following code:

    DataSet ds = new DataSet();        
    
    ds.Tables.Add("Orders");
    ds.Tables.Add("Customers");
    
    string strConn = "Data Source = .\\SQLExpress; Initial Catalog = Northwind; Integrated Security = True";
    string strSQL = "Select * from Customers; Select * from Orders";
    
    SqlDataAdapter da = new SqlDataAdapter(strSQL ,strConn);
    da.TableMappings.Add("Table","Customers");
    da.TableMappings.Add("Table1", "Orders");
    
    da.Fill(ds);      
    
    DataRelation rel = ds.Relations.Add("Customers_Orders", 
                      ds.Tables["Customers"].Columns["CustomerID"],
                      ds.Tables["Orders"].Columns["CustomerID"]);
    
    var results = from p in ds.Tables["Customers"].AsEnumerable()
           where p.GetChildRows(rel).Count() > 0
           select new 
           {
             CustomerName = p["CustomerID"], //Customer Name
             USA_Order = p.GetChildRows(rel).Count(u => u["ShipCountry"].ToString() == "USA"), // how may order that has country USA
             Total_Order = p.GetChildRows(rel).Count(), // how many total orders               
           };
    
          
    this.dataGridView2.DataSource = results.ToList ();
    

    Best regards,
    Alex Liang


    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.
    • Marked as answer by seco Monday, May 17, 2010 7:08 AM
    Monday, May 17, 2010 3:18 AM
    Moderator
  • thanks for reply

    ok it works but when i add some other expressions to the the result query it gives me this exception

    Object reference not set to an instance of an object.

    any idea how to avoid it and view result

    thanks in advance.



    nothing beat the human brain
    Monday, May 17, 2010 5:07 AM
  • Hi seco,

    How did you add these other expressions, could you please show your code ? By the way, you can view query results like this:

    var results = from p in ds.Tables["Customers"].AsEnumerable()
           where p.GetChildRows(rel).Count() > 0
           select new 
           {
             CustomerName = p["CustomerID"], //Customer Name
             USA_Order = p.GetChildRows(rel).Count(u => u["ShipCountry"].ToString() == "USA"), // how may order that has country USA
             Total_Order = p.GetChildRows(rel).Count(), // how many total orders               
           };
    
    foreach (var u in results)
    {
      Console.WriteLine("[Customer Name]: {0}, [USA Orders]: {1}, [Total Orders]: {2}", u.CustomerName, u.USA_Order, u.Total_Order);
    }
    

    Best regards,
    Alex Liang


    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, May 17, 2010 5:54 AM
    Moderator
  • oh sorry i reorder my expressions and it works ok now

    thanks very much for your strong support

    but i found that the order of the columns is diffrent than the expression order i wrote

    1-can i order the output columns as i want ?

    2-if i have 2 query results res1 and res2 and res1 outputs 3 columns and res2 outputs 2 columns and both results from the same table i want to show all five columns in one grid can i do this?

    thank in advance.


    nothing beat the human brain
    Monday, May 17, 2010 7:08 AM
  • Hi seco,

    This issue has been fixed in VS2010, but you can use DataGridView Column's DisplayIndex property to specify the display position of the columns. For example:

    var results = from p in ds.Tables["Customers"].AsEnumerable()
           where p.GetChildRows(rel).Count() > 0
           select new 
           {
             CustomerName = p["CustomerID"], //Customer Name
             USA_Order = p.GetChildRows(rel).Count(u => u["ShipCountry"].ToString() == "USA"), // how may order that has country USA 
             Total_Order = p.GetChildRows(rel).Count(), // how many total orders  
           };
    
             
    
    foreach (var u in results)
    {
      Console.WriteLine("[Customer Name]: {0}, [USA Orders]: {1}, [Total Orders]: {2}", u.CustomerName, u.USA_Order, u.Total_Order);
    }
          
    
    this.dataGridView2.DataSource = results.ToList ();
    
    dataGridView2.Columns["Total_Order"].DisplayIndex = 0;
    dataGridView2.Columns["CustomerName"].DisplayIndex = 1;
    dataGridView2.Columns["USA_Order"].DisplayIndex = 2;
    

    Best regards,
    Alex Liang


    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, May 17, 2010 7:48 AM
    Moderator
  • thanks

    ok

    if i have 2 query results res1 and res2 and res1 outputs 3 columns and res2 outputs 2 columns and both results from the same table i want to show all five columns in one grid can i do this?


    nothing beat the human brain
    Monday, May 17, 2010 10:05 AM
  • Hi seco,

    Since this thread is already closed, so please open a new thread for new question and you will get better support for that new question because more community members will focus on it. And it is our forum's policy to keep one thread one question. Thank you.

    Best regards,
    Alex Liang


    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, May 17, 2010 10:24 AM
    Moderator