Retrieve an Object from Entityframework Without One Field

Retrieve an object from entityframework without ONE field

Is there a way to retrieve a List but without this column filled?

Not without projection which you want to avoid. If the column is mapped it is natural part of your entity. Entity without this column is not complete - it is different data set = projection.

I'm using here the anonymous type because otherwise you will get a
NotSupportedException: The entity or complex type 'ProjectName.File'
cannot be constructed in a LINQ to Entities query.

As exception says you cannot project to mapped entity. I mentioned reason above - projection make different data set and EF don't like "partial entities".

Error 16 Error 3023: Problem in mapping fragments starting at line
2717:Column Files.Data in table Files must be mapped: It has no
default value and is not nullable.

It is not enough to delete property from designer. You must open EDMX as XML and delete column from SSDL as well which will make your model very fragile (each update from database will put your column back). If you don't want to map the column you should use database view without the column and map the view instead of the table but you will not be able to insert data.

As a workaround to all your problems use table splitting and separate the problematic binary column to another entity with 1 : 1 relation to your main File entity.

Exclude certain column from Entity Framework select statment

Create a DTO with all the properties you need except the image property:

public class YourDTO
{
    public string YourProperty1 { get; set; }
    public string YourProperty2 { get; set; }
    // etc
}

You can then do:

var productDto = context.Products
                        .Where(x => x.Id == productId)
                        .Select(x => new YourDTO {
                            YourProperty1 = x.DbProperty1,
                            YourProperty2 = x.DbProperty2        
                            // etc, don't include the image column
                        });

Update:

If you don't want to map the results to YourDTO, you can project into an anonymous type:

var product = context.Products
                     .Where(x => x.Id == productId)
                     .Select(x => new {
                         x.DbProperty1,
                         x.DbProperty2        
                         // etc, don't include the image column
                     });

...and if you want to provide a custom name for each of the properties of the anonymous type:

var product = context.Products
                     .Where(x => x.Id == productId)
                     .Select(x => new {
                         YourProperty1 = x.DbProperty1,
                         YourProperty2 = x.DbProperty2        
                         // etc, don't include the image column
                     });

All of the above approaches would be functionally equivalent to the following SQL:

SELECT p.DbProperty1, p.DbProperty2 
FROM products p
WHERE p.Id = WhateverId;

How to only load certain fields of a child object in Entity Framework 6.1?

To achieve what you need you don't have other choice than project your query to an anonymous type or a DTO. As you can see, in the Include extension method you can just specify the related entities you want to load which is translated in an inner join with a table (or several joins, see the Remarks section in the quoted link), but that doesn't mean you're going to load all the properties from the related entities. If you call the Select method you can choose which columns you want to project, but you can't project a Linq to Entities query using an entity type, you must use one of the two options that I commented above. So, my advice is create in your business logic layer a set of classes (DTOs) to project the result of your queries, eg:

 public class ProductDTO
 {
    [DataMember]
    public Guid ProductId {get; set;}
    [DataMember]
    public virtual IEnumerable<DateTime> TransactionDates { get; set; }
 }

Later you can do something like this:

var product = db.Products.Where(p => p.ProductId = productId)
                         .Select(pr=> new ProductDTO
                         {
                           ProductId = pr.ProductId,
                           TransactionDates = pr.Transactions.Select(tr=>tr.Date),
                         }.ToList();
 

See I don't need to call Include extension method in this case, because in the Select I'm projecting a column from Transactions table. In that point the data is not still loaded, you are just defining a linq query that later is translated to sql. When that take place?, when you call ToList extension method.

As a last recommendation, I suggest you to take a look to Automapper. Once you have mapped your entities with their respective DTOs, your queries could be this way:

var product = db.Products.Where(p => p.ProductId == productId)
                         .ProjectTo<ProductDTO>()    
                         .ToList();

More info about ProjectTo extension method in this link

EF: Eagerly include single field

You're touching on an issue that does exist in EF.

The problem lies in how EF handles the loading of data. It loads all scalar properties of an object, but not the navigational properties.

Include influences this behavior, by telling EF to also include a specified navigational property (with all of its scalar properties)

But then we get to Select. When you use this, you are essentially giving a fixed list of columns that you want to retrieve. This overrides the default behavior of loading all scalar properties, which sadly also overrides the Include statements that you added.

---

The simplest solution is to explicitly tell EF to retrieve the details:

var d = DataContext.Objects.Include(o => o.Details)
    .Select(o => new { 
               Object = o, 
               ObjectDetails = o.Details,
               OwnerName= o.Owner.Name 
    });

As an aside, since the Select explicitly states which columns EF needs to retrieve, you don't need the Include statement anymore.

This behavior can already be seen by you retrieving o.Owner.Name without actually calling Include(o => o.Owner).

Reading data from DB using Entity Framework results in missing fields of an object

change in edmx file

<EntityContainer Name="StudentContext" annotation:LazyLoadingEnabled="false">

OR

{

    public DbContext()
        : base("name=StudentContext")
    {
         this.Configuration.LazyLoadingEnabled = false;
         this.Configuration.ProxyCreationEnabled = false;
    }

    public DbSet<Student> Students { get; set; }
}

Or you can also modify your query

var a = this.context.Students.Include(s => s.Sports).FirstOrDefault();

How to select a single column with Entity Framework?

You can use LINQ's .Select() to do that. In your case it would go something like:

string Name = yourDbContext
  .MyTable
  .Where(u => u.UserId == 1)
  .Select(u => u.Name)
  .SingleOrDefault(); // This is what actually executes the request and return a response

If you are expecting more than one entry in response, you can use .ToList() instead, to execute the request. Something like this, to get the Name of everyone with age 30:

string[] Names = yourDbContext
  .MyTable
  .Where(u => u.Age == 30)
  .Select(u => u.Name)
  .ToList();

Only get specific columns

Sure. Projection does this:

var q = from r in Context.TableName
        select new 
        {
            Id = r.Id,
            Status = r.Status
        }

Here's an actual example (obviously, my DB has different tables than yours). I added my EF model to LINQPad and typed the following query:

from at in AddressTypes
select new
{
    Id = at.Id,
    Code = at.Code
}

LINQPad shows me that the generated SQL is:

SELECT 
    1 AS [C1], 
    [Extent1].[AddressTypeId] AS [AddressTypeId], 
    [Extent1].[Code] AS [Code]
FROM 
    [dbo].[AddressType] AS [Extent1]

None of the other fields from the table are included.

Responding to updated question

Your columns argument says it takes a type T and returns the same type. Therefore, the expression you pass must conform to this, or you need to change the type of the argument, i.e.:

public IEnumerable<U> GetBy<U>(Expression<Func<T, bool>> exp, Expression<Func<T, U>> columns)
{
    return Table.Where<T>(exp).Select<T, U>(columns);
}

Now the expression can return any type you care to use.

---

Related Topics