How to get specific column in Entity Framework

Question

I have a product table. I want to get a specific column (only name and price columns) from this table, but I am getting an error. What am I doing wrong?

To summarize, I want to write

SELECT name, surname 
FROM Product 
WHERE name='emre'

but in my Entity Framework based code.

public class products
{
    public int ID { get; set; }
    public string name { get; set; }
    public decimal price { get; set; }
    public int stock { get; set; }
}

public class EtradeContext:DbContext
{
    public DbSet<products> prdcts { get; set; }
}

My ProductDal.cs is below:

    public List<products> GetNameAndPrice()
    {
        using (EtradeContext context = new EtradeContext())
        {

            var result = (from x in context.prdcts
                          where x.name == "emre"
                          select new products
                          {
                              name = x.name,
                              price=x.price

                          }).ToList();
            return result;
        }
    }

And I am getting this error.

The error is because you were try to project your query result to same entity that you defined as Dbset for Dbcontext means you cannot use entity for projecting query result that you used for DbSet — er-sho, Mar 12 '19 at 11:00
So you need view model for to collect query result. so create a view model means new class with name `productsViewModel` and copy all properties from `products` to new one and then change method return type to List and project your result to new one like `select new productsViewModel {}` — er-sho, Mar 12 '19 at 11:01
Possible duplicate of [The entity cannot be constructed in a LINQ to Entities query](https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query) — ElasticCode, Mar 12 '19 at 11:04

score 3 · Accepted Answer · edited Mar 12 '19 at 11:50

You cannot project the result to the same entity. What you can do is to use an anonymous type, something like a DTO, ViewModel or something like this. But using the same class in the select like you have in your DBSet will produce your error:

var result = (from x in context.prdcts
              where x.name == "emre"
              select new ProductDTO
                      {
                          name = x.name,
                          price = x.price
                      }).ToList();

score 0 · Answer 2 · answered Mar 12 '19 at 11:54

Use this code:

public class ProductDTO
{
    public string Name { get; set; }
    public string Price{ get; set; }
}

  public List<ProductDTO> GetNameAndPrice()
    {
        using (EtradeContext db= new EtradeContext())
        {
        return (from p in db.prdcts
            where p.name == "emre"
            select new ProductDTO { Name = p.name,Price = p.price }).ToList();
        }
    }

Milad · Answer 3 · 2019-03-12T12:50:36.877

0

You can try this way too.
It,s clear and optimized.

 using (EtradeContext context = new EtradeContext())
    {

        var result = context.prdcts
            .Where(x => x.name == "emre")
            .Select(s => new
            {
                name = s.name
                ,
                price = s.price
            }).ToList();

        return result;
    }

edited Mar 12 '19 at 12:50

answered Mar 12 '19 at 11:59

Milad

117
1
8

How to get specific column in Entity Framework

3 Answers3