Selecting specific columns with Linq, using string or propertyInfo

Question

I am rebuilding our reporting system using EF, where as our old system used a lot of dynamic SQL (bad i know), so i would like to do it using Linq, so it uses parameterized queries etc.

In a report a user can choose which columns of data they want to view. Now how can i take these values and return an SQL statement using Linq and get the columns i need? I wonder if i should even bother and just return all the data, then just show the columns the user wants on screen, which may be want i need to do, but thought i would ask anyway.

So lets take the following Linq example, i say i would only like the Id, Name and Town, how could i do this. Currently i have something similar to

var columns = new List<string>() { "Id", "Name", "Town" };

return _context.Data
    .Where(e => e.Name == "test")
    .ToList();

Is this even possible?

This might give you an idea http://tomasp.net/blog/dynamic-linq-queries.aspx/ — Davor Zlotrg, Feb 25 '15 at 10:24

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

if you yant select propertys accordance their names try Dynamic LINQ library:

public List<Data> ListByNames(string[] arr)
{
    var str = string.Format("new ({0})", string.Join(", ", arr));
    return _context.Data.Select(str);
}

Or write your own Expression, see @TomBrothers answer: https://stackoverflow.com/a/4546633/1271037

edited May 23 '17 at 11:43

Community

1
1

answered Feb 25 '15 at 09:57

dovid

6,354
3
33
73

score -1 · Answer 2 · answered Feb 25 '15 at 10:06

-1

I know this kind of problem. Chief problem: with EF you are not handling columns any more but properties.

Try somthing like this:

var column="yourcolumn";
return _context.Data.Where(e => e.GetType().GetProperty(column).GetValue(_context, null)).ToList();

answered Feb 25 '15 at 10:06

LocEngineer

2,847
1
16
28

You can not use `Reflection` in the `EF` expression. – dovid Feb 25 '15 at 10:29
Yes you can, once you use the model instead of the context. I am using reflection myself directly on an EF model. – LocEngineer Feb 25 '15 at 12:14
Means intead of returning _context.Data... you probably have to first set a var data=_context.Data, then var result=data.Where(...), then return result. – LocEngineer Feb 25 '15 at 12:22
right, its possible in LINQ2OBJECT. but the question is how avoid unnecessary result form DB-query (LinqToEntity). – dovid Feb 25 '15 at 12:43

Selecting specific columns with Linq, using string or propertyInfo

2 Answers2