Return a subset of a related collection in c# EF6

Question

I have a webservice that allows the user to query a give subset of data from a database. I will only know which fields the user wants at runtime. It's quite easy to build a dynamic linq query to retrieve only the corresponding properties from the database for the fields in the object table itself, but how can I dynamically specify which fields I want to retrive in the related table ? Sounds confusing, let me explain with a sample.

So let's say I have a structure like:

class User {
 public string Name {get; set;}
 public List<Role> Roles {get; set;}
}

class Role {
  public string RoleName {get; set;}
  public string RoleDescription {get; set;}
}

So User and Role are the objects used by EF6 in my code first databse table generation.

Now I have webservice that wants to get the username and the name of the roles from the database. It will send a parameter like

var requestData = "{fields: {'Name', 'Roles.RoleName'}}"`;

What I would like to do is only retrieve the RoleName field from the Roles collection, knowing another client might ask the Roles.RoleDescription only so I need to dynamically create the query. Ideally I should be able to generate this at the Select level on an IQueryable object in order to avoid getting a huge number of data from the database and only using one field.

What I have tried so far:

Using System.Linq.Dynamic to retrieve the properties of the User table. Something like:

var dynamicFields = GetFieldsFromRequestData(requestData); // results in "Name, Roles" 
var query = context.Set<User>().Select(dynamicFields);
query.Load();

Works like a charm. BUT will query all fields from the Roles collection. I don't seem to be able to only select the Roles.RoleName column from the Roles table using the same strategy, which is what I want.

var query = context.Set<User>().Select("Name, Roles.RoleName");

Does not work.

I also tried using Linq.Expressions which requires specifying the objects types:

class UserDTO {
 public string Name {get; set;}
 public List<RoleDTO> Roles {get; set;}
}

class RoleDTO {
 public string RoleName {get; set;}
}

then using Linq.Expressions I generate

var generatedExpression = GetFieldsFromRequestDataLinq(requestData); //
results in an expression = (user => New UserDTO {Name = user.Name, Roles = user.Roles });
var query = context.Set<User>().Select(generatedExpression);

Works like a charm EF converts the objects in the Roles collection to RoleDTO objects.

BUT this implies knowing in advance which fields will be queried in the Roles collection since I defined the RoleDTO type. Which is not what I want since I don't know the list of queried fields.

So if someone has a nice way of solving this issue that would be awesome. I am open to AutoMapper solutions, to Newtonsoft JSON approaches, ...

Have been battling with this since 10 days ;-)

http://docs.automapper.org/en/latest/Queryable-Extensions.html#explicit-expansion — Lucian Bargaoanu, Jun 23 '19 at 09:14
Thanks! Could be an intereisting way of handling this but unfortunately the issue is with collections and it seems I would need to generate a dynamic select lambda query. — user2097439, Jun 23 '19 at 19:05
https://github.com/AutoMapper/AutoMapper/blob/master/src/IntegrationTests/ExplicitExpansion/ExpandCollections.cs — Lucian Bargaoanu, Jun 23 '19 at 19:12
I'm sure if you spend some time with that code you'll understand it. Otherwise you should simply use whatever you're comfortable with. — Lucian Bargaoanu, Jun 24 '19 at 09:01
Thanks I tried it, with d => d.Groups.Select(g => g.GroupName) but regardless it returns all fields of the Groups collection. Doesn't seem to work. — user2097439, Jun 24 '19 at 22:10
Well, you must be doing smth wrong, because obviously all those tests pass. — Lucian Bargaoanu, Jun 25 '19 at 01:50
Take it step by step. Debug that test, see that it does what you want. Then start changing it to be more similar to what you need. If smth fails, rollback to the previous step. — Lucian Bargaoanu, Jun 25 '19 at 08:03

score 0 · Answer 1 · answered Jun 23 '19 at 10:26

0

you should be able to add properties to anonymous type on runtime:

Add property to anonymous type after creation

Utilitizing reflection the query could look like:

    var query = context.Set<User>().Select(p=>{ 
        var dto = new UserDTO{};

        parameterNames.foreach(pn =>{
            var value = p.GetField( pn ).GetValue (p, null);
            UserDTO.Add(parameter, value);
        });
        return dto;
    });

answered Jun 23 '19 at 10:26

Kejklir

23
4

Hmm thanks will try this out. Would it work if the property is a Collection ? – user2097439 Jun 23 '19 at 19:06
tried Expression> exp = x => { var t = new UserSmallDTO { }; var value = x.GetType().GetField("FirstName").GetValue(t); t.AddProperty("FirstName", value); return t; }; var lst = context .Users .Select(exp) .ToList(); Got an error a lambda expression with a statement body cannot be converted to an expression tree – user2097439 Jun 24 '19 at 15:50
x.GetType().GetField("FirstName").GetValue(t); shouldnt t in getValue be x, as you are trying to access value of x? – Kejklir Jun 25 '19 at 07:19

score 0 · Answer 2 · answered Jun 23 '19 at 14:56

0

Based on the context you provided I would suggest to try out OData/GraphQL.

It is usually flexible enough to be able to handle such kind of scenarios but can cause some other struggles.

answered Jun 23 '19 at 14:56

Artyom Ignatovich

591
1
3
13

Return a subset of a related collection in c# EF6

2 Answers2