What's the correct way to fill a DropDownList using LINQ?

Question

I know this question might be duplicate but I have a look at tons of examples but none of them suit my situation.

I have a DropDownList inside a DataList, I need to populate DropDownList with data from the DB. I know how to find the the dropdown control in the DataList and I know how to populate the dropdown using SqlCommand. Now I'm trying to learn LINQ but I'm unable to fill the dropdown. Please see my scenarios below:

        //Scenario 1
        var ddquery = from dd in db.PRODUCTs select dd.pr_product.Distinct();

        product.DataSource = ddquery;                
        product.DataTextField = "pr_product";
        product.DataValueField = "pr_product";
        product.DataBind();

In Scenario 1 I get error: "Sequence operators not supported for type 'System.String'."

        //Scenario 2
        var ddproc = from dd in db.isp_GETDDL("PRODUCTS", "", "") select dd;

        product.DataSource = ddproc;
        product.DataTextField = "pr_product";
        product.DataValueField = "pr_product";
        product.DataBind();

In Scenario 2 the I get error: "DataBinding: 'isp_GETDDLResult' does not contain a property with the name 'pr_product'." In this scenario I'm not really if I'm doing it right since the PROC returns a set of data but I'm not sure if I'm handling correctly.

        //Scenario 3
        var ddq = from dd in db.PRODUCTs select dd;

        product.DataSource = ddq;
        product.DataTextField = "pr_product";
        product.DataValueField = "pr_product";
        product.DataBind();

In Scenario 3, I get the same error as in 2 but on this one I'm not using a PROC to get the data.

Any help will be appreciated.

Answer 1

Looks like your Distinct call should be on query itself, not on one value within results:

var ddquery = (from dd in db.PRODUCTs select dd.pr_product).Distinct().ToList();

And what more important, you don't have to specify DataTextField and DataValueField, because your DataSource is List<string>:

product.DataSource = ddquery;                
product.DataBind();