Getting only what is needed with Entity Framework

Question

With a plain connection to SQL Server, you can specify what columns to return in a simple SELECT statement.

With EF:

Dim who = context.Doctors.Find(3) ' Primary key is an integer

The above returns all data that entity has... BUT... I would only like to do what you can with SQL and get only what I need.

Doing this:

 Dim who= (From d In contect.Doctors
                     Where d.Regeneration = 3
                     Select New Doctor With {.Actor = d.Actor}).Single

Gives me this error:

The entity or complex type XXXXX cannot be constructed in a LINQ to Entities query.

So... How do I return only selected data from only one entity?

See [here](http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query) for a solution. — Jesse van Assen, Apr 22 '12 at 09:50
@JessevanAssen nice link, and good explanation for why in the comments — Manatherin, Apr 22 '12 at 09:58

Manatherin · Answer 1 · 2012-04-22T09:43:37.533

Basically, I'm not sure why, but Linq can't create the complex type. It would work if you were creating a anonymous type like (sorry c# code)

var who = (from x in contect.Doctors
           where x.Regeneration == 3
           select new { Actor = x.Actor }).Single();

you can then go

var doctor = new Doctor() {
    Actor = who.Actor
};

but it can't build it as a strongly typed or complex type like you're trying to do with

var who = (from x in contect.Doctors
           where x.Regeneration == 3
           select new Doctor { Actor = x.Actor }).Single();

also you may want to be careful with the use of single, if there is no doctor with the regeneration number or there are more than one it will throw a exception, singleordefault is safer but it will throw a exception if there is more than one match. First or Firstordefault are much better options First will throw a exception only if none exist and Firstordefault can handle pretty much anything

score 0 · Answer 2 · answered Apr 22 '12 at 09:36

0

You can just simply do this:

 Dim who= (From d In contect.Doctors
                     Where d.Regeneration = 3
                     Select d.Actor).Single

answered Apr 22 '12 at 09:36

Thach Mai

915
1
6
16

Wahid Bitar · Accepted Answer · 2012-04-24T13:12:48.363

The best way to do this is by setting the wanted properties in ViewModel "or DTO if you're dealing with upper levels" Then as your example the ViewModel will be:

public class DoctorViewModel{
public string Actor {get;set;}
// You can add as many properties as you want
}

then the query will be:

var who = (From d In contect.Doctors
                     Where d.Regeneration = 3
                     Select New DoctorViewModel {Actor = d.Actor}).Single();

Sorry i wrote the code with C# but i think the idea is clear :)

score 0 · Answer 4 · answered Apr 22 '12 at 11:59

0

Try this

Dim who = contect.Doctors.SingleOrDefault(Function(d) d.Regeneration = 3).Actor

answered Apr 22 '12 at 11:59

naveen

53,448
46
161
251

Won't this throw an exception if there's no record with *Regeneration==3* ? – McGarnagle Apr 23 '12 at 08:24

Getting only what is needed with Entity Framework

4 Answers4