Using IQueryable select to filter columns

Question

I've been exploring WEB API based on the guide at www.asp.net, and I've run into a problem when trying to filter columns from a table.

My code is simply this:

    // GET api/Admin
    public IEnumerable<Product> GetProducts(testvar = "")
    {
        IQueryable<Product> productString = db.Products;

        if (testvar != "")
        {
            productString = productString.ToList().Select(i => new Product
            {
                Name = i.Name
            }).AsQueryable();
        }
        return productString;
    }

All I want to do is retrieve the Name column from the Product table for my JSON. However, instead of getting something like:

[{"Name":"Hammer"}] or [{"$id":"1","Name":"Hammer"}]

I'm getting:

[{"$id":"1","Id":0,"Name":"Hammer","Price":0.0,"ActualCost":0.0}]

The Price and ActualCost values are normally 16.99 and 20.00, but instead of removing them, they are just returned as 0. Same for Id.

Is there an easy way to stop it returning the Id, Price and ActualCost? Perhaps I'm missing something really simple, but the below two links are the closest I've come to finding something that might work.

IQueryable C# Select

The entity cannot be constructed in a LINQ to Entities query

As per the guide I've been using ( http://www.asp.net/web-api/overview/creating-web-apis/using-web-api-with-entity-framework/using-web-api-with-entity-framework,-part-1 ) I'm working in an MVC 4 project with the Orders, Projects and OrderDetails tables.

Thanks

If you're only trying to return the name, why don't you just return an `IEnumerable`? (I strongly suspect you should get rid of the `ToList` and `AsQueryable` calls, too.) — Jon Skeet, Apr 27 '13 at 17:49
Just had a play around with the code. I only had ToList and AsQueryable as it stopped another error from happening, based on one of the SO links I posted. But, I did have a play with returning a string and I seem to be getting somewhere. It means I will have to build my own string for each different request filtering though. I just imagined there would be an easier way. Thanks — ASouthorn, Apr 28 '13 at 12:02

score 1 · Answer 1 · answered Apr 27 '13 at 17:50

1

You are still returning the full Product entity type, so even though you are projecting to a collection of new Product, the uninitialized properties will still have their default values (0 for int).

You should create a new type which just contains the properties (e.g. ProductInfo) you wish to expose and return a collection of this type.

Alternatively, if your service supported the $select query option, the client could perform projections.

answered Apr 27 '13 at 17:50

devdigital

34,151
9
98
120

1

The only issue with ProductInfo for example is that I plan for my API to be much bigger and rely on any property call, including multiple selectors such as both name and price. I'll look at $select – ASouthorn Apr 27 '13 at 17:55
Yes, $select support is being worked on in Web API. According to http://stackoverflow.com/questions/16093783/web-api-odata-select-not-working it should be in the nightly builds soon. – devdigital Apr 27 '13 at 18:00

score 0 · Accepted Answer · answered Apr 28 '13 at 17:44

I figured it out. I just assigned the Product to a new variable and made the select call on that, and it worked perfectly.

So now instead of:

productString = productString.ToList().Select(i => new Product
        {
            Name = i.Name
        }).AsQueryable();

I have

var productInfos =
                    from i in productString
                    select new { i.Name };
                    newVariable.Add(productInfos);

I then return the newVariable, which is a list of objects instead of an IEnumerable of Products.

Using IQueryable select to filter columns

2 Answers2