Can not bind distinct to drop down control

Question

My application is showing this error below:

Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery) is not supported

Due to the fact that I am trying to bind my drop down control to this LINQ statement. Check below for my code;

 using (AdventureWorksEntities dw = new AdventureWorksEntities())
        {
            ddlCon.DataSource = (from em in dw.Employees
                       select new { em.Title, em.EmployeeID }).Distinct().OrderBy(name => name);
            ;


            ddlCon.DataTextField = "Title";
            ddlCon.DataValueField = "EmployeeID";
            ddlCon.DataBind();
        }

NB: Please explain well in your answers why this error and the solution to it.

Answer 1

Just like in your other question, you need to use ToList(), as you can't bind directly to a query result.

That being said, I also suspect your OrderBy is incorrect. This should likely be:

 ddlCon.DataSource = (from em in dw.Employees
                      select new { em.Title, em.EmployeeID })
                    .Distinct()
                    .OrderBy(emp => emp.Title)  // Pick Title or EmployeeID here
                    .ToList();

Answer 2

You need to execute your query and bring results from database to memory. Only objects in memory could be used as data source. This could be achieved by applying ToList to your query:

ddlCon.DataSource = (from em in dw.Employees
                     select new { em.Title, em.EmployeeID })
                    .Distinct()
                    .OrderBy(x => x.Title)
                    .ToList();