Getting Specific Columns in Entity Framework

Question

I would like to get the list of users from the database, but I want only 5 columns instead of all (it has about 35 columns). When I wrote like the following, it shows me no error at the compile time but the error at the runtime.

bksb_Users is the table name in my database as well as object name in the Entity Model.

public List<bksb_Users> SearchStudents(string reference, string firstname, string lastname)
        {
            return (from u in context.bksb_Users
                    where u.userName.Contains(reference)
                    && u.FirstName.Contains(firstname)
                    && u.LastName.Contains(lastname)
                    orderby u.FirstName, u.LastName
                    select new bksb_Users
                     {
                         user_id = u.user_id,
                         userName = u.userName,
                         FirstName = u.FirstName,
                         LastName = u.LastName,
                         DOB = u.DOB
                     }).Take(100).ToList<bksb_Users>();
        }

The error is...

The entity or complex type 'bksbModel.bksb_Users' cannot be constructed in a LINQ to Entities query.

Answer 1

Does below work?

public List<bksb_Users> SearchStudents(string reference, string firstname, string lastname) 
    { 
        var anon = (from u in context.bksb_Users 
                where u.userName.Contains(reference) 
                && u.FirstName.Contains(firstname) 
                && u.LastName.Contains(lastname) 
                orderby u.FirstName, u.LastName 
                select new 
                 { 
                     user_id = u.user_id, 
                     userName = u.userName, 
                     FirstName = u.FirstName, 
                     LastName = u.LastName, 
                     DOB = u.DOB 
                 }).Take(100).ToList(); 

        return anon.Select(z => new bksb_Users()
        {
            user_id = z.user_id, userName = z.userName, FirstName = z.FirstName, DOB = z.DOB
        }).ToList();
    } 

All I have done is split the task into two steps:

1. Get the data out (into an anonymous type) using LINQ to Entities.
2. Convert the anonymous type into the desired type using LINQ to Objects.

Note a better option would be to create a new type (class) that contains just the fields/properties you need - that would remove the need for step 2, and will make it clear to the callers of your function which columns are 'populated' and which aren't. It also means you are less likely to 'accidentally' try and persist these half populated entities back to the database.