none
LINQ query help. RRS feed

  • Question

  • I am new to LINQ and have no idea what I'm doing. I have the following SQL query and I need to change it to LINQ. Any help would be much appreciated. I also wasn't sure if connecting to the database was the same in LINQ so I just added my connection information as well. Thanks!

    *CODE*

    SqlConnection myConnection = new SqlConnection("Trusted_Connection=yes;" +

                                           "database=Northwind; " +

                                           "connection timeout=30");

     

                myConnection.Open();

     

                SqlDataReader myReader = null;

                SqlCommand myCommand = new SqlCommand("SELECT Customers.ContactName, COUNT(Orders.OrderID) AS NumberOfOrders      FROM Northwind.dbo.Customers INNER JOIN Northwind.dbo.Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.ContactName", myConnection);

     

                myReader = myCommand.ExecuteReader();

     

                while (myReader.Read())

                {

                    lstbxName.Items.Add((myReader["ContactName"].ToString() + " has " + myReader["NumberOfOrders"].ToString() + " orders."));

                }

     

                using (StreamWriter myWriter = new StreamWriter("C:/<Folder>"))

                {

                    for (int i = 0; i < lstbxName.Items.Count; i++)

                    {

                        myWriter.Write(lstbxName.Items[i] + "\r\n");

                    }

                }

     

                myConnection.Close();

    Thursday, February 17, 2011 4:36 PM

Answers

  • "SELECT Customers.ContactName, COUNT(Orders.OrderID) AS NumberOfOrders     

    FROM Northwind.dbo.Customers

    INNER JOIN Northwind.dbo.Orders

    ON Customers.CustomerID = Orders.CustomerID

    GROUP BY Customers.ContactName" 

    Hello jjohnson88,

     

    Welcome to the MSDN Forum and thanks for posting here.

    According to your description, I can help you to transfer the SQL query to LINQ query, as below:

    using(NorthwindDataContext ctx = new NorthwindDataContext())

    {

          var query  = (from c in ctx.Customers

                             join o in ctx.Orders

                             on c.CustomerID = o.CustomerID

                             group by c.ContactName

                             select new{ c.ContactName, COUNT(o.OrderID)}).ToList()

    }

    And if you would like to know more about LINQ to SQL, please read this :

    http://www.codeproject.com/KB/database/LINQtoSQL.aspx

    I hope it can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 18, 2011 5:43 AM
    Moderator