Entity Framework - only get specific columns specified dynamically from list

Question

I would like to have some flexibility and provide some interface to specify list of columns that should be included to the final select dynamically.

For example for this table

    public class Person
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity), Key()]
        public int Id { get; set; }

        [Required]
        public string FirstName { get; set; }

        [Required]
        public string LastName { get; set; }

        public string Address { get; set; }

        ...
    }

I want to have at one case

persons.Select(p => new { FirstName = p.FirstName, LastName = p.LastName }).ToList();

but at another I want to have

persons.Select(p => new { FirstName = p.FirstName, LastName = p.LastName, Address = p.Address }).ToList();

at third case I'll need something else... So, I was thinking it would be good to have some flexible mechanism that would allow to specify the list of columns to extract.

Any ideas how I can do this?

I was reading a bit about LINQ expressions and I have some feeling that this is the right way but do not understand how implement it yet...

have you tried those two expressions? – DFord Aug 12 '15 at 14:57 — DFord, Aug 12 '15 at 14:57
That is the way to do it – Nick Strupat Aug 12 '15 at 15:14 — Nick Strupat, Aug 12 '15 at 15:14
@DFord, what's wrong with those two expressions? – Mr. Pumpkin Aug 12 '15 at 16:02 — Mr. Pumpkin, Aug 12 '15 at 16:02

Mant101 · Accepted Answer · 2015-08-12T15:21:41.463

1

If you include the NuGet package System.Linq.Dynamic it is pretty straightforward:

var columns = new [] { "FirstName", "LastName" };
var selectText = "new (" + string.Join(", ", columns) + ")";
var result = Users.AsQueryable().Select(selectText);

Or for type safety

var columns = new Expression<Func<User, object>> [] { x => x.FirstName, x => x.Surname };
var selectText = "new (" + string.Join(", ", columns.Select(x => ((MemberExpression)x.Body).Member.Name)) + ")";
var result = Users.AsQueryable().Select(selectText);

This is turning expression into string for a library that will turn them back so it isn't the most optimized approach, but the cost is likely to be insignificant compared to that database query.

You could do it by building expressions but the library does it all for you.

edited Aug 12 '15 at 15:21

answered Aug 12 '15 at 15:15

Mant101

2,705
1
23
27

Hi Mant101, thanks for pointing me to System.Linq.Dynamic... I tried to add it to my solution but unfortunately I already use EntityFramework.Extended and this one also have Select extension inside the same System.Linq.Dynamic namespace so they are in conflict with each other. I have tried the method from EntityFramework.Extended but it raises the exception... will continue digging to that direction. Thanks again... – Mr. Pumpkin Aug 12 '15 at 18:08
If you have a conflict you can use the normal class name.methodname notation to access the extension method as a normal static method. – Mant101 Aug 12 '15 at 22:50

score 0 · Answer 2 · answered Aug 12 '15 at 15:21

Basically it is the right way you're doing it. You could only make it a bit more comfortable and type safe by using classes as 'Models' for your data. Basically, create another Person class with either all fields of the table or only the ones you need in that 'person'. Doing this instead of just creating nameless objects and writing them to a list gives you more flexibility, since you can add other Methods that transform the data in this class.

You could also add different constructors to this class depending on which fields you want to initialize. Like this:

var modelPersons = persons.Select(p => new ModelPerson(p.FirstName, p.LastName)).ToList();

If you write a Constructor for every initialization you need, you'll have reusable and short code when querying. Of course, you still could use a object initializer like this:

var modelPersons = persons.Select(p => new ModelPerson {
  FirstName = p.FirstName
  LastName = p.LastName
}).ToList();

Which is as long as the one you posted, but it's better to use an object you can define yourself, add methods, format data etc. But all together you're using the right approach.

score 0 · Answer 3 · edited May 23 '17 at 11:44

0

To expand on Mant101's answer. You can solve the duplicate namespace problem by using extern alias. See also this answer to an SO question.

edited May 23 '17 at 11:44

Community

1
1

answered Aug 12 '15 at 20:22

MarkHasper

177
1
11

Entity Framework - only get specific columns specified dynamically from list

3 Answers3