0

Title might be confusing, so let me explain. Let's pretend I have a Dbo that looks like this:

public class EmployeeDbo
{
    public int EmployeeId { get; set; }
    public string FirstName{ get; set; }
    public string LastName{ get; set; }
    public string EmployeeData { get; set; }
    public string EvenMoreEmployeeData{ get; set; }
}

And then somewhere in my data access layer I have this code:

  // employeeId is some param that gets passed into this method
  var query = _dbContext.Employees.Where(e => e.EmployeeId = employeeId);

  // Now I want to further modify the query. I just want to load SOME values from the DB, instead of all of them
  // The problem I have is that now this is trying to assign query, which is IQueryable<EmployeeDbo> to IQueryable<{string FirstName, string LastName}>
  query = query.Select(e => new 
      {
          e.FirstName,
          e.LastName
      });

 // More code here that is expecting IQueryable<EmployeeDbo>

So, to summarize my question, how can I just populate SOME properties in a dbo with data from the DB? I suppose I could create a new Dbo with only the data I want as properties, but can I avoid doing that?

Improve this question
5
  • 1
    query.Select(e => new EmployeeDbo { EmployeeId = 0, FirstName = e.FirstName, LastName = e.LastName }) The rest of the properties would be null. Commented Feb 26, 2021 at 20:48
  • @HereticMonkey That seems so obvious, why didn't I think of that? Thank you! Commented Feb 26, 2021 at 20:53
  • 1
    @Andrio ... be aware that if you new EmployeeDbo here, you're (probably) no longer working with a tracked entity. That is probably fine for Read\Query operations in a service type, but could pose issues in DAL. The scope of this concern relates to the reasoning of using Data Transitioning\Transferable Objects (DTOs). Commented Feb 26, 2021 at 21:10
  • Actually after trying the solution by @HereticMonkey I'm getting this error cannot be constructed in a LINQ to Entities query. Commented Feb 26, 2021 at 21:21
  • right, so this question relates to stackoverflow.com/q/5325797/1366179 and though that question is framed differently, may actually be considered a duplicate in that respect. Commented Feb 27, 2021 at 11:11

2 Answers 2

Reset to default
1

Using Select is a Projection, it does not actually retrieve anything from the database, it just nominates what data you will want, and as such it should be moved to the end of your querying to avoid issues /w type.

If you just want to extract a sub-set of names from the query as it is at a particular point then you can use:

var results = query.Select(e => new 
  {
      e.FirstName,
      e.LastName
  }).ToList();

ToList() is actually what executes the query at that point and returns the results into the "results" variable, leaving the "query" variable to be further refined and ultimately consumed.

Otherwise, if you're only going to want to return these details for a view, you need to move the Select towards the end of the search expression chain. The only thing after a Select will typically be ToList and pagination, (Skip/Take) or Single/First etc.

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

Comments

0

Entity Framework keeps an exclusive control on the queries it generates. The DbSets have this mapping defined for the Entities in the corresponding EDMX file in XML notation.

    <EntityType Name="Table1">
      <Key>
        <PropertyRef Name="Id" />
      </Key>
      <Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
      <Property Name="Column01" Type="int" />
      <Property Name="Column02" Type="int" Nullable="false" />
      <Property Name="Column03" Type="varchar" MaxLength="30" />
      <Property Name="Column04" Type="varchar" MaxLength="10" />
    </EntityType>

New instances created "using query.Select(e => new" and eliminating some fields from the output will simply ignore these entity fields. These fields will still be pulled from the database.

As far as I know there is currently no way to "load SOME values from the DB" using EF context. I would not recommend modifying Entity Classes or EDMX manually as it may have some unforeseen issues.

You can examine the queries Entity Framework generates in Visual Studio output window using the below before running the EF query in Visual Studio.

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

If you want to limit the data load from Entity Framework then consider using stored procedures instead or use other Micro ORMs such as Dapper for a more fine grained control on your data.

Improve this answer

4 Comments

welcome to stackoverflow. your answer\remark here is making a few presumptions and may be incorrect in other respects. First, not every EF uses EDMX (Database-First) - in fact, DB-First isn't supported in more recent versions of EF. Also, EDMX is used for code\type\embedded resource generation. It doesn't pose additional considerations to what OP is attempting here (or would otherwise face without having\using EDMX).
Additionally, the behavior\observation of pulling\querying all fields of entities even when specifying particular fields is based on caching in-memory\tracking feature of EF to mitigate against making subsequent queries to the DB. It is a feature of EF and it is configurable. The queries you're observing in log likely relate to that premise\feature.
@Brett Caswell, Thanks. I am aware that not every EF implementations use EDMX, that was just an example. Also, this post is about EF, not EFCore. Most application migrations would use EDMX though and query performance would be important as well. Would you be able to elaborate how we can "load SOME values from the DB" using EF, and this is not about caching but pulling only the data that we need. Although EDMX and Visual Designer is gone in EFCore but you can use Scaffold-DbContext to reverse engineer your entity and context classes.
entityframeworktutorial.net/efcore/…

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.