Ling to SQL + filter depending on drop down

Izi · 22 Apr 2009 at 18:33

I have a populated list of companies and projects. I want to be able to filter by company depending on the selected company in a drop down list on the page. By default all companies should show.

current code:

PHP:

protected void Page_Load(object sender, EventArgs e)
    {

        StringBuilder sb = new StringBuilder();

        ProjectsDataContext db = new ProjectsDataContext();

        
        var companyProjectList = from company in db.Companies
                                 join project in db.Projects on company.CompanyID equals project.ProjectCompanyID
                                 join categoryname in db.ProjectCategories on project.ProjectCategoryID equals categoryname.ProjectCategoryID
                                 select new
                                 {
                                     CompanyName = company.CompanyName,
                                     ProjectName = project.ProjectName,
                                     CategoryName = categoryname.ProjectCategoryName,
                                     ProjectID = project.ProjectID,
                                 };
        
        

        foreach (var company in companyProjectList)
        {
            
            sb.Append("<p>" + company.CompanyName + "<p>");
            sb.Append("<p>PROJECT:" + company.ProjectName + "<p>");
            sb.Append("<p>CATEGORY NAME:" + company.CategoryName + "<p>");
            sb.Append("<a href='ProjectEdit.aspx?ProjectID="+company.ProjectID+"'>Click here to edit</a>");

        }

        ProjectsTable.Text = sb.ToString();
        
    }

I know i need a where clause in my select statment, but not sure how to implement this depending on post back value of a drop down list?

Izi · 22 Apr 2009 at 18:34

OOPS ** LINQ, not ling

Haircut · 22 Apr 2009 at 18:39

If you're doing it on postback then you can simply inspect the ValueMember property of the list and add that to the where clause in your linq query.
Assuming that is, that the ValueMember property correspeonds to whatever id it is that you're after in the SQL.

Izi · 22 Apr 2009 at 18:39

ok i got it working by putting a big if clause around the whole lot, but isnt there a better way of doing this:

PHP:

StringBuilder sb = new StringBuilder();

        ProjectsDataContext db = new ProjectsDataContext();

        if (Page.IsPostBack && ddlCompaniesFilter.SelectedValue.ToString() != "Please select")
        {
            var companyProjectList = from company in db.Companies                                     
                                     join project in db.Projects on company.CompanyID equals project.ProjectCompanyID
                                     join categoryname in db.ProjectCategories on project.ProjectCategoryID equals categoryname.ProjectCategoryID
                                     where company.CompanyID == Convert.ToInt32(ddlCompaniesFilter.SelectedValue)
                                     select new
                                     {
                                         CompanyName = company.CompanyName,
                                         ProjectName = project.ProjectName,
                                         CategoryName = categoryname.ProjectCategoryName,
                                         ProjectID = project.ProjectID,
                                     };
            foreach (var company in companyProjectList)
            {

                sb.Append("<p>" + company.CompanyName + "<p>");
                sb.Append("<p>PROJECT:" + company.ProjectName + "<p>");
                sb.Append("<p>CATEGORY NAME:" + company.CategoryName + "<p>");
                sb.Append("<a href='ProjectEdit.aspx?ProjectID=" + company.ProjectID + "'>Click here to edit</a>");

            }
        }
        else
        {
            var companyProjectList = from company in db.Companies
                                     join project in db.Projects on company.CompanyID equals project.ProjectCompanyID
                                     join categoryname in db.ProjectCategories on project.ProjectCategoryID equals categoryname.ProjectCategoryID
                                     select new
                                     {
                                         CompanyName = company.CompanyName,
                                         ProjectName = project.ProjectName,
                                         CategoryName = categoryname.ProjectCategoryName,
                                         ProjectID = project.ProjectID,
                                     };
            foreach (var company in companyProjectList)
            {

                sb.Append("<p>" + company.CompanyName + "<p>");
                sb.Append("<p>PROJECT:" + company.ProjectName + "<p>");
                sb.Append("<p>CATEGORY NAME:" + company.CategoryName + "<p>");
                sb.Append("<a href='ProjectEdit.aspx?ProjectID=" + company.ProjectID + "'>Click here to edit</a>");

            }
        }
        

        

        ProjectsTable.Text = sb.ToString();

Haircut · 22 Apr 2009 at 18:45

Ah, I see what you mean now.
Could you still not replace the where clause with something along the lines of:

Code:

where company.CompanyID == Convert.ToInt32(ddlCompaniesFilter.SelectedValue ?? company.CompanyID)

Or replace with a ternary operator if you need to do a bit more than a simple null check.

Izi · 22 Apr 2009 at 18:45

Compiler Error Message: CS0019: Operator '??' cannot be applied to operands of type 'string' and 'int'

hmmf

sorry on a massive learning curve right now never really coded in .net !

what does the ?? Operator actually mean?

Goksly · 22 Apr 2009 at 19:48

Right, quite hard to do in this window as no colour coding in edit mode... but basically just use a lambda expression to filter the results set.

Izi said:

ok i got it working by putting a big if clause around the whole lot, but isnt there a better way of doing this:

PHP:

StringBuilder sb = new StringBuilder();

        ProjectsDataContext db = new ProjectsDataContext();

        var companyProjectList = from company in db.Companies                                     
                                     join project in db.Projects on company.CompanyID equals project.ProjectCompanyID
                                     join categoryname in db.ProjectCategories on project.ProjectCategoryID equals categoryname.ProjectCategoryID
                                     where company.CompanyID == Convert.ToInt32(ddlCompaniesFilter.SelectedValue)
                                     select new
                                     {
                                         CompanyName = company.CompanyName,
                                         ProjectName = project.ProjectName,
                                         CategoryName = categoryname.ProjectCategoryName,
                                         ProjectID = project.ProjectID,
                                     };

 if (Page.IsPostBack && ddlCompaniesFilter.SelectedValue.ToString() != "Please select")
        companyProjectList = companyProjectList.where(company => company.CompanyID == Convert.ToInt32(ddlCompaniesFilter.SelectedValue));


            foreach (var company in companyProjectList)
            {

                sb.Append("<p>" + company.CompanyName + "<p>");
                sb.Append("<p>PROJECT:" + company.ProjectName + "<p>");
                sb.Append("<p>CATEGORY NAME:" + company.CategoryName + "<p>");
                sb.Append("<a href='ProjectEdit.aspx?ProjectID=" + company.ProjectID + "'>Click here to edit</a>");

            }
        }

        

        

        ProjectsTable.Text = sb.ToString();

Goksly · 22 Apr 2009 at 19:49

Izi said:
what does the ?? Operator actually mean?

means if its null, take the other option.

int? megh = null;

int bob = megh ?? 8;

bob will be set to 8 if megh is null.

Izi · 22 Apr 2009 at 22:09

Goksly - THANK YOU SO MUCH! that was doing my nut in.

out of interest, when i was doing some browsing i kept coming accross:

IQueryable, IEnumerator and a few others.

Could you explain these in layman terms, what they represent and when I may need them?

I really am a newbie, so forgive me. I just find MSDN whitepapers on the subject a little to nuch to chew !

I come from a classic asp/vbscript mssql2000 background... (yes i know its taken me longer than it should have to move to .net

)

Goksly · 22 Apr 2009 at 23:26

Firstly I would recommend, if you are new to all this, to start with the basics. I started with the Pro C# Apress books - even though they have 'pro' in, they cover the basics as well so its a good start point.

I've been doing C# for 3 years (but 2.5 of them were .Net 2.0, so no fancy stuff like Linq) and picking up Linq and Lambda expressions were pretty tough to get my head around, so starting with them isn't a good place to start

I've not come across the IQueryable type (i.e. implemented it myself) but seems to be only when you want to do some fairly complex linq stuff - I've only just started delving into Linq so I'm not the best for that one.

IEnumerator is used when you want to make your custom classes able to be used more intelligently. For example, you might have a garage class and you want to use foreach to cycle through the cars in there etc. Linq, I assume, uses object enumerators behind the scenes of some of its functionality.

Izi · 23 Apr 2009 at 08:37

Goksly said:
Firstly I would recommend, if you are new to all this, to start with the basics. I started with the Pro C# Apress books - even though they have 'pro' in, they cover the basics as well so its a good start point.

I've been doing C# for 3 years (but 2.5 of them were .Net 2.0, so no fancy stuff like Linq) and picking up Linq and Lambda expressions were pretty tough to get my head around, so starting with them isn't a good place to start

I've not come across the IQueryable type (i.e. implemented it myself) but seems to be only when you want to do some fairly complex linq stuff - I've only just started delving into Linq so I'm not the best for that one.

IEnumerator is used when you want to make your custom classes able to be used more intelligently. For example, you might have a garage class and you want to use foreach to cycle through the cars in there etc. Linq, I assume, uses object enumerators behind the scenes of some of its functionality.

Thanks for the reply. Funny you should mention that book, its the exact one I just got and its pretty easy read - thats where I kicked of my LINQ understanding from. It covers all the basics well. I shall make sure to read through the rest also.

Cheers for your help.