Select specific columns from table, Entity Framework

Question

I have a question about selecting specific columns from table using entity framework. The problem is, that I'm using Find() method to get my desired table, by primary key, then taking from it some data.

I have one table with massive amounts of columns and if I call Find() method, it will return all columns of that row, but I want to use only, for example, the data from 2 columns.

MyTable table = context.MyTable.Find(id); //Get MyTable object from context, id = primary key
string p1 = table.Prop1;
string p2 = table.Prop2;

This will return single object with all (for example it has Prop1, Prop2,...,PropN) properties filled (if its filled in database). So I know that I can use anonymous objects or data transfer objects (DTO), but [question1] is there any other (yet simple) method to get specific columns? [question2] Is it affecting on performance if I use Find() (or I should use Where()/Select())?

Answer 1

    var items = context.MyTable.Where(x => x.Id == id)
                  .Select(x => new
                               {
                                    P1 = table.Prop1,
                                    P2 = table.Prop2
                               });

This will translate into a sql call like:

SELECT p.Prop1, p.Prop2 FROM mytable p WHERE p.Id = id

Answer 2

Use Data Transfer Objects: DTO, which is a recommened microsoft pattern.

Putting it simple, they are just objects that hold data.

Then do like someone suggested:

public class MyDto
{
  public string Prop1 {get;set;} = String.Empty
  public string Prop2 {get;set;} = String.Empty
}
MyDto x = new MyDto();
 x = context.MyTable.Where(x => x.Id == id)
                  .Select(x => new MyDto
                               {
                                    P1 = table.Prop1
//I don't want prop 2, for example
                               });

And pass around the object. Set defaults for Auto Properties (C# 6 and up) and initialize only the properties you want.

EDIT: I've read you don't want to use anonymous and DTO, then how you want to do it. You either use objects or anonymous.

Other ways is just build a layered structure and call the query method directly where you need it. Patterns exists for a reason.

You can call queries against Dynamic objects. With these you may assign fields that will be resolved at runtime, at the cost of losing strong typing.

You might also want to check if it's performance-whorty to use dynamics.

Answer 3

Another option is to project the class back to itself, and only provide the columns you want.

var table = context.MyTable.Where(mt => mt.Id == id)
                           .Select(mt => new MyTable
                            {
                                Prop1 = mt.Prop1,
                                Prop2 = mt.Prop2
                            })
                           .FirstOrDefault();

string p1 = table.Prop1;
string p2 = table.Prop2;

Effectively, you get the strong typing of a DTO without having to create/maintain another class. All columns not specified will be populated with the default value of the column's type.

It translates to the following in SQL:

SELECT TOP(1) m.Prop1, m.Prop2 FROM MyTable m WHERE m.Id = @id

Which indeed gives a performance boost over Find() assuming you're not specifying all the columns.

EDIT: As Gert mentioned, use with caution, as it's not always obvious when a "partial entity" is being passed around.

Answer 4

You can use free AutoMapper's ProjectTo<> extension, so the query would look like this:

context.OrderLines
   .Where(ol => ol.OrderId == orderId)
   .ProjectTo<OrderLineDTO>(configuration)
   .ToList();