26

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())?

Improve this question
7
  • If a list of columns not known at compile time, and is only available in runtime, then you should use dynamically created queries. See DynamicLINQ, PredicateBuilder, LINQKit, ExpressionTrees. Commented Sep 25, 2017 at 11:30
  • They are known at the compile time. Commented Sep 25, 2017 at 11:32
  • The only difference between Find and let say FirstOrDefault(condition) is that the former first looks at the local cache and executes db query only if the object is not there. But since it (as any method which returns entity instances) will only return fully populated objects, it cannot be used for projection (Select) which is the only way to get partial data in EF. Commented Sep 25, 2017 at 11:55
  • @IvanStoev I understand that, only issue is that I want to change all Finds in my code to Where/Select (cause of performance issue), Find generates for example about 300 lines of Select code, but Where/Select about 10. Commented Sep 25, 2017 at 11:59
  • Then go ahead and change it. EF cannot cache partially loaded data for you. And there is no "lazy loading" for primitive data properties. Again, if EF method returns entity object, it will be fully populated. Hence you can't use Find and the only option is Where + Select. Commented Sep 25, 2017 at 12:08

4 Answers 4

Reset to default
32 
    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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The OP has already acknowledged the option of anonymous types: "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?" There are also quality issues with your answer, a simple code block is not sufficient.
Find() method returning single value (in my case), which means I can't perform Select() on it.
Nope, Find() will translate SELECT TOP(1) * FROM MyTable WHERE Id = @id
this answer works really well. One drawback you should be aware of is that at the time of writing this comment you can not pass anonymous objects into other functions, so if you have a complex procedure to run, because not functions are supported in iqueryable you have no choice but to make a giant method.
7

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.

Improve this answer

8 Comments

Read what OP asked: So I know that I can use anonymous objects or data transfer objects (DTO), but is there any other (yet simple) method to get specific columns?
No. There isn't. You query or use objects.
Yes, I know (or at least I think), but that does not answer OP's question :)
Actually I have given some ways to do it, but they're quite hacky.
Actually you can't use dynamic types in EF projections. EF is statically type based system.
|
7

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.

Improve this answer

7 Comments

It tends to cause all kinds of unexpected problems if you use "partial entities". For one, it's a constant source of ambiguity in code. Now all code has to be aware of what kind of entity it receives.
I don't know how you verified that EF Core translates that LINQ to SQL but it gives me another result, it selects all the columns and not the ones I want.
Sorry. It does return the selected columns. It returns all columns when I use a Dto mapper. See: stackoverflow.com/questions/62115690/…
@GertArnold This is where concepts like projection objects, data transfer objects, business logic adapters etc. come into play. It must be clear that what is the required input and what is the expected output of some code, that is, accurate planning is a disproof for "now all code has to be aware of what kind of entity it receives". The projection shown above is just fine as it is.
@Kerberos I'd say that an unambiguous data contract is part of accurate planning. Even in code that's fully under my own control I wouldn't like to have to keep in mind which properties are and aren't populated when receiving entity objects. An exception to this rule is when we deliberately work with stub entities to save database roundtrips. We always try to make that really explicit in code.
|
2

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();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.