CODEWITHSUNDEEP
c#asp.netentity-frameworkgenerics
TTCG
TTCG

Reputation: 9113

Getting Specific Columns in Entity Framework

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.

Upvotes: 3

Views: 1058

Answers (2)

mjwills
mjwills

Reputation: 23820

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.

Upvotes: 3

maxlego
maxlego

Reputation: 4914

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

Upvotes: 0

Related Questions