Linq Asp.net code optimization

Question

I'm using AdventureWorks as a database to get familiar with linq and asp.net interface and was wondering if anyone could take a look at my code behind in C# and give me a optimized way to get the results populated when searched and then updated when submitted.

protected void btnSearch_Click(object sender, EventArgs e)
    {
        TestDataClassDataContext dc = new TestDataClassDataContext();
        Individual ind = new Individual();
        var q = from Individual in dc.Individuals
                where Individual.CustomerID == Convert.ToInt32(txtCustID.Text)
                select Individual;
        if (q.Count() > 0)
        {
            ind = q.First();
            Contact con = new Contact();
            var q2 = from Contact in dc.Contacts
                     where Contact.ContactID == ind.ContactID
                     select Contact;
            if (q2.Count() > 0)
            {
                con = q2.First();
                txtFname.Text = con.FirstName;
                txtLname.Text = con.LastName;
                txtMname.Text = con.MiddleName;
                txtPhone.Text = con.Phone;
                txtPword.Text = con.PasswordSalt;
                txtSuff.Text = con.Suffix;
                txtTitle.Text = con.Title;
                txtEmail.Text = con.EmailAddress;
                ddlEmailPromo.SelectedValue = con.EmailPromotion.ToString();

            }
        }
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        TestDataClassDataContext dc = new TestDataClassDataContext();
        Individual ind = new Individual();
        var q = from Individual in dc.Individuals
                where Individual.CustomerID == Convert.ToInt32(txtCustID.Text)
                select Individual;
        if (q.Count() > 0)
        {
            ind = q.First();
            Contact con = new Contact();
            var q2 = from Contact in dc.Contacts
                     where Contact.ContactID == ind.ContactID
                     select Contact;
            if (q2.Count() > 0)
            {
                con = q2.First();
                con.FirstName = txtFname.Text;
                con.LastName = txtLname.Text;
                con.MiddleName = txtMname.Text;
                con.Phone = txtPhone.Text;
                con.PasswordSalt = txtPword.Text;
                con.Suffix = txtSuff.Text;
                con.Title = txtTitle.Text;
                con.EmailAddress = txtEmail.Text ;
                ddlEmailPromo.SelectedValue = con.EmailPromotion.ToString();

            }
        }

Answer 1

If you have the relationships setup properly in SQL, and both tables exist on your DBML, the first SQL query should fetch the second part of your query too.

Example:

TestDataClassDataContext dc = new TestDataClassDataContext();
Individual individual = dc.Individuals.Where(a => a.CustomerID == Convert.ToInt32(txtCustID.Text).FirstOrDefault();

if(individual != null)
{
   Contact contact = individual.Contacts.FirstOrDefault();
   if(contact != null)
   {
       txtFname.Text = contact.FirstName;
   }
}

Answer 2

1) First start of an asp.net application always takes more time, than just handling a request.

2) Debugging enabled in web.config alsow slows the application.

3) (Maybe the most significant) Lets look closer to your code.

var q = from Individual in dc.Individuals
                where Individual.CustomerID == Convert.ToInt32(txtCustID.Text)
                select Individual;

q DOES NOT contain any objects - it's just a query. Now, lets count, how many times it is executed:

1.if (q.Count() > 0)

1. ind = q.First();

2. q2.Count() > 0

At least 3 times per request your connection is established, opened, and closed. It takes time. Try to use your resources use sparingly.

Hope this helps, Ilya.

Answer 3

In one query:

TestDataClassDataContext dc = new TestDataClassDataContext();
var con = (from individual in dc.Individuals 
                 join contact in dc.Contacts on individual.ContactID equals contact .ContactID
                 where individual.CustomerID == Convert.ToInt32(txtCustID.Text)
                 select contact).FirstOrDefault();

if(con == null) return;

txtFname.Text = con.FirstName;
txtLname.Text = con.LastName;
txtMname.Text = con.MiddleName;
txtPhone.Text = con.Phone;
txtPword.Text = con.PasswordSalt;
txtSuff.Text = con.Suffix;
txtTitle.Text = con.Title;
txtEmail.Text = con.EmailAddress;
ddlEmailPromo.SelectedValue = con.EmailPromotion.ToString();

Use the same query into the submit changes function!

Answer 4

Pierre-Luc's answer is spot on. It might be worth also mentioning that because the btnSearch_Click method is only reading from the database there is no need to have .net track the ojects for changes as it does for updates or deletes so putting:

dc.ObjectTrackingEnabled = false;

just under the DataContext initialization will tell .net to just open the connection for a read request which can help when trying to optimize your code. More info here: http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

Answer 5

Also note that each call to .Count() will enumerate over the complete result set to return a count.

What you're looking for is the .Any() method, which will run in constant time.