Exclude property from WebApi OData (EF) response in c#

Question

I'm working with a WebApi project in C# (EF code first) and I'm using OData. I have a "User" model with Id, Name, LastName, Email, and Password.

In controller for example I have this code:

// GET: odata/Users
[EnableQuery]
public IQueryable<User> GetUsers()
{
    return db.Users;
}

If I call /odata/Users I'll get all the data: Id, Name, LastName, Email, and Password.

How can I exclude Password from results but keep available in controller to make Linq queries?

Solution 1

I made a craft and temporary solution to this problem (is not the best solution because UserInfo is not an entity type and not support $select or $expand). I created a new model called UserInfo just with the properties I need (apart of User):

public class UserInfo
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

Then I changed the method in the controller:

// GET: odata/Users
[EnableQuery]
public IQueryable<UserInfo> GetUsers()
{
    List<UserInfo> lstUserInfo = new List<UserInfo>();

    foreach(User usr in db.Users)
    {
        UserInfo userInfo = new UserInfo();
        userInfo.Id = usr.Id;
        userInfo.Name = usr.Name;
        userInfo.Email = usr.Email;

        lstUserInfo.Add(userInfo);
    }

    return lstUserInfo.AsQueryable();
}

Solution 2

I'm a little late to the topic but I think this might help you out.

I assume that you will want to encrypt the password for storage purposes. Have you looked at using an odata action to set the password? Using an action lets you ignore the password property when setting up your entities while still exposing a clean way to the end user to update the password.

first: ignore the password property

builder.EntitySet<UserInfo>("UserInfo").EntityType.Ignore(ui => ui.Password);

second: add your odata action

builder.EntityType<UserInfo>().Action("SetPassword").Returns<IHttpActionResult>();

Then add the SetPassword method to your UserInfoController.

Solution 3

How can I exclude Password from results but keep available in controller to make Linq queries?

Ignore it. From Security Guidance for ASP.NET Web API 2 OData:

There are two ways to exlude a property from the EDM. You can set the [IgnoreDataMember] attribute on the property in the model class:

public class Employee
{
    public string Name { get; set; }
    public string Title { get; set; }
    [IgnoreDataMember]
    public decimal Salary { get; set; } // Not visible in the EDM
}

You can also remove the property from the EDM programmatically:

var employees = modelBuilder.EntitySet<Employee>("Employees");
employees.EntityType.Ignore(emp => emp.Salary);

Solution 4

It might be a little late, but an elegant solution would be to add a custom QueryableSelectAttribute and then simply list the fields that you want selected on the serve-side. In your case it will look something like this:

public class QueryableSelectAttribute : ActionFilterAttribute
{
    private const string ODataSelectOption = "$select=";
    private string selectValue;

    public QueryableSelectAttribute(string select)
    {
        this.selectValue = select;
    }

    public override void OnActionExecuting(HttpActionContext actionContext)
    {
        base.OnActionExecuting(actionContext);

        var request = actionContext.Request;
        var query = request.RequestUri.Query.Substring(1);
        var parts = query.Split('&').ToList();

        for (int i = 0; i < parts.Count; i++)
        {
            string segment = parts[i];
            if (segment.StartsWith(ODataSelectOption, StringComparison.Ordinal))
            {
                parts.Remove(segment);
                break;
            }
        }

        parts.Add(ODataSelectOption + this.selectValue);

        var modifiedRequestUri = new UriBuilder(request.RequestUri);
        modifiedRequestUri.Query = string.Join("&", parts.Where(p => p.Length > 0));
        request.RequestUri = modifiedRequestUri.Uri;

        base.OnActionExecuting(actionContext);
    }
}

And in the controller you simply add the attribute with the desired properties:

[EnableQuery]
[QueryableSelect("Name,LastName,Email")]
public IQueryable<User> GetUsers()
{
    return db.Users;
}

And that's it!

Of course the same principle can be applied for a custom QueryableExpandAttribute.

Solution 5

Add [NotMapped] attribute on the Password property in User Class as following:

public class User
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Email { get; set; }

    public string LastName {get; set; }

    [NotMapped]
    public string Password {get; set;}
}

View more solutions

Comments

[Martín]

I get "message": "LINQ to Entities does not recognize the method 'ExampleAPI.Models.User Map[User](System.Object)' method, and this method cannot be translated into a store expression.",

[dariogriffo]

Missing a ToList in the middle, check it now

[Martín]

I changed the code and the error has been solved but still getting the password in the Odata response.

[dariogriffo]

Try with Ignore instead of UseDestination value (I updated my answer)

[Martín]

Thanks but the password is still being displayed in the response.

[dariogriffo]

Sorry, it was ForMember, not SourceMember, hope you still need this

[dariogriffo]

Let us continue this discussion in chat.

[Martín]

Maya, that's not working for me because as I said in my post I need to keep the property available in the controller to make linq queries. If I try with NotMapped the property will be no present in the entity model. I need that property but I just want to hide it from API responses.

[Martín]

Thanks but I've seen that documentation two days ago. If I try with [IgnoreDataMember] I will not have access to the Password in the EDM so that's not working for me. I can't remove the property programmatically from controller because the EDM is defined into WebApiConfig.cs. As I said I need this visible into the EDM but not in the response.

[ta.speot.is]

What does that mean? Entity Framework should still allow you to query it.

[Martín]

If you exclude the property from EDM this is not going to appear into the GET response (this is OK), but if you try for example to make a POST to create a new User in the API the password will be not received because that property is not part of the EDM. That's why I need to hide the password only for responses but not all the EDM (I think the only solution is to create a new class just with the parameters that I need only for responses).

[Martín]

That's not a good solution. As I said I need Password available in the EDM!!! I just want to remove it from the response!

[Rick Willis]

I guess i'm confused as to what you mean by "available in the EDM". This solution allows you to access the password property in the api and removes it from the payload.

[Martín]

I just need to have access to the password inside a method in the controller but I don't want to show it into the response (when I return the User object). When I say that I need this available in the EDM I just say that I need to work with the password in the controller (work with linq queries, etc).

[Peter Lillevold]

This is the correct solution, imo. As I understand your goal, you want to work with User in your controller and not need the extra UserInfo mapping class. With this solution you can. Drop UserInfo and expose User directly. Then apply @RickWillis solution on the User entity, this will result in the Password not be exposed in the OData payload.

[SzilardD]

Maya's solution is for ignoring the property from OData query but still serializing it. Thanks!

[jwize]

Doesn't this break the IQueryable design? What if you want 5 records out of 10,000,000?

[Raffaeu]

This is also a very interesting idea for a sort of "Permission Attribute" where only certain properties/expands can be returned depending on permissions.

[Artemious]

this is the only thing that worked for me. However, unfortunately, it removes the "$select" clause from the request URI itself. So if I want to filter the fields down to only Name and LastName, it will return ALL the fields - Name,LastName,Email - anyway :-(

[Artemious]

What is "db.User"? Where do I find the "db" variable? If I use "Query()" instead, this will raise an exception: System.InvalidOperationException: 'The source IQueryable doesn't implement IDbAsyncEnumerable<MyType>. Only sources that implement IDbAsyncEnumerable can be used for Entity Framework asynchronous operations. For more details see go.microsoft.com/fwlink/?LinkId=287068.'

[Artemious]

Another disadvantage is that every time I add a new property to my DTO, I have to change its [QueryableSelect("")] attribute on every method in my TableController.

[Raven]

This sounds like a great idea, but i can't find a good example how to combine OData, EntityFrameworkCore and a Domain model. do you have one?

[Fidan]

Automapper has ProjectTo() extension method for IQueryable that will not break design

[Konstantinos Papakonstantinou]

This worked for me as far as the response is concerned but screwed up something else, I have queries with complex ordering and they stopped working after I used those attributes

[Maulik Modi]

@Martin, Use separate model for CreateUserCommand and UserInfoQuery

[Jesper1]

@MaulikModi I tried, but it does not work: github.com/OData/WebApi/issues/2517

[Jesper1]

@Martín Did you manage to find a solution?