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.

IIRC that normally means you don't have a public no-arguments constructor (although I thought the error was better for that) — Rup
– Rup, Commented Nov 28, 2011 at 11:01

mjwills · Accepted Answer · 2011-11-28 11:11:33Z

3

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:

Get the data out (into an anonymous type) using LINQ to Entities.
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.

edited Nov 28, 2011 at 11:11

answered Nov 28, 2011 at 10:56

mjwills

24.1k6 gold badges45 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ladislav Mrnka Over a year ago

This is actually the only way because EF doesn't allow projection to mapped types.

Till Over a year ago

I wouldn't recommend trying to push those objects back into persistence.

maxlego · Accepted Answer · 2011-11-28 11:10:05Z

0

for some reason i quess that field DOB looks something like this

public object DOB { get { return fieldX + fieldY } }

Entity framework does not understand that. All fields in query must be mapped with certain columns in DB

answered Nov 28, 2011 at 11:10

maxlego

4,9143 gold badges33 silver badges38 bronze badges

Collectives™ on Stack Overflow

Getting Specific Columns in Entity Framework

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related