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

Question

Am coding on visual studio 2012 and using Entity Model as my Data layer. However, my drop down control with the Linq statement tend to throw an unhandled exception when the page tries to load (stated title above). Here is my code below;

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

            ddlCon.DataTextField = "Title";
            ddlCon.DataValueField = "EmployeeID";
            ddlCon.DataBind();
            ddlCon.Items.Insert(0, new ListItem("--Select--", "--Select--"));
        }

1. I want to know why that error occurred
2. What should be the proper way to bind to a control when using LINQ?

Villenage

It helps if you post the error as well. – Carlyle

@Carlyle The question title is the error (though that's not incredibly clear) – Kaif

Answer 1

The error is fairly clear - you can't bind directly to the query results, but need to populate some local collection instead.

The simplest way to do this is to convert it to a List<T>, via ToList():

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

Kaif

Thank you... But now another issue the fact that the new LINQ statement is throwing the same error (from em in dw.Employees select new { em.Title, em.EmployeeID }).Distinct().OrderBy(name => name)... Please help me out – Villenage

@user1753728 That looks like a different issue. I'd ask it as a separate question, with details, including the code. (Right off the bat, though, you're ordering by the entire anonymous class, which is probably not quite what you want...) – Kaif

No its not what I want, I just need the unique items from the source. – Villenage

@user1753728 As I said, try posting it in a separate question. There's too many details to put in comments. – Kaif

ok... but how can i tagged you because I believe you've got the answer that I need. Smiles – Villenage

@user1753728 I'll keep an eye out for it - if it's tagged C# and linq, I'll see it ;) – Kaif

its on... check for 'Can not bind distinct to drop down control'. Thanks in advance. – Villenage

@user1753728 That's the same exact problem. You can never bind to a query. – Kaif

Answer 2

Or if you want to avoid writing a LINQ expression you could just do this:

var dbContext = new EF.CustomerEntities();
gvCustomers.DataSource = dbContext.CustomersTable.ToList();

Gumma

Answer 3

though this question has already been answered still I want to show that you can even get the answer directly from the message box as well (i got the same error) ERROR DIALOGUE BOX IMAGE

using (var retrive=new Models.Academy_MSDBEntities())
        {
            var query = retrive.Students.Where(s => s.Year == year).ToList();
            return query;
        }

Editor